package main

import (

	"database/sql"

	"errors"

	"strings"

	"time"

	"github.com/stevedylandev/andromeda/pkg/auth"

)

const schema = `

CREATE TABLE IF NOT EXISTS categories (

    id         INTEGER PRIMARY KEY AUTOINCREMENT,

    short_id   TEXT NOT NULL UNIQUE,

    name       TEXT NOT NULL UNIQUE,

    position   INTEGER NOT NULL DEFAULT 0,

    created_at INTEGER NOT NULL

);

CREATE TABLE IF NOT EXISTS links (

    id           INTEGER PRIMARY KEY AUTOINCREMENT,

    short_id     TEXT NOT NULL UNIQUE,

    title        TEXT NOT NULL,

    url          TEXT NOT NULL,

    favicon_url  TEXT,

    category_id  INTEGER NOT NULL REFERENCES categories(id) ON DELETE CASCADE,

    created_at   INTEGER NOT NULL

);

CREATE INDEX IF NOT EXISTS idx_links_category ON links(category_id, created_at DESC);

`

func listCategories(db *sql.DB) ([]Category, error) {

	rows, err := db.Query(`SELECT id, short_id, name, position FROM categories ORDER BY position ASC, name COLLATE NOCASE`)

	if err != nil {

		return nil, err

	}

	defer rows.Close()

	out := []Category{}

	for rows.Next() {

		var c Category

		if err := rows.Scan(&c.ID, &c.ShortID, &c.Name, &c.Position); err != nil {

			return nil, err

		}

		out = append(out, c)

	}

	return out, rows.Err()

}

func createCategory(db *sql.DB, name string) (*Category, error) {

	shortID, err := auth.GenerateShortID(10)

	if err != nil {

		return nil, err

	}

	now := time.Now().UTC().Unix()

	var nextPos int64

	if err := db.QueryRow(`SELECT COALESCE(MAX(position), 0) + 1 FROM categories`).Scan(&nextPos); err != nil {

		return nil, err

	}

	res, err := db.Exec(`INSERT INTO categories (short_id, name, position, created_at) VALUES (?, ?, ?, ?)`, shortID, name, nextPos, now)

	if err != nil {

		return nil, err

	}

	id, _ := res.LastInsertId()

	return &Category{ID: id, ShortID: shortID, Name: name, Position: nextPos}, nil

}

func deleteCategoryByShortID(db *sql.DB, shortID string) (bool, error) {

	res, err := db.Exec(`DELETE FROM categories WHERE short_id = ?`, shortID)

	if err != nil {

		return false, err

	}

	n, _ := res.RowsAffected()

	return n > 0, nil

}

func moveCategory(db *sql.DB, shortID string, direction int) (bool, error) {

	tx, err := db.Begin()

	if err != nil {

		return false, err

	}

	defer tx.Rollback()

	var curID, curPos int64

	err = tx.QueryRow(`SELECT id, position FROM categories WHERE short_id = ?`, shortID).Scan(&curID, &curPos)

	if errors.Is(err, sql.ErrNoRows) {

		return false, nil

	}

	if err != nil {

		return false, err

	}

	var nbID, nbPos int64

	if direction < 0 {

		err = tx.QueryRow(`SELECT id, position FROM categories WHERE position < ? ORDER BY position DESC LIMIT 1`, curPos).Scan(&nbID, &nbPos)

	} else {

		err = tx.QueryRow(`SELECT id, position FROM categories WHERE position > ? ORDER BY position ASC LIMIT 1`, curPos).Scan(&nbID, &nbPos)

	}

	if errors.Is(err, sql.ErrNoRows) {

		return false, nil

	}

	if err != nil {

		return false, err

	}

	if _, err := tx.Exec(`UPDATE categories SET position = ? WHERE id = ?`, nbPos, curID); err != nil {

		return false, err

	}

	if _, err := tx.Exec(`UPDATE categories SET position = ? WHERE id = ?`, curPos, nbID); err != nil {

		return false, err

	}

	if err := tx.Commit(); err != nil {

		return false, err

	}

	return true, nil

}

func getCategoryByName(db *sql.DB, name string) (*Category, error) {

	name = strings.TrimSpace(name)

	var c Category

	err := db.QueryRow(`SELECT id, short_id, name, position FROM categories WHERE name = ?`, name).Scan(&c.ID, &c.ShortID, &c.Name, &c.Position)

	if errors.Is(err, sql.ErrNoRows) {

		return nil, nil

	}

	if err != nil {

		return nil, err

	}

	return &c, nil

}

func listLinks(db *sql.DB) ([]Link, error) {

	rows, err := db.Query(`SELECT id, short_id, title, url, favicon_url, category_id, created_at FROM links ORDER BY created_at DESC`)

	if err != nil {

		return nil, err

	}

	defer rows.Close()

	out := []Link{}

	for rows.Next() {

		var l Link

		var fav sql.NullString

		if err := rows.Scan(&l.ID, &l.ShortID, &l.Title, &l.URL, &fav, &l.CategoryID, &l.CreatedAt); err != nil {

			return nil, err

		}

		if fav.Valid && fav.String != "" {

			s := fav.String

			l.FaviconURL = &s

		}

		out = append(out, l)

	}

	return out, rows.Err()

}

func createLink(db *sql.DB, title, url string, faviconURL *string, categoryID int64) (*Link, error) {

	shortID, err := auth.GenerateShortID(10)

	if err != nil {

		return nil, err

	}

	now := time.Now().UTC().Unix()

	var fav any

	if faviconURL != nil && *faviconURL != "" {

		fav = *faviconURL

	}

	res, err := db.Exec(`INSERT INTO links (short_id, title, url, favicon_url, category_id, created_at) VALUES (?, ?, ?, ?, ?, ?)`,

		shortID, title, url, fav, categoryID, now)

	if err != nil {

		return nil, err

	}

	id, _ := res.LastInsertId()

	link := &Link{ID: id, ShortID: shortID, Title: title, URL: url, CategoryID: categoryID, CreatedAt: now}

	if faviconURL != nil && *faviconURL != "" {

		s := *faviconURL

		link.FaviconURL = &s

	}

	return link, nil

}

func listLinksMissingFavicon(db *sql.DB) ([]struct {

	ID  int64

	URL string

}, error) {

	rows, err := db.Query(`SELECT id, url FROM links WHERE favicon_url IS NULL OR favicon_url = ''`)

	if err != nil {

		return nil, err

	}

	defer rows.Close()

	var out []struct {

		ID  int64

		URL string

	}

	for rows.Next() {

		var r struct {

			ID  int64

			URL string

		}

		if err := rows.Scan(&r.ID, &r.URL); err != nil {

			return nil, err

		}

		out = append(out, r)

	}

	return out, rows.Err()

}

func updateLinkFavicon(db *sql.DB, id int64, favicon *string) error {

	var v any

	if favicon != nil && *favicon != "" {

		v = *favicon

	}

	_, err := db.Exec(`UPDATE links SET favicon_url = ? WHERE id = ?`, v, id)

	return err

}

func deleteLinkByShortID(db *sql.DB, shortID string) (bool, error) {

	res, err := db.Exec(`DELETE FROM links WHERE short_id = ?`, shortID)

	if err != nil {

		return false, err

	}

	n, _ := res.RowsAffected()

	return n > 0, nil

}