package db

import (
	"database/sql"
	"log"

	_ "modernc.org/sqlite"
)

func Init(path string) (*sql.DB, error) {
	db, err := sql.Open("sqlite", path+"?_foreign_keys=on&_journal_mode=WAL")
	if err != nil {
		return nil, err
	}
	if err := db.Ping(); err != nil {
		return nil, err
	}
	log.Printf("✓ SQLite connecté : %s", path)
	return db, nil
}

func Migrate(db *sql.DB) error {
	migrations := []string{
		sqlCreateUsers,
		sqlCreateProperties,
		sqlCreateCalendarEvents,
		sqlCreateCategories,
		sqlCreateTransactions,
		sqlCreateDocuments,
		sqlCreateFiscalExports,
		sqlCreateIcalSyncLog,
		sqlSeedCategories,
	}
	for _, m := range migrations {
		if _, err := db.Exec(m); err != nil {
			return err
		}
	}
	log.Println("✓ Migrations appliquées")
	return nil
}

const sqlCreateUsers = `
CREATE TABLE IF NOT EXISTS users (
	id           TEXT PRIMARY KEY,
	email        TEXT NOT NULL UNIQUE,
	name         TEXT NOT NULL,
	password_hash TEXT NOT NULL,
	created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
);`

const sqlCreateProperties = `
CREATE TABLE IF NOT EXISTS properties (
	id           TEXT PRIMARY KEY,
	name         TEXT NOT NULL,
	address      TEXT NOT NULL,
	type         TEXT NOT NULL CHECK(type IN ('airbnb','longterm')),
	bank_account TEXT,
	ical_url     TEXT,
	notes        TEXT,
	created_at   DATETIME DEFAULT CURRENT_TIMESTAMP
);`

const sqlCreateCalendarEvents = `
CREATE TABLE IF NOT EXISTS calendar_events (
	id            TEXT PRIMARY KEY,
	property_id   TEXT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
	title         TEXT,
	start_date    DATE NOT NULL,
	end_date      DATE NOT NULL,
	source        TEXT NOT NULL CHECK(source IN ('airbnb','manual')),
	ical_uid      TEXT,
	notes         TEXT,
	created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
	UNIQUE(property_id, ical_uid)
);`

const sqlCreateCategories = `
CREATE TABLE IF NOT EXISTS categories (
	id             TEXT PRIMARY KEY,
	name           TEXT NOT NULL,
	type           TEXT NOT NULL CHECK(type IN ('income','expense')),
	tax_deductible INTEGER DEFAULT 0,
	description    TEXT
);`

const sqlCreateTransactions = `
CREATE TABLE IF NOT EXISTS transactions (
	id          TEXT PRIMARY KEY,
	property_id TEXT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
	category_id TEXT REFERENCES categories(id),
	type        TEXT NOT NULL CHECK(type IN ('income','expense')),
	amount      REAL NOT NULL,
	date        DATE NOT NULL,
	description TEXT,
	created_by  TEXT REFERENCES users(id),
	created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);`

const sqlCreateDocuments = `
CREATE TABLE IF NOT EXISTS documents (
	id             TEXT PRIMARY KEY,
	property_id    TEXT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
	transaction_id TEXT REFERENCES transactions(id) ON DELETE SET NULL,
	filename       TEXT NOT NULL,
	original_name  TEXT NOT NULL,
	file_path      TEXT NOT NULL,
	mime_type      TEXT,
	fiscal_year    INTEGER,
	uploaded_by    TEXT REFERENCES users(id),
	created_at     DATETIME DEFAULT CURRENT_TIMESTAMP
);`

const sqlCreateFiscalExports = `
CREATE TABLE IF NOT EXISTS fiscal_exports (
	id           TEXT PRIMARY KEY,
	property_id  TEXT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
	fiscal_year  INTEGER NOT NULL,
	file_path    TEXT NOT NULL,
	generated_by TEXT REFERENCES users(id),
	generated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);`

const sqlCreateIcalSyncLog = `
CREATE TABLE IF NOT EXISTS ical_sync_log (
	id              TEXT PRIMARY KEY,
	property_id     TEXT NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
	synced_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
	status          TEXT NOT NULL CHECK(status IN ('ok','error')),
	events_imported INTEGER DEFAULT 0,
	error_message   TEXT
);`

// Catégories de base prêtes à l'emploi
const sqlSeedCategories = `
INSERT OR IGNORE INTO categories (id, name, type, tax_deductible) VALUES
	('cat-loyer',        'Loyer perçu',          'income',  0),
	('cat-airbnb',       'Revenu Airbnb',         'income',  0),
	('cat-charges',      'Charges copropriété',   'expense', 1),
	('cat-travaux',      'Travaux & réparations', 'expense', 1),
	('cat-assurance',    'Assurance',             'expense', 1),
	('cat-taxe',         'Taxe foncière',         'expense', 1),
	('cat-interets',     'Intérêts emprunt',      'expense', 1),
	('cat-menage',       'Ménage & entretien',    'expense', 1),
	('cat-gestion',      'Frais de gestion',      'expense', 1),
	('cat-electricite',  'Électricité',           'expense', 1),
	('cat-eau',          'Eau',                   'expense', 1),
	('cat-internet',     'Internet',              'expense', 1),
	('cat-autre-dep',    'Autre dépense',         'expense', 0),
	('cat-autre-rev',    'Autre revenu',          'income',  0);`