package database

import (
	"database/sql"
	"fmt"
	"log/slog"

	atshorter "tangled.sh/willdot.net/at-shorter-url"
)

func createURLsTable(db *sql.DB) error {
	createURLsTableSQL := `CREATE TABLE IF NOT EXISTS urls (
		"id" TEXT NOT NULL PRIMARY KEY,
		"url" TEXT NOT NULL,
		"did" TEXT NOT NULL,
		"originHost" TEXT NOT NULL,
		"createdAt" integer
	  );`

	slog.Info("Create urls table...")
	statement, err := db.Prepare(createURLsTableSQL)
	if err != nil {
		return fmt.Errorf("prepare DB statement to create urls table: %w", err)
	}
	_, err = statement.Exec()
	if err != nil {
		return fmt.Errorf("exec sql statement to create urls table: %w", err)
	}
	slog.Info("status urls created")

	return nil
}

func (d *DB) CreateURL(id, url, did, originHost string, createdAt int64) error {
	sql := `INSERT INTO urls (id, url, did, originHost, createdAt) VALUES (?, ?, ?, ?, ?) ON CONFLICT(id) DO NOTHING;`
	_, err := d.db.Exec(sql, id, url, did, originHost, createdAt)
	if err != nil {
		// TODO: catch already exists
		return fmt.Errorf("exec insert url: %w", err)
	}

	return nil
}

func (d *DB) GetURLs(did string) ([]atshorter.ShortURL, error) {
	sql := "SELECT id, url, did, originHost FROM urls WHERE did = ?;"
	rows, err := d.db.Query(sql, did)
	if err != nil {
		return nil, fmt.Errorf("run query to get URLS': %w", err)
	}
	defer rows.Close()

	var results []atshorter.ShortURL
	for rows.Next() {
		var shortURL atshorter.ShortURL
		if err := rows.Scan(&shortURL.ID, &shortURL.URL, &shortURL.Did, &shortURL.OriginHost); err != nil {
			return nil, fmt.Errorf("scan row: %w", err)
		}

		results = append(results, shortURL)
	}
	return results, nil
}

func (d *DB) GetURLByID(id string) (atshorter.ShortURL, error) {
	sql := "SELECT id, url, did, originHost FROM urls WHERE id = ?;"
	rows, err := d.db.Query(sql, id)
	if err != nil {
		return atshorter.ShortURL{}, fmt.Errorf("run query to get URL by id': %w", err)
	}
	defer rows.Close()

	var result atshorter.ShortURL
	for rows.Next() {
		if err := rows.Scan(&result.ID, &result.URL, &result.Did, &result.OriginHost); err != nil {
			return atshorter.ShortURL{}, fmt.Errorf("scan row: %w", err)
		}
		return result, nil
	}
	return atshorter.ShortURL{}, atshorter.ErrorNotFound
}

func (s *DB) DeleteURL(id, did string) error {
	sql := "DELETE FROM urls WHERE id = ? AND did = ?;"
	_, err := s.db.Exec(sql, id, did)
	if err != nil {
		return fmt.Errorf("exec delete URL by id and DID: %w", err)
	}
	return nil
}