package db

import (
	"os"
	"path/filepath"
	"testing"

	_ "github.com/mattn/go-sqlite3"
)

func setupTestDB(t *testing.T) (*string, func()) {
	t.Helper()
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")

	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}

	cleanup := func() {
		database.Close()
	}

	return &dbPath, cleanup
}

func TestInitDB(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")

	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB returned error: %v", err)
	}
	defer database.Close()

	// Verify tables exist
	var count int
	err = database.QueryRow("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='pdfs'").Scan(&count)
	if err != nil {
		t.Fatalf("Query pdfs table: %v", err)
	}
	if count != 1 {
		t.Errorf("expected pdfs table to exist, got count %d", count)
	}

	err = database.QueryRow("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='form_fields'").Scan(&count)
	if err != nil {
		t.Fatalf("Query form_fields table: %v", err)
	}
	if count != 1 {
		t.Errorf("expected form_fields table to exist, got count %d", count)
	}
}

func TestInitDBCreatesDirectory(t *testing.T) {
	dir := t.TempDir()
	nestedDir := filepath.Join(dir, "sub", "nested")
	dbPath := filepath.Join(nestedDir, "test.db")

	database, err := InitDB(dbPath, nestedDir)
	if err != nil {
		t.Fatalf("InitDB should create nested directories: %v", err)
	}
	defer database.Close()

	if _, err := os.Stat(dbPath); os.IsNotExist(err) {
		t.Error("expected database file to be created")
	}
}

func TestInsertPDF(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	pdfID, err := InsertPDF(database, "test.pdf", "original.pdf", "/path/to/test.pdf", 1234)
	if err != nil {
		t.Fatalf("InsertPDF returned error: %v", err)
	}
	if pdfID == 0 {
		t.Error("expected non-zero ID")
	}
}

func TestInsertPDFDuplicateFilename(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	_, err = InsertPDF(database, "test.pdf", "original.pdf", "/path/to/test.pdf", 1234)
	if err != nil {
		t.Fatalf("first InsertPDF should succeed: %v", err)
	}

	_, err = InsertPDF(database, "test.pdf", "original.pdf", "/path/to/test2.pdf", 5678)
	if err == nil {
		t.Error("expected duplicate filename to fail")
	}
}

func TestGetPDF(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	pdfID, err := InsertPDF(database, "test.pdf", "original.pdf", "/path/to/test.pdf", 1234)
	if err != nil {
		t.Fatalf("InsertPDF: %v", err)
	}

	filename, filePath, err := GetPDF(database, pdfID)
	if err != nil {
		t.Fatalf("GetPDF returned error: %v", err)
	}
	if filename != "test.pdf" {
		t.Errorf("expected filename test.pdf, got %s", filename)
	}
	if filePath != "/path/to/test.pdf" {
		t.Errorf("expected file_path /path/to/test.pdf, got %s", filePath)
	}
}

func TestGetPDFNotFound(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	_, _, err = GetPDF(database, 999)
	if err == nil {
		t.Error("expected error for non-existent PDF")
	}
}

func TestInsertAndGetFormField(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	pdfID, err := InsertPDF(database, "test.pdf", "original.pdf", "/path/to/test.pdf", 1234)
	if err != nil {
		t.Fatalf("InsertPDF: %v", err)
	}

	_, err = InsertFormField(database, pdfID, "Name", "text", "", "", "", "Full name", false, 1, "")
	if err != nil {
		t.Fatalf("InsertFormField: %v", err)
	}

	rows, err := GetFormFields(database, pdfID)
	if err != nil {
		t.Fatalf("GetFormFields: %v", err)
	}
	if len(rows) != 1 {
		t.Fatalf("expected 1 field, got %d", len(rows))
	}

	row := rows[0]
	if row[1].(string) != "Name" {
		t.Errorf("expected name 'Name', got %v", row[1])
	}
	if row[2].(string) != "text" {
		t.Errorf("expected type 'text', got %v", row[2])
	}
}

func TestInsertFormFieldWithChoices(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	pdfID, err := InsertPDF(database, "test.pdf", "original.pdf", "/path/to/test.pdf", 1234)
	if err != nil {
		t.Fatalf("InsertPDF: %v", err)
	}

	_, err = InsertFormField(database, pdfID, "Country", "combobox", `["Austria","Belgium","Germany"]`, "", "", "", false, 1, "")
	if err != nil {
		t.Fatalf("InsertFormField: %v", err)
	}

	rows, err := GetFormFields(database, pdfID)
	if err != nil {
		t.Fatalf("GetFormFields: %v", err)
	}

	row := rows[0]
	if row[3].(string) != `["Austria","Belgium","Germany"]` {
		t.Errorf("expected choices JSON, got %v", row[3])
	}
}

func TestInsertFormFieldWithValue(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	pdfID, err := InsertPDF(database, "test.pdf", "original.pdf", "/path/to/test.pdf", 1234)
	if err != nil {
		t.Fatalf("InsertPDF: %v", err)
	}

	_, err = InsertFormField(database, pdfID, "Height", "text", "", "150", "150", "", false, 1, "")
	if err != nil {
		t.Fatalf("InsertFormField: %v", err)
	}

	rows, err := GetFormFields(database, pdfID)
	if err != nil {
		t.Fatalf("GetFormFields: %v", err)
	}

	row := rows[0]
	if row[4].(string) != "150" {
		t.Errorf("expected value '150', got %v", row[4])
	}
	if row[5].(string) != "150" {
		t.Errorf("expected default '150', got %v", row[5])
	}
}

func TestGetFormFieldsEmpty(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	rows, err := GetFormFields(database, 999)
	if err != nil {
		t.Fatalf("GetFormFields: %v", err)
	}
	if len(rows) != 0 {
		t.Errorf("expected 0 fields for non-existent PDF, got %d", len(rows))
	}
}

func TestClearFormFields(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	pdfID, err := InsertPDF(database, "test.pdf", "original.pdf", "/path/to/test.pdf", 1234)
	if err != nil {
		t.Fatalf("InsertPDF: %v", err)
	}

	_, err = InsertFormField(database, pdfID, "Field1", "text", "", "", "", "", false, 1, "")
	if err != nil {
		t.Fatalf("InsertFormField: %v", err)
	}
	_, err = InsertFormField(database, pdfID, "Field2", "text", "", "", "", "", false, 1, "")
	if err != nil {
		t.Fatalf("InsertFormField: %v", err)
	}

	err = ClearFormFields(database, pdfID)
	if err != nil {
		t.Fatalf("ClearFormFields: %v", err)
	}

	rows, err := GetFormFields(database, pdfID)
	if err != nil {
		t.Fatalf("GetFormFields: %v", err)
	}
	if len(rows) != 0 {
		t.Errorf("expected 0 fields after clear, got %d", len(rows))
	}
}

func TestMultiplePDFs(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	id1, err := InsertPDF(database, "doc1.pdf", "doc1.pdf", "/path/1.pdf", 100)
	if err != nil {
		t.Fatalf("InsertPDF 1: %v", err)
	}
	id2, err := InsertPDF(database, "doc2.pdf", "doc2.pdf", "/path/2.pdf", 200)
	if err != nil {
		t.Fatalf("InsertPDF 2: %v", err)
	}

	_, err = InsertFormField(database, id1, "FieldA", "text", "", "", "", "", false, 1, "")
	if err != nil {
		t.Fatalf("InsertFormField for 1: %v", err)
	}
	_, err = InsertFormField(database, id2, "FieldB", "text", "", "", "", "", false, 1, "")
	if err != nil {
		t.Fatalf("InsertFormField for 2: %v", err)
	}

	rows1, err := GetFormFields(database, id1)
	if err != nil || len(rows1) != 1 {
		t.Fatalf("PDF 1 should have 1 field, got %d: %v", len(rows1), err)
	}
	rows2, err := GetFormFields(database, id2)
	if err != nil || len(rows2) != 1 {
		t.Fatalf("PDF 2 should have 1 field, got %d: %v", len(rows2), err)
	}

	if rows1[0][1].(string) != "FieldA" {
		t.Errorf("PDF 1 should have FieldA, got %v", rows1[0][1])
	}
	if rows2[0][1].(string) != "FieldB" {
		t.Errorf("PDF 2 should have FieldB, got %v", rows2[0][1])
	}
}

func TestUpdateFormFieldValueGroup(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	pdfID, _ := InsertPDF(database, "test.pdf", "test.pdf", "/path/test.pdf", 100)
	fieldID, _ := InsertFormField(database, pdfID, "Name1", "text", "", "", "", "", false, 1, "")
	fieldID2, _ := InsertFormField(database, pdfID, "Name2", "text", "", "", "", "", false, 2, "")

	err = UpdateFormFieldValueGroup(database, fieldID, "Client Name")
	if err != nil {
		t.Fatalf("UpdateFormFieldValueGroup: %v", err)
	}
	err = UpdateFormFieldValueGroup(database, fieldID2, "Client Name")
	if err != nil {
		t.Fatalf("UpdateFormFieldValueGroup: %v", err)
	}

	rows, err := GetFormFields(database, pdfID)
	if err != nil || len(rows) != 2 {
		t.Fatalf("GetFormFields: %v, got %d rows", err, len(rows))
	}

	if rows[0][11].(string) != "Client Name" {
		t.Errorf("expected value_group 'Client Name', got %v", rows[0][11])
	}
	if rows[1][11].(string) != "Client Name" {
		t.Errorf("expected value_group 'Client Name' for field 2, got %v", rows[1][11])
	}
}

func TestUpdateFormFieldWizardPage(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	pdfID, _ := InsertPDF(database, "test.pdf", "test.pdf", "/path/test.pdf", 100)
	fieldID1, _ := InsertFormField(database, pdfID, "FirstName", "text", "", "", "", "", false, 1, "")
	fieldID2, _ := InsertFormField(database, pdfID, "LastName", "text", "", "", "", "", false, 1, "")
	fieldID3, _ := InsertFormField(database, pdfID, "Address", "text", "", "", "", "", false, 1, "")

	err = UpdateFormFieldWizardPage(database, fieldID1, 1)
	if err != nil {
		t.Fatalf("UpdateFormFieldWizardPage: %v", err)
	}
	err = UpdateFormFieldWizardPage(database, fieldID2, 1)
	if err != nil {
		t.Fatalf("UpdateFormFieldWizardPage: %v", err)
	}
	err = UpdateFormFieldWizardPage(database, fieldID3, 2)
	if err != nil {
		t.Fatalf("UpdateFormFieldWizardPage: %v", err)
	}

	rows, err := GetFormFields(database, pdfID)
	if err != nil || len(rows) != 3 {
		t.Fatalf("GetFormFields: %v, got %d rows", err, len(rows))
	}

	if rows[0][12].(int) != 1 {
		t.Errorf("expected wizard_page 1, got %v", rows[0][12])
	}
	if rows[1][12].(int) != 1 {
		t.Errorf("expected wizard_page 1 for field 2, got %v", rows[1][12])
	}
	if rows[2][12].(int) != 2 {
		t.Errorf("expected wizard_page 2 for field 3, got %v", rows[2][12])
	}
}

func TestGetFormFieldsReturnsNewColumns(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	pdfID, _ := InsertPDF(database, "test.pdf", "test.pdf", "/path/test.pdf", 100)
	fieldID, _ := InsertFormField(database, pdfID, "TestField", "text", "", "", "", "", false, 1, "")

	UpdateFormFieldValueGroup(database, fieldID, "MyGroup")
	UpdateFormFieldWizardPage(database, fieldID, 3)

	rows, err := GetFormFields(database, pdfID)
	if err != nil || len(rows) != 1 {
		t.Fatalf("GetFormFields: %v", err)
	}

	row := rows[0]
	if len(row) != 13 {
		t.Fatalf("expected 13 columns, got %d", len(row))
	}
	if row[11].(string) != "MyGroup" {
		t.Errorf("expected value_group 'MyGroup', got %v", row[11])
	}
	if row[12].(int) != 3 {
		t.Errorf("expected wizard_page 3, got %v", row[12])
	}
}

func TestGetFormFieldsNullNewColumns(t *testing.T) {
	dir := t.TempDir()
	dbPath := filepath.Join(dir, "test.db")
	database, err := InitDB(dbPath, dir)
	if err != nil {
		t.Fatalf("InitDB: %v", err)
	}
	defer database.Close()

	pdfID, _ := InsertPDF(database, "test.pdf", "test.pdf", "/path/test.pdf", 100)
	_, _ = InsertFormField(database, pdfID, "TestField", "text", "", "", "", "", false, 1, "")

	rows, err := GetFormFields(database, pdfID)
	if err != nil || len(rows) != 1 {
		t.Fatalf("GetFormFields: %v", err)
	}

	row := rows[0]
	if row[11].(string) != "" {
		t.Errorf("expected empty value_group, got %v", row[11])
	}
	if row[12].(int) != -1 {
		t.Errorf("expected -1 wizard_page, got %v", row[12])
	}
}