package db

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

	_ "modernc.org/sqlite"
)

const schema = `
CREATE TABLE IF NOT EXISTS profiles (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	first_name TEXT NOT NULL DEFAULT '',
	last_name TEXT NOT NULL DEFAULT '',
	email TEXT NOT NULL DEFAULT '',
	phone TEXT NOT NULL DEFAULT '',
	location TEXT NOT NULL DEFAULT '',
	website TEXT NOT NULL DEFAULT '',
	summary TEXT NOT NULL DEFAULT '',
	updated_at DATETIME DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS experiences (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	company TEXT NOT NULL DEFAULT '',
	title TEXT NOT NULL DEFAULT '',
	start_date TEXT NOT NULL DEFAULT '',
	end_date TEXT NOT NULL DEFAULT '',
	location TEXT NOT NULL DEFAULT '',
	description TEXT NOT NULL DEFAULT '',
	sort_order INTEGER DEFAULT 0,
	created_at DATETIME DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS educations (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	institution TEXT NOT NULL DEFAULT '',
	degree TEXT NOT NULL DEFAULT '',
	field_of_study TEXT NOT NULL DEFAULT '',
	start_date TEXT NOT NULL DEFAULT '',
	end_date TEXT NOT NULL DEFAULT '',
	location TEXT NOT NULL DEFAULT '',
	gpa TEXT NOT NULL DEFAULT '',
	description TEXT NOT NULL DEFAULT '',
	sort_order INTEGER DEFAULT 0,
	created_at DATETIME DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS skills (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	category TEXT NOT NULL DEFAULT '',
	name TEXT NOT NULL DEFAULT '',
	level TEXT NOT NULL DEFAULT '',
	description TEXT NOT NULL DEFAULT '',
	sort_order INTEGER DEFAULT 0,
	created_at DATETIME DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS work_samples (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	title TEXT NOT NULL DEFAULT '',
	description TEXT NOT NULL DEFAULT '',
	url TEXT NOT NULL DEFAULT '',
	content TEXT NOT NULL DEFAULT '',
	created_at DATETIME DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS css_templates (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL DEFAULT '',
	header_html TEXT NOT NULL DEFAULT '',
	css TEXT NOT NULL DEFAULT '',
	is_active INTEGER DEFAULT 0,
	created_at DATETIME DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS applications (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	company TEXT NOT NULL DEFAULT '',
	position TEXT NOT NULL DEFAULT '',
	job_url TEXT NOT NULL DEFAULT '',
	status TEXT DEFAULT 'new',
	notes TEXT NOT NULL DEFAULT '',
	created_at DATETIME DEFAULT (datetime('now')),
	updated_at DATETIME DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS questions_answers (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	application_id INTEGER NOT NULL,
	question TEXT NOT NULL DEFAULT '',
	answer TEXT NOT NULL DEFAULT '',
	created_at DATETIME DEFAULT (datetime('now')),
	FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS saved_resumes (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	application_id INTEGER NOT NULL,
	yaml_content TEXT NOT NULL DEFAULT '',
	created_at DATETIME DEFAULT (datetime('now')),
	updated_at DATETIME DEFAULT (datetime('now')),
	UNIQUE(application_id),
	FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
);
`

type Database struct {
	db *sql.DB
}

func New(path string) (*Database, error) {
	if err := os.MkdirAll(filepath.Dir(path), 0755); err != nil {
		return nil, fmt.Errorf("failed to create data directory: %w", err)
	}

	sqlDB, err := sql.Open("sqlite", path+"?_journal_mode=WAL&_foreign_keys=on")
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	if _, err := sqlDB.Exec(schema); err != nil {
		return nil, fmt.Errorf("failed to execute schema: %w", err)
	}

	// Run migrations
	if err := runMigrations(sqlDB); err != nil {
		return nil, fmt.Errorf("failed to run migrations: %w", err)
	}

	return &Database{db: sqlDB}, nil
}

func runMigrations(db *sql.DB) error {
	// Add html_template column (new unified template field)
	_, err := db.Exec(`ALTER TABLE css_templates ADD COLUMN html_template TEXT NOT NULL DEFAULT ''`)
	if err != nil && !isColumnAlreadyExists(err) {
		return fmt.Errorf("migration html_template: %w", err)
	}

	// Migrate existing templates: combine header_html + css into html_template
	_, err = db.Exec(`UPDATE css_templates SET html_template =
		'<!DOCTYPE html><html><head><meta charset="UTF-8"><style>' || COALESCE(css, '') ||
		'</style></head><body>' || COALESCE(header_html, '') || '</body></html>'
		WHERE html_template = '' AND (header_html != '' OR css != '')`)
	if err != nil {
		log.Printf("Migration warning: failed to backfill html_template: %v", err)
	}

	// Add job_posting column to applications (inlines job posting into application)
	_, err = db.Exec(`ALTER TABLE applications ADD COLUMN job_posting TEXT NOT NULL DEFAULT ''`)
	if err != nil && !isColumnAlreadyExists(err) {
		return fmt.Errorf("migration job_posting: %w", err)
	}

	return nil
}

func isColumnAlreadyExists(err error) bool {
	if err == nil {
		return false
	}
	msg := err.Error()
	return strings.Contains(msg, "duplicate column") ||
		strings.Contains(msg, "already exists") ||
		strings.Contains(msg, "no such column")
}

func (d *Database) DB() *sql.DB {
	return d.db
}

func (d *Database) Close() error {
	return d.db.Close()
}