package db

import (
	"database/sql"
	"path/filepath"
	"testing"
)

func TestSnippetExportable(t *testing.T) {
	cases := []struct {
		snippet string
		want    bool
	}{
		{"", false},
		{"[]", false},
		{"not json", false},
		{`{"related_kw":[],"description":[]}`, false},
		{`{"related_kw":["a"],"description":[]}`, false},
		{`{"related_kw":["a"],"description":[""]}`, false},
		{`{"related_kw":["a"],"description":["real"]}`, true},
		{`{"related_kw":[],"description":["x","y"]}`, true},
	}
	for _, c := range cases {
		if got := SnippetExportable(c.snippet); got != c.want {
			t.Errorf("SnippetExportable(%q) = %v, want %v", c.snippet, got, c.want)
		}
	}
}

func TestCountExportable(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "exp.sqlite")
	conn, err := Open(dbPath)
	if err != nil {
		t.Fatal(err)
	}
	if err := EnsureSchema(conn); err != nil {
		t.Fatal(err)
	}

	stmts := []string{
		// exportable: images + description
		`INSERT INTO posts (keyword, slug, images, snippet) VALUES ('k1','k1','[{"title":"t","image_url":"u"}]', '{"related_kw":["a"],"description":["d"]}')`,
		// exportable: images + ai_content only (Case C)
		`INSERT INTO posts (keyword, slug, images, ai_content) VALUES ('k2','k2','[{"title":"t","image_url":"u"}]', '<p>ai</p>')`,
		// not exportable: images but neither
		`INSERT INTO posts (keyword, slug, images, snippet) VALUES ('k3','k3','[{"title":"t","image_url":"u"}]', '{"related_kw":["a"],"description":[]}')`,
		// not exportable: no images
		`INSERT INTO posts (keyword, slug, images, snippet) VALUES ('k4','k4','[]', '{"related_kw":["a"],"description":["d"]}')`,
		// exportable: both present
		`INSERT INTO posts (keyword, slug, images, snippet, ai_content) VALUES ('k5','k5','[{"title":"t","image_url":"u"}]', '{"related_kw":["a"],"description":["d"]}', '<p>ai</p>')`,
	}
	for _, s := range stmts {
		if _, err := conn.Exec(s); err != nil {
			t.Fatal(err)
		}
	}

	n, err := CountExportable(conn)
	if err != nil {
		t.Fatal(err)
	}
	if n != 3 {
		t.Fatalf("CountExportable = %d, want 3", n)
	}
	conn.Close()
}

func TestDeleteAI(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "del.sqlite")
	conn, err := Open(dbPath)
	if err != nil {
		t.Fatal(err)
	}
	if err := EnsureSchema(conn); err != nil {
		t.Fatal(err)
	}
	if _, err := conn.Exec(`INSERT INTO posts (keyword, slug, ai_title, ai_content) VALUES ('k','k','Title','<p>body</p>')`); err != nil {
		t.Fatal(err)
	}

	n, err := DeleteAITitles(conn)
	if err != nil || n != 1 {
		t.Fatalf("DeleteAITitles = %d, %v; want 1, nil", n, err)
	}
	n, err = DeleteAIArticles(conn)
	if err != nil || n != 1 {
		t.Fatalf("DeleteAIArticles = %d, %v; want 1, nil", n, err)
	}

	var title, content sql.NullString
	if err := conn.QueryRow(`SELECT ai_title, ai_content FROM posts WHERE id = 1`).Scan(&title, &content); err != nil {
		t.Fatal(err)
	}
	if title.Valid || content.Valid {
		t.Fatalf("expected NULL after delete, got %q / %q", title.String, content.String)
	}
	conn.Close()
}