package db

import (
	"database/sql"
	"encoding/json"
	"strings"
	"time"

	_ "modernc.org/sqlite"
)

type MetaDB struct {
	db *sql.DB
}

type PageMeta struct {
	Path      string
	HTMLPath  string
	Title     string
	Date      string
	Tags      []string
	UpdatedAt time.Time
}

func OpenMeta(path string) (*MetaDB, error) {
	db, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS pages (
			id        INTEGER PRIMARY KEY AUTOINCREMENT,
			path      TEXT NOT NULL UNIQUE,
			html_path TEXT NOT NULL,
			title     TEXT NOT NULL DEFAULT '',
			date      TEXT DEFAULT '',
			tags      TEXT DEFAULT '[]',
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		);
		CREATE INDEX IF NOT EXISTS idx_pages_path ON pages(path);
		CREATE INDEX IF NOT EXISTS idx_pages_date ON pages(date);
	`)
	if err != nil {
		return nil, err
	}
	return &MetaDB{db: db}, nil
}

func (m *MetaDB) Close() error { return m.db.Close() }

func (m *MetaDB) UpsertPage(p PageMeta) error {
	tags, _ := json.Marshal(p.Tags)
	_, err := m.db.Exec(`
		INSERT INTO pages (path, html_path, title, date, tags, updated_at)
		VALUES (?, ?, ?, ?, ?, ?)
		ON CONFLICT(path) DO UPDATE SET
			html_path  = excluded.html_path,
			title      = excluded.title,
			date       = excluded.date,
			tags       = excluded.tags,
			updated_at = excluded.updated_at
	`, p.Path, p.HTMLPath, p.Title, p.Date, string(tags), p.UpdatedAt.UTC())
	return err
}

func (m *MetaDB) DeletePage(path string) error {
	_, err := m.db.Exec(`DELETE FROM pages WHERE path = ?`, path)
	return err
}

func (m *MetaDB) GetPage(path string) (*PageMeta, error) {
	row := m.db.QueryRow(
		`SELECT path, html_path, title, date, tags FROM pages WHERE path = ?`, path)
	var p PageMeta
	var tagsJSON string
	if err := row.Scan(&p.Path, &p.HTMLPath, &p.Title, &p.Date, &tagsJSON); err != nil {
		return nil, err
	}
	_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
	return &p, nil
}

func (m *MetaDB) ListPages() ([]PageMeta, error) {
	rows, err := m.db.Query(
		`SELECT path, html_path, title, date, tags FROM pages ORDER BY date DESC, path`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanPages(rows)
}

func (m *MetaDB) ListByTag(tag string) ([]PageMeta, error) {
	// JSON array contains check via LIKE — sufficient for simple tag strings.
	needle := `%"` + strings.ReplaceAll(tag, `"`, `\"`) + `"%`
	rows, err := m.db.Query(
		`SELECT path, html_path, title, date, tags FROM pages WHERE tags LIKE ? ORDER BY date DESC`, needle)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	return scanPages(rows)
}

func scanPages(rows *sql.Rows) ([]PageMeta, error) {
	var pages []PageMeta
	for rows.Next() {
		var p PageMeta
		var tagsJSON string
		if err := rows.Scan(&p.Path, &p.HTMLPath, &p.Title, &p.Date, &tagsJSON); err != nil {
			return nil, err
		}
		_ = json.Unmarshal([]byte(tagsJSON), &p.Tags)
		pages = append(pages, p)
	}
	return pages, rows.Err()
}