package db

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"os"
	"path/filepath"
	"sort"
	"strings"

	_ "modernc.org/sqlite"
)

const schemaSQL = `
CREATE TABLE IF NOT EXISTS posts (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	keyword TEXT,
	slug TEXT,
	images TEXT,
	snippet TEXT,
	ai_title TEXT,
	ai_content TEXT,
	status INTEGER DEFAULT 0,
	created_at TEXT DEFAULT (datetime('now')),
	updated_at TEXT DEFAULT (datetime('now'))
)
`

func Open(path string) (*sql.DB, error) {
	conn, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}
	conn.SetMaxOpenConns(1)
	if _, err := conn.Exec("PRAGMA journal_mode=WAL"); err != nil {
		conn.Close()
		return nil, err
	}
	if _, err := conn.Exec("PRAGMA synchronous=NORMAL"); err != nil {
		conn.Close()
		return nil, err
	}
	if _, err := conn.Exec("PRAGMA busy_timeout=30000"); err != nil {
		conn.Close()
		return nil, err
	}
	return conn, nil
}

func EnsureSchema(conn *sql.DB) error {
	_, err := conn.Exec(schemaSQL)
	return err
}

// Close checkpoints the WAL into the main database file and closes the
// connection. Without this the data stays in <db>-wal and tools reading only
// the .sqlite file do not see it.
func Close(conn *sql.DB) error {
	if conn == nil {
		return nil
	}
	conn.Exec("PRAGMA wal_checkpoint(TRUNCATE)")
	return conn.Close()
}

func ListDBFiles(dataFolder string) ([]string, error) {
	entries, err := os.ReadDir(dataFolder)
	if err != nil {
		if os.IsNotExist(err) {
			return nil, nil
		}
		return nil, err
	}
	var out []string
	for _, e := range entries {
		if e.IsDir() {
			continue
		}
		ext := strings.ToLower(filepath.Ext(e.Name()))
		if ext == ".sqlite" || ext == ".db" {
			out = append(out, filepath.Join(dataFolder, e.Name()))
		}
	}
	sort.Strings(out)
	return out, nil
}

type KeywordRow struct {
	ID      int64
	Keyword string
	Slug    string
}

func GetPendingKeywords(conn *sql.DB) ([]KeywordRow, error) {
	rows, err := conn.Query(`
		SELECT id, keyword FROM posts
		WHERE (status = 0 OR status IS NULL)
		OR (status = 1 AND (images IS NULL OR images = '[]' OR images = ''))
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []KeywordRow
	for rows.Next() {
		var r KeywordRow
		if err := rows.Scan(&r.ID, &r.Keyword); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// SnippetRow is a row whose snippet needs related keywords filled in.
type SnippetRow struct {
	ID       int64
	Keyword  string
	Existing string
}

// GetPendingRelated returns rows that already carry images but whose
// related_kw half is still empty: phase 2 of the scrape (suggest only,
// never Pinterest). Rows without images are skipped, they are not exported.
func GetPendingRelated(conn *sql.DB) ([]SnippetRow, error) {
	rows, err := conn.Query(`
		SELECT id, keyword, snippet FROM posts
		WHERE images IS NOT NULL AND images != '' AND images != '[]'
		AND (
			snippet IS NULL OR snippet = '' OR snippet = '[]'
			OR json_valid(snippet) = 0
			OR COALESCE(json_array_length(json_extract(snippet, '$.related_kw')), 0) = 0
		)
	`)
	goFilter := false
	if err != nil {
		// JSON1 unavailable: fetch candidates and filter in Go.
		rows, err = conn.Query(`
			SELECT id, keyword, snippet FROM posts
			WHERE images IS NOT NULL AND images != '' AND images != '[]'
		`)
		if err != nil {
			return nil, err
		}
		goFilter = true
	}
	defer rows.Close()
	var out []SnippetRow
	for rows.Next() {
		var r SnippetRow
		var snippet sql.NullString
		if err := rows.Scan(&r.ID, &r.Keyword, &snippet); err != nil {
			return nil, err
		}
		r.Existing = snippet.String
		_, needRelated := snippetGaps(r.Existing)
		if goFilter && !needRelated {
			continue
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// snippetGaps reports which snippet halves are still missing: descriptions
// and/or related keywords. Mirrors the JSON1 predicates in the pending queries.
func snippetGaps(snippet string) (needDesc, needRelated bool) {
	if snippet == "" || snippet == "[]" {
		return true, true
	}
	var data struct {
		RelatedKW   []string `json:"related_kw"`
		Description []string `json:"description"`
	}
	if err := json.Unmarshal([]byte(snippet), &data); err != nil {
		return true, true
	}
	return len(data.Description) == 0, len(data.RelatedKW) == 0
}

// imagesNeedWork mirrors the images half of the pending predicate: true when
// the images column holds nothing yet.
func imagesNeedWork(images string) bool {
	return images == "" || images == "[]"
}

// WorkRow is a keyword row that still needs phase 1 work: images and/or
// descriptions, both fetched from a single Pinterest request.
type WorkRow struct {
	ID         int64
	Keyword    string
	Existing   string
	NeedImages bool
	NeedDesc   bool
}

// GetPendingImagesDesc returns rows that still need images and/or
// descriptions: phase 1 of the scrape (Pinterest only). Rows that only lack
// related keywords are left to GetPendingRelated.
func GetPendingImagesDesc(conn *sql.DB) ([]WorkRow, error) {
	rows, err := conn.Query(`
		SELECT id, keyword, snippet, images FROM posts
		WHERE images IS NULL OR images = '' OR images = '[]'
		OR (
			snippet IS NULL OR snippet = '' OR snippet = '[]'
			OR json_valid(snippet) = 0
			OR COALESCE(json_array_length(json_extract(snippet, '$.description')), 0) = 0
		)
	`)
	goFilter := false
	if err != nil {
		// JSON1 unavailable: fetch candidates and filter in Go.
		rows, err = conn.Query(`SELECT id, keyword, snippet, images FROM posts`)
		if err != nil {
			return nil, err
		}
		goFilter = true
	}
	defer rows.Close()
	var out []WorkRow
	for rows.Next() {
		var r WorkRow
		var snippet, images sql.NullString
		if err := rows.Scan(&r.ID, &r.Keyword, &snippet, &images); err != nil {
			return nil, err
		}
		r.Existing = snippet.String
		r.NeedImages = imagesNeedWork(images.String)
		r.NeedDesc, _ = snippetGaps(snippet.String)
		if goFilter && !r.NeedImages && !r.NeedDesc {
			continue
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// Progress counts filled columns over one database for the summary report.
type Progress struct {
	Total        int64
	Images       int64
	Descriptions int64
	Related      int64
	AITitles     int64
	AIArticles   int64
}

// CountProgress aggregates the summary counters of one database.
func CountProgress(conn *sql.DB) (Progress, error) {
	var p Progress
	for _, c := range []struct {
		dst *int64
		q   string
	}{
		{&p.Total, `SELECT COUNT(*) FROM posts`},
		{&p.Images, `SELECT COUNT(*) FROM posts WHERE images IS NOT NULL AND images != '' AND images != '[]'`},
		{&p.AITitles, `SELECT COUNT(*) FROM posts WHERE ai_title IS NOT NULL AND ai_title != ''`},
		{&p.AIArticles, `SELECT COUNT(*) FROM posts WHERE ai_content IS NOT NULL AND ai_content != ''`},
	} {
		if err := conn.QueryRow(c.q).Scan(c.dst); err != nil {
			return Progress{}, err
		}
	}
	descErr := conn.QueryRow(`
		SELECT COUNT(*) FROM posts
		WHERE snippet IS NOT NULL AND snippet != '' AND json_valid(snippet) = 1
		AND COALESCE(json_array_length(json_extract(snippet, '$.description')), 0) > 0
	`).Scan(&p.Descriptions)
	relErr := conn.QueryRow(`
		SELECT COUNT(*) FROM posts
		WHERE snippet IS NOT NULL AND snippet != '' AND json_valid(snippet) = 1
		AND COALESCE(json_array_length(json_extract(snippet, '$.related_kw')), 0) > 0
	`).Scan(&p.Related)
	if descErr == nil && relErr == nil {
		return p, nil
	}
	// JSON1 unavailable: count both snippet halves in Go.
	p.Descriptions, p.Related = 0, 0
	rows, err := conn.Query(`SELECT snippet FROM posts`)
	if err != nil {
		return p, err
	}
	defer rows.Close()
	for rows.Next() {
		var s sql.NullString
		if err := rows.Scan(&s); err != nil {
			return p, err
		}
		needDesc, needRelated := snippetGaps(s.String)
		if !needDesc {
			p.Descriptions++
		}
		if !needRelated {
			p.Related++
		}
	}
	return p, rows.Err()
}

// AIJobRow is a row waiting for ai_title or ai_content generation.
type AIJobRow struct {
	ID      int64
	Keyword string
}

func GetPendingAI(conn *sql.DB, column string) ([]AIJobRow, error) {
	if column != "ai_title" && column != "ai_content" {
		return nil, fmt.Errorf("invalid column %q", column)
	}
	q := fmt.Sprintf(`
		SELECT id, keyword FROM posts WHERE %s IS NULL
		AND images IS NOT NULL AND images != '' AND images != '[]'
	`, column)
	rows, err := conn.Query(q)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []AIJobRow
	for rows.Next() {
		var r AIJobRow
		if err := rows.Scan(&r.ID, &r.Keyword); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

func UpdateAllStatus(conn *sql.DB) (map[string]int64, error) {
	res := make(map[string]int64)
	stmts := []struct {
		key string
		sql string
	}{
		{"status_0", `UPDATE posts SET status = 0, updated_at = datetime('now')
			WHERE (images IS NULL OR images = '' OR images = '[]')
			AND (ai_title IS NULL OR ai_title = '')
			AND (ai_content IS NULL OR ai_content = '')
			AND status != 0`},
		{"status_1", `UPDATE posts SET status = 1, updated_at = datetime('now')
			WHERE images IS NOT NULL AND images != '' AND images != '[]'
			AND (ai_title IS NULL OR ai_title = '')
			AND (ai_content IS NULL OR ai_content = '')
			AND status != 1`},
		{"status_2", `UPDATE posts SET status = 2, updated_at = datetime('now')
			WHERE images IS NOT NULL AND images != '' AND images != '[]'
			AND ai_title IS NOT NULL AND ai_title != ''
			AND ai_content IS NOT NULL AND ai_content != ''
			AND status != 2`},
	}
	for _, s := range stmts {
		r, err := conn.Exec(s.sql)
		if err != nil {
			return nil, err
		}
		n, _ := r.RowsAffected()
		res[s.key] = n
	}
	return res, nil
}

// SnippetExportable reports whether a snippet JSON carries at least one
// non-empty description (the export minimum requirement).
func SnippetExportable(raw string) bool {
	if raw == "" || raw == "[]" {
		return false
	}
	var data struct {
		RelatedKW   []string `json:"related_kw"`
		Description []string `json:"description"`
	}
	if err := json.Unmarshal([]byte(raw), &data); err != nil {
		return false
	}
	for _, d := range data.Description {
		if strings.TrimSpace(d) != "" {
			return true
		}
	}
	return false
}

// CountExportable counts rows that can be exported: images present AND
// (snippet description OR ai_content present).
func CountExportable(conn *sql.DB) (int64, error) {
	rows, err := conn.Query(`
		SELECT images, snippet, ai_content FROM posts
		WHERE images IS NOT NULL AND images != '' AND images != '[]'
	`)
	if err != nil {
		return 0, err
	}
	defer rows.Close()
	var n int64
	for rows.Next() {
		var images string
		var snippet, aiContent sql.NullString
		if err := rows.Scan(&images, &snippet, &aiContent); err != nil {
			return 0, err
		}
		if SnippetExportable(snippet.String) || strings.TrimSpace(aiContent.String) != "" {
			n++
		}
	}
	return n, rows.Err()
}

// DeleteAITitles resets ai_title for every row so titles can be regenerated.
func DeleteAITitles(conn *sql.DB) (int64, error) {
	r, err := conn.Exec(`UPDATE posts SET ai_title = NULL, updated_at = datetime('now') WHERE ai_title IS NOT NULL`)
	if err != nil {
		return 0, err
	}
	return r.RowsAffected()
}

// DeleteAIArticles resets ai_content for every row so articles can be
// regenerated.
func DeleteAIArticles(conn *sql.DB) (int64, error) {
	r, err := conn.Exec(`UPDATE posts SET ai_content = NULL, updated_at = datetime('now') WHERE ai_content IS NOT NULL`)
	if err != nil {
		return 0, err
	}
	return r.RowsAffected()
}