package db

import (
	"database/sql"

	_ "modernc.org/sqlite"
)

type DB struct {
	db *sql.DB
}

func Open(path string) (*DB, error) {
	sqldb, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}
	_, err = sqldb.Exec(`
		CREATE TABLE IF NOT EXISTS pages (
			id         INTEGER PRIMARY KEY AUTOINCREMENT,
			path       TEXT NOT NULL UNIQUE,
			html_path  TEXT NOT NULL,
			title      TEXT NOT NULL DEFAULT '',
			date       TEXT DEFAULT '',
			tags       TEXT DEFAULT '[]',
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
		);
		CREATE INDEX IF NOT EXISTS idx_pages_path ON pages(path);
		CREATE INDEX IF NOT EXISTS idx_pages_date ON pages(date);
		CREATE TABLE IF NOT EXISTS posts (
			id         INTEGER PRIMARY KEY AUTOINCREMENT,
			slug       TEXT NOT NULL UNIQUE,
			title      TEXT NOT NULL DEFAULT '',
			date       TEXT DEFAULT '',
			tags       TEXT DEFAULT '[]',
			draft      INTEGER NOT NULL DEFAULT 0,
			blocks     TEXT NOT NULL DEFAULT '[]',
			updated_at INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') * 1000000 as integer))
		);
		CREATE INDEX IF NOT EXISTS idx_posts_slug ON posts(slug);
		CREATE INDEX IF NOT EXISTS idx_posts_date ON posts(date);
		CREATE TABLE IF NOT EXISTS redirects (
			from_slug TEXT PRIMARY KEY,
			to_slug   TEXT NOT NULL
		);
		CREATE TABLE IF NOT EXISTS settings (
			key   TEXT PRIMARY KEY,
			value TEXT NOT NULL DEFAULT ''
		);
		CREATE VIRTUAL TABLE IF NOT EXISTS pages_fts USING fts5(
			path      UNINDEXED,
			title,
			content,
			tokenize = 'porter unicode61'
		);
	`)
	if err != nil {
		return nil, err
	}
	return &DB{db: sqldb}, nil
}

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

// RawDB returns the underlying *sql.DB for advanced queries.
func (d *DB) RawDB() *sql.DB { return d.db }