from __future__ import annotations

import sqlite3
from pathlib import Path

SCHEMA = """
CREATE TABLE IF NOT EXISTS audio_tracks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    file_path TEXT UNIQUE NOT NULL,
    file_size INTEGER NOT NULL,
    fast_hash TEXT NOT NULL,
    sha256 TEXT,
    duration REAL,
    artist TEXT,
    title TEXT,
    album TEXT,
    format TEXT,
    bitrate INTEGER,
    sample_rate INTEGER,
    bit_depth INTEGER,
    chromaprint TEXT,
    score INTEGER,
    status TEXT NOT NULL DEFAULT 'active',
    inode INTEGER,
    device INTEGER,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    trashed_at TEXT,
    review_status TEXT
);
CREATE INDEX IF NOT EXISTS idx_fast_hash ON audio_tracks(fast_hash);
CREATE INDEX IF NOT EXISTS idx_sha256 ON audio_tracks(sha256);
CREATE INDEX IF NOT EXISTS idx_duration ON audio_tracks(duration);
CREATE INDEX IF NOT EXISTS idx_status ON audio_tracks(status);
CREATE INDEX IF NOT EXISTS idx_duration_size ON audio_tracks(duration, file_size);
CREATE TABLE IF NOT EXISTS operations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action TEXT NOT NULL,
    file_path TEXT NOT NULL,
    winner_path TEXT,
    details TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_operations_created ON operations(created_at);
CREATE TABLE IF NOT EXISTS jobs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    kind TEXT NOT NULL,
    status TEXT NOT NULL,
    started_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finished_at TEXT,
    result TEXT,
    error TEXT
);
CREATE INDEX IF NOT EXISTS idx_jobs_started ON jobs(started_at);
"""


def connect(path: Path) -> sqlite3.Connection:
    path.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    conn.execute("PRAGMA busy_timeout=5000")
    conn.executescript(SCHEMA)
    cols={r[1] for r in conn.execute("PRAGMA table_info(audio_tracks)")}
    if "version_flags" not in cols:
        conn.execute("ALTER TABLE audio_tracks ADD COLUMN version_flags TEXT")
    return conn