package db

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"

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

func InitDB(dbPath, storeDir string) (*sql.DB, error) {
	if err := os.MkdirAll(filepath.Dir(dbPath), 0o755); err != nil {
		return nil, fmt.Errorf("creating db directory: %w", err)
	}
	if err := os.MkdirAll(storeDir, 0o755); err != nil {
		return nil, fmt.Errorf("creating store directory: %w", err)
	}

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return nil, fmt.Errorf("opening db: %w", err)
	}

	schema := `
	CREATE TABLE IF NOT EXISTS pdfs (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		filename TEXT NOT NULL UNIQUE,
		original_name TEXT NOT NULL,
		file_path TEXT NOT NULL,
		size INTEGER NOT NULL,
		description TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE TABLE IF NOT EXISTS form_fields (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		pdf_id INTEGER NOT NULL,
		name TEXT NOT NULL,
		type TEXT NOT NULL,
		choices TEXT,
		value TEXT,
		default_value TEXT,
		title TEXT,
		required INTEGER DEFAULT 0,
		page INTEGER DEFAULT 0,
		rect TEXT,
		question TEXT,
		value_group TEXT,
		wizard_page INTEGER,
		FOREIGN KEY (pdf_id) REFERENCES pdfs(id) ON DELETE CASCADE
	);
	`
	if _, err := db.Exec(schema); err != nil {
		return nil, fmt.Errorf("creating schema: %w", err)
	}

	// Migrate existing tables to add new columns if missing
	migrations := []string{
		"ALTER TABLE pdfs ADD COLUMN description TEXT",
		"ALTER TABLE form_fields ADD COLUMN page INTEGER DEFAULT 0",
		"ALTER TABLE form_fields ADD COLUMN rect TEXT",
		"ALTER TABLE form_fields ADD COLUMN question TEXT",
		"ALTER TABLE form_fields ADD COLUMN value_group TEXT",
		"ALTER TABLE form_fields ADD COLUMN wizard_page INTEGER",
	}
	for _, stmt := range migrations {
		// Ignore "duplicate column" errors (column already exists)
		_, _ = db.Exec(stmt)
	}

	return db, nil
}

func InsertPDF(db *sql.DB, filename, originalName, filePath string, size int64) (int64, error) {
	res, err := db.Exec(
		"INSERT INTO pdfs (filename, original_name, file_path, size) VALUES (?, ?, ?, ?)",
		filename, originalName, filePath, size,
	)
	if err != nil {
		return 0, fmt.Errorf("inserting pdf: %w", err)
	}
	return res.LastInsertId()
}

func InsertFormField(db *sql.DB, pdfID int64, name, fieldType, choices, value, defaultVal, title string, required bool, page int, rect string) (int64, error) {
	res, err := db.Exec(
		`INSERT INTO form_fields (pdf_id, name, type, choices, value, default_value, title, required, page, rect)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		pdfID, name, fieldType, choices, value, defaultVal, title, required, page, rect,
	)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func GetPDF(db *sql.DB, id int64) (filename, filePath string, err error) {
	var desc sql.NullString
	err = db.QueryRow("SELECT filename, file_path, description FROM pdfs WHERE id = ?", id).Scan(&filename, &filePath, &desc)
	return
}

func UpdatePDFDescription(db *sql.DB, pdfID int64, description string) error {
	_, err := db.Exec("UPDATE pdfs SET description = ? WHERE id = ?", description, pdfID)
	return err
}

func GetPDFDescription(db *sql.DB, pdfID int64) (string, error) {
	var desc sql.NullString
	err := db.QueryRow("SELECT description FROM pdfs WHERE id = ?", pdfID).Scan(&desc)
	if err != nil {
		return "", err
	}
	if desc.Valid {
		return desc.String, nil
	}
	return "", nil
}

func GetFormFields(db *sql.DB, pdfID int64) ([][]interface{}, error) {
	rows, err := db.Query(
		"SELECT id, name, type, choices, value, default_value, title, required, page, rect, question, value_group, wizard_page FROM form_fields WHERE pdf_id = ?",
		pdfID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var results [][]interface{}
	for rows.Next() {
		var id, reqSQL, page int
		var name, fieldType, choices, value, defaultVal, title, rect, question, valueGroup sql.NullString
		var wizardPage sql.NullInt64
		if err := rows.Scan(&id, &name, &fieldType, &choices, &value, &defaultVal, &title, &reqSQL, &page, &rect, &question, &valueGroup, &wizardPage); err != nil {
			return nil, err
		}
		wp := (-1)
		if wizardPage.Valid {
			wp = int(wizardPage.Int64)
		}
		results = append(results, []interface{}{
			id, nullStr(name), nullStr(fieldType), nullStr(choices), nullStr(value), nullStr(defaultVal), nullStr(title), reqSQL == 1, page, nullStr(rect), nullStr(question), nullStr(valueGroup), wp,
		})
	}
	return results, rows.Err()
}

func nullStr(ns sql.NullString) string {
	if ns.Valid {
		return ns.String
	}
	return ""
}

func ClearFormFields(db *sql.DB, pdfID int64) error {
	_, err := db.Exec("DELETE FROM form_fields WHERE pdf_id = ?", pdfID)
	return err
}

func UpdateFormFieldQuestion(db *sql.DB, fieldID int64, question string) error {
	_, err := db.Exec("UPDATE form_fields SET question = ? WHERE id = ?", question, fieldID)
	return err
}

func UpdateFormFieldValueGroup(db *sql.DB, fieldID int64, valueGroup string) error {
	_, err := db.Exec("UPDATE form_fields SET value_group = ? WHERE id = ?", valueGroup, fieldID)
	return err
}

func UpdateFormFieldWizardPage(db *sql.DB, fieldID int64, wizardPage int) error {
	_, err := db.Exec("UPDATE form_fields SET wizard_page = ? WHERE id = ?", wizardPage, fieldID)
	return err
}