import sqlite3
import os
import config


def get_db():
    os.makedirs(os.path.dirname(config.DATABASE_PATH), exist_ok=True)
    conn = sqlite3.connect(config.DATABASE_PATH, timeout=10)
    conn.row_factory = sqlite3.Row
    conn.execute('PRAGMA foreign_keys = ON')
    return conn


def init_db():
    conn = get_db()
    conn.executescript(
        """
        CREATE TABLE IF NOT EXISTS Flow (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            description TEXT,
            servicesInfo TEXT,
            fileOriginalName TEXT,
            fileStoredName TEXT,
            fileMimeType TEXT,
            isActive INTEGER NOT NULL DEFAULT 1,
            createdAt TEXT NOT NULL,
            updatedAt TEXT NOT NULL
        );

        CREATE TABLE IF NOT EXISTS Run (
            id TEXT PRIMARY KEY,
            flowId TEXT NOT NULL,
            status TEXT NOT NULL,
            startedAt TEXT NOT NULL,
            finishedAt TEXT,
            raw TEXT,
            createdAt TEXT NOT NULL,
            updatedAt TEXT NOT NULL,
            FOREIGN KEY (flowId) REFERENCES Flow(id)
        );

        CREATE INDEX IF NOT EXISTS idx_run_flow_started ON Run(flowId, startedAt);
        CREATE INDEX IF NOT EXISTS idx_run_status_started ON Run(status, startedAt);
        """
    )
    conn.commit()
    conn.close()