package db

import (
	"database/sql"
	"fmt"
	"log"
	"strings"

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

func InitDB(dbPath string) (*sql.DB, error) {
	db, err := sql.Open("sqlite3", dbPath+"?_busy_timeout=10000")
	if err != nil {
		return nil, fmt.Errorf("opening database: %w", err)
	}

	db.SetMaxOpenConns(25)

	// Enable WAL mode for better performance
	_, err = db.Exec("PRAGMA journal_mode=WAL")
	if err != nil {
		log.Printf("warning: could not enable WAL mode: %v", err)
	}

	// Enable foreign keys
	_, err = db.Exec("PRAGMA foreign_keys = ON")
	if err != nil {
		return nil, fmt.Errorf("enabling foreign keys: %w", err)
	}

	schema := `
	CREATE TABLE IF NOT EXISTS orgs (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE TABLE IF NOT EXISTS org_domains (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		org_id INTEGER NOT NULL,
		domain TEXT NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		UNIQUE(org_id, domain),
		FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT NOT NULL UNIQUE,
		password_hash TEXT NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE TABLE IF NOT EXISTS user_orgs (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		org_id INTEGER NOT NULL,
		UNIQUE(user_id, org_id),
		FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
		FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS sessions (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		user_id INTEGER NOT NULL,
		token TEXT NOT NULL UNIQUE,
		expires_at DATETIME NOT NULL,
		FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS pdfs (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		url TEXT NOT NULL,
		md5_hash TEXT NOT NULL UNIQUE,
		org_id INTEGER,
		status TEXT NOT NULL DEFAULT 'pending',
		error TEXT,
		custom_markdown TEXT,
		llm_status TEXT DEFAULT 'pending',
		llm_error TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (org_id) REFERENCES orgs(id)
	);

	CREATE TABLE IF NOT EXISTS form_fields (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		pdf_id INTEGER NOT NULL,
		field_index INTEGER NOT NULL,
		field_name TEXT NOT NULL,
		type TEXT NOT NULL DEFAULT 'text',
		custom_label TEXT,
		group_id INTEGER,
		llm_suggested_type TEXT,
		FOREIGN KEY (pdf_id) REFERENCES pdfs(id) ON DELETE CASCADE,
		FOREIGN KEY (group_id) REFERENCES field_groups(id) ON DELETE SET NULL
	);

	CREATE TABLE IF NOT EXISTS field_groups (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		pdf_id INTEGER NOT NULL,
		group_type TEXT NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (pdf_id) REFERENCES pdfs(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS field_group_members (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		group_id INTEGER NOT NULL,
		field_id INTEGER NOT NULL,
		UNIQUE(group_id, field_id),
		FOREIGN KEY (group_id) REFERENCES field_groups(id) ON DELETE CASCADE,
		FOREIGN KEY (field_id) REFERENCES form_fields(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS markdown_versions (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		pdf_id INTEGER NOT NULL,
		markdown TEXT NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (pdf_id) REFERENCES pdfs(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS prompt_overrides (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		org_id INTEGER,
		pdf_id INTEGER,
		prompt_type TEXT NOT NULL,
		prompt_content TEXT NOT NULL,
		is_active BOOLEAN NOT NULL DEFAULT 1,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE CASCADE,
		FOREIGN KEY (pdf_id) REFERENCES pdfs(id) ON DELETE CASCADE
	);

	CREATE INDEX IF NOT EXISTS idx_form_fields_pdf_id ON form_fields(pdf_id);
	CREATE INDEX IF NOT EXISTS idx_form_fields_group_id ON form_fields(group_id);
	CREATE INDEX IF NOT EXISTS idx_org_domains_domain ON org_domains(domain);
	CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token);
	CREATE INDEX IF NOT EXISTS idx_user_orgs_user_id ON user_orgs(user_id);
	CREATE INDEX IF NOT EXISTS idx_user_orgs_org_id ON user_orgs(org_id);
	CREATE INDEX IF NOT EXISTS idx_field_groups_pdf_id ON field_groups(pdf_id);
	CREATE INDEX IF NOT EXISTS idx_field_group_members_group_id ON field_group_members(group_id);
	CREATE INDEX IF NOT EXISTS idx_prompt_overrides_org_type ON prompt_overrides(org_id, prompt_type);
	CREATE INDEX IF NOT EXISTS idx_prompt_overrides_pdf_type ON prompt_overrides(pdf_id, prompt_type);
	CREATE INDEX IF NOT EXISTS idx_markdown_versions_pdf_id ON markdown_versions(pdf_id);

	CREATE TABLE IF NOT EXISTS prompt_versions (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		prompt_override_id INTEGER NOT NULL,
		prompt_content TEXT NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		FOREIGN KEY (prompt_override_id) REFERENCES prompt_overrides(id) ON DELETE CASCADE
	);

	CREATE INDEX IF NOT EXISTS idx_prompt_versions_override_id ON prompt_versions(prompt_override_id);
	`

	_, err = db.Exec(schema)
	if err != nil {
		return nil, fmt.Errorf("creating schema: %w", err)
	}

	// Migrations for existing databases
	_, _ = db.Exec("ALTER TABLE prompt_overrides ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT 1")

	return db, nil
}

func isDuplicateColumnError(err error) bool {
	return err != nil && strings.Contains(err.Error(), "duplicate column")
}