<?php
declare(strict_types=1);

class Database
{
    private static ?Database $instance = null;
    private PDO $pdo;

    private function __construct()
    {
        $dbPath = DATA_PATH . '/database.sqlite';
        // Track first-creation so we can restrict perms on the brand-new file. PDO
        // creates the SQLite file lazily — without this guard we'd chmod on every
        // boot and stomp on any tighter permissions the operator may have set.
        $isNew = !file_exists($dbPath);
        $this->pdo = new PDO('sqlite:' . $dbPath, options: [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
        if ($isNew && file_exists($dbPath)) {
            // 0640: owner rw, group r, world none. Defends against world-readable
            // umask defaults (some distros umask 022 → 0644). Survives the next
            // boot because we only touch perms on first creation.
            @chmod($dbPath, 0640);
        }
        $this->pdo->exec('PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;');
        // SQLite WAL/SHM sidecar files: tighten only when world has any access bit set,
        // never relaxing operator-set tighter perms.
        foreach (['-wal', '-shm'] as $suffix) {
            $sidecar = $dbPath . $suffix;
            if (file_exists($sidecar) && (fileperms($sidecar) & 0007) !== 0) {
                @chmod($sidecar, 0640);
            }
        }

        // Incremental migrations – safe to run on every boot
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS task_dependencies (
                predecessor_id INTEGER NOT NULL,
                successor_id   INTEGER NOT NULL,
                PRIMARY KEY (predecessor_id, successor_id),
                FOREIGN KEY (predecessor_id) REFERENCES tasks(id) ON DELETE CASCADE,
                FOREIGN KEY (successor_id)   REFERENCES tasks(id) ON DELETE CASCADE
            );
        ");

        // task_assignees migration
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS task_assignees (
                id            INTEGER PRIMARY KEY AUTOINCREMENT,
                task_id       INTEGER NOT NULL,
                user_id       INTEGER,
                external_name TEXT,
                external_id   INTEGER,
                FOREIGN KEY (task_id)     REFERENCES tasks(id)             ON DELETE CASCADE,
                FOREIGN KEY (user_id)     REFERENCES users(id)             ON DELETE CASCADE,
                FOREIGN KEY (external_id) REFERENCES project_externals(id) ON DELETE CASCADE
            );
        ");
        // Idempotent: migrate existing tasks.assigned_to → task_assignees
        // Wrapped in try/catch because tasks table does not exist on a fresh install
        try {
            $this->pdo->exec("
                INSERT INTO task_assignees (task_id, user_id)
                SELECT id, assigned_to FROM tasks
                WHERE assigned_to IS NOT NULL
                AND NOT EXISTS (
                    SELECT 1 FROM task_assignees ta2
                    WHERE ta2.task_id = tasks.id AND ta2.user_id = tasks.assigned_to
                );
            ");
        } catch (\Throwable) {}

        // TOTP columns on users table
        try { $this->pdo->exec("ALTER TABLE users ADD COLUMN totp_secret TEXT"); } catch (\Throwable) {}
        try { $this->pdo->exec("ALTER TABLE users ADD COLUMN totp_enabled INTEGER NOT NULL DEFAULT 0"); } catch (\Throwable) {}
        // Replay protection: store last successfully consumed TOTP counter
        try { $this->pdo->exec("ALTER TABLE users ADD COLUMN totp_last_counter INTEGER NOT NULL DEFAULT 0"); } catch (\Throwable) {}

        // Encrypt any plaintext TOTP secrets at rest. The SQL filter `NOT LIKE 'enc1:%'`
        // ensures already-encrypted rows are skipped at the DB layer so this loop is
        // a no-op on every boot once migration has run.
        try {
            $stmt = $this->pdo->query("SELECT id, totp_secret FROM users WHERE totp_secret IS NOT NULL AND totp_secret != '' AND totp_secret NOT LIKE 'enc1:%'");
            $upd  = null;
            foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $row) {
                $upd ??= $this->pdo->prepare("UPDATE users SET totp_secret=? WHERE id=?");
                $upd->execute([Crypto::encrypt($row['totp_secret']), $row['id']]);
            }
        } catch (\Throwable) {}

        // External auth providers (LDAP, future OIDC). 'local' for password-based
        // accounts, 'ldap' for accounts authenticated against an LDAP/AD directory.
        // external_id holds the provider-specific stable identifier (LDAP DN).
        // ldap_synced_at is the unix timestamp of the last successful directory
        // sync for this user; used by the cron sweep to flip vanished users to
        // is_active=0.
        try { $this->pdo->exec("ALTER TABLE users ADD COLUMN auth_provider  TEXT NOT NULL DEFAULT 'local'"); } catch (\Throwable) {}
        try { $this->pdo->exec("ALTER TABLE users ADD COLUMN external_id    TEXT"); } catch (\Throwable) {}
        try { $this->pdo->exec("ALTER TABLE users ADD COLUMN ldap_synced_at INTEGER"); } catch (\Throwable) {}
        try { $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_users_auth_provider ON users(auth_provider, external_id)"); } catch (\Throwable) {}

        // Indexes for the cmd-k people-search counter and the managed-tasks
        // detail view: COUNT/SELECT over task_assignees by user_id / external_id.
        try { $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_task_assignees_user     ON task_assignees(user_id)"); } catch (\Throwable) {}
        try { $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_task_assignees_external ON task_assignees(external_id)"); } catch (\Throwable) {}

        // Inactive-account cleanup. Tracks the moment a "your account will be
        // deleted in N days" warning mail was sent. NULL means no pending
        // deletion countdown — set on every successful login so the user is
        // considered "active again" the next morning.
        try { $this->pdo->exec("ALTER TABLE users ADD COLUMN inactive_warning_sent_at INTEGER"); } catch (\Throwable) {}

        // Notification settings – new columns
        try { $this->pdo->exec("ALTER TABLE project_notification_settings ADD COLUMN notify_assignee_on_comment    INTEGER NOT NULL DEFAULT 1"); } catch (\Throwable) {}
        try { $this->pdo->exec("ALTER TABLE project_notification_settings ADD COLUMN notify_assignee_on_change     INTEGER NOT NULL DEFAULT 1"); } catch (\Throwable) {}
        try { $this->pdo->exec("ALTER TABLE project_notification_settings ADD COLUMN notify_assignee_on_due_day    INTEGER NOT NULL DEFAULT 1"); } catch (\Throwable) {}
        try { $this->pdo->exec("ALTER TABLE project_notification_settings ADD COLUMN notify_assignee_on_unassign   INTEGER NOT NULL DEFAULT 1"); } catch (\Throwable) {}
        try { $this->pdo->exec("ALTER TABLE project_notification_settings ADD COLUMN notify_assignee_on_attachment INTEGER NOT NULL DEFAULT 1"); } catch (\Throwable) {}
        try { $this->pdo->exec("ALTER TABLE project_notification_settings ADD COLUMN notify_manager_task_overdue   INTEGER NOT NULL DEFAULT 1"); } catch (\Throwable) {}
        try { $this->pdo->exec("ALTER TABLE project_notification_settings ADD COLUMN notify_manager_task_created   INTEGER NOT NULL DEFAULT 1"); } catch (\Throwable) {}
        // Manager toggle: separate from notify_manager_any_activity. Off by default
        // so the flood of assignee-change pings only happens when explicitly enabled.
        try { $this->pdo->exec("ALTER TABLE project_notification_settings ADD COLUMN notify_manager_assignees_changed INTEGER NOT NULL DEFAULT 0"); } catch (\Throwable) {}

        // AI messages table
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS ai_messages (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                user_id    INTEGER NOT NULL,
                role       TEXT NOT NULL,
                content    TEXT NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
                FOREIGN KEY (user_id)    REFERENCES users(id)    ON DELETE CASCADE
            );
        ");

        // Outbound mail queue. Web requests write rows here instead of doing
        // SMTP synchronously; the cron worker dispatches pending rows on
        // each tick. Decouples the user-facing request latency from SMTP
        // round-trips and survives PHP-FPM restarts. CLI / cron callers
        // bypass the queue and send directly via Mailer::sendSync.
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS mail_queue (
                id              INTEGER PRIMARY KEY AUTOINCREMENT,
                to_addrs        TEXT NOT NULL,
                subject         TEXT NOT NULL,
                html_body       TEXT NOT NULL,
                text_body       TEXT NOT NULL DEFAULT '',
                created_at      INTEGER NOT NULL,
                attempts        INTEGER NOT NULL DEFAULT 0,
                last_attempt_at INTEGER,
                last_error      TEXT,
                sent_at         INTEGER
            );
        ");
        try { $this->pdo->exec("CREATE INDEX IF NOT EXISTS idx_mail_queue_pending ON mail_queue(sent_at, attempts)"); } catch (\Throwable) {}

        // Always reflect the current code version in settings
        // Wrapped in try/catch because settings table does not exist on a fresh install
        try {
            $version = defined('APP_VERSION') ? APP_VERSION : 'unknown';
            $stmt = $this->pdo->prepare("INSERT OR REPLACE INTO settings (key, value) VALUES ('app_version', ?)");
            $stmt->execute([$version]);
        } catch (\Throwable) {}
    }

    public static function getInstance(): static
    {
        if (self::$instance === null) {
            self::$instance = new static();
        }
        return self::$instance;
    }

    /**
     * Drop the singleton so the next `getInstance()` call re-opens the DB.
     * Used by `Backup::restore()` after the data/database.sqlite file has
     * been swapped out from underneath the running process.
     */
    public static function reset(): void
    {
        self::$instance = null;
    }

    public function getPdo(): PDO
    {
        return $this->pdo;
    }

    public function query(string $sql, array $params = []): PDOStatement
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    public function fetch(string $sql, array $params = []): array|false
    {
        $row = $this->query($sql, $params)->fetch();
        return $row !== false ? $this->decryptRow($row) : false;
    }

    public function fetchAll(string $sql, array $params = []): array
    {
        return array_map([$this, 'decryptRow'], $this->query($sql, $params)->fetchAll());
    }

    private function decryptRow(array $row): array
    {
        if (isset($row['totp_secret']) && $row['totp_secret'] !== null && $row['totp_secret'] !== '') {
            $row['totp_secret'] = Crypto::decrypt($row['totp_secret']);
        }
        return $row;
    }

    public function insert(string $sql, array $params = []): int|string
    {
        $this->query($sql, $params);
        return $this->pdo->lastInsertId();
    }

    public function execute(string $sql, array $params = []): int
    {
        return $this->query($sql, $params)->rowCount();
    }

    public function lastInsertId(): string
    {
        return $this->pdo->lastInsertId();
    }

    public function beginTransaction(): void
    {
        $this->pdo->beginTransaction();
    }

    public function commit(): void
    {
        $this->pdo->commit();
    }

    public function rollback(): void
    {
        $this->pdo->rollBack();
    }

    public static function schema(): void
    {
        $db = self::getInstance();
        $pdo = $db->getPdo();

        $pdo->exec("
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE,
                password TEXT NOT NULL,
                role TEXT DEFAULT 'user',
                language TEXT DEFAULT 'de',
                timezone TEXT,
                theme TEXT DEFAULT 'light',
                avatar TEXT,
                is_active INTEGER DEFAULT 1,
                totp_secret TEXT,
                totp_enabled INTEGER NOT NULL DEFAULT 0,
                reset_token TEXT,
                reset_token_expires INTEGER,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                last_login DATETIME
            );

            CREATE TABLE IF NOT EXISTS projects (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                description TEXT,
                owner_id INTEGER NOT NULL,
                status TEXT DEFAULT 'active',
                color TEXT DEFAULT '#4f46e5',
                default_view TEXT DEFAULT 'kanban',
                required_fields TEXT DEFAULT '[]',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (owner_id) REFERENCES users(id)
            );

            CREATE TABLE IF NOT EXISTS project_members (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                user_id INTEGER NOT NULL,
                role TEXT NOT NULL DEFAULT 'member',
                joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                UNIQUE(project_id, user_id)
            );

            CREATE TABLE IF NOT EXISTS task_statuses (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                name TEXT NOT NULL,
                color TEXT DEFAULT '#6b7280',
                position INTEGER DEFAULT 0,
                is_done INTEGER DEFAULT 0,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS labels (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                name TEXT NOT NULL,
                color TEXT DEFAULT '#6b7280',
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS tasks (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                parent_id INTEGER,
                title TEXT NOT NULL,
                description TEXT,
                status_id INTEGER,
                priority TEXT DEFAULT 'medium',
                assigned_to INTEGER,
                created_by INTEGER NOT NULL,
                due_date DATE,
                start_date DATE,
                position INTEGER DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
                FOREIGN KEY (parent_id) REFERENCES tasks(id) ON DELETE SET NULL,
                FOREIGN KEY (status_id) REFERENCES task_statuses(id),
                FOREIGN KEY (assigned_to) REFERENCES users(id),
                FOREIGN KEY (created_by) REFERENCES users(id)
            );

            CREATE TABLE IF NOT EXISTS task_labels (
                task_id INTEGER NOT NULL,
                label_id INTEGER NOT NULL,
                PRIMARY KEY (task_id, label_id),
                FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
                FOREIGN KEY (label_id) REFERENCES labels(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS project_externals (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                name       TEXT NOT NULL,
                email      TEXT,
                token      TEXT UNIQUE,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
                UNIQUE(project_id, email)
            );

            CREATE TABLE IF NOT EXISTS comments (
                id            INTEGER PRIMARY KEY AUTOINCREMENT,
                task_id       INTEGER NOT NULL,
                user_id       INTEGER,
                external_id   INTEGER,
                external_name TEXT,
                content       TEXT NOT NULL,
                is_edited     INTEGER DEFAULT 0,
                created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (task_id)     REFERENCES tasks(id)             ON DELETE CASCADE,
                FOREIGN KEY (user_id)     REFERENCES users(id)             ON DELETE SET NULL,
                FOREIGN KEY (external_id) REFERENCES project_externals(id) ON DELETE SET NULL
            );

            CREATE TABLE IF NOT EXISTS attachments (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                task_id INTEGER NOT NULL,
                user_id INTEGER NOT NULL,
                filename TEXT NOT NULL,
                original_name TEXT NOT NULL,
                file_size INTEGER,
                mime_type TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
                FOREIGN KEY (user_id) REFERENCES users(id)
            );

            CREATE TABLE IF NOT EXISTS activity_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER,
                task_id INTEGER,
                user_id INTEGER,
                action TEXT NOT NULL,
                description TEXT,
                old_value TEXT,
                new_value TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id)
            );

            CREATE TABLE IF NOT EXISTS notifications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                title TEXT NOT NULL,
                message TEXT NOT NULL,
                type TEXT DEFAULT 'info',
                is_read INTEGER DEFAULT 0,
                related_type TEXT,
                related_id INTEGER,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id)
            );

            CREATE TABLE IF NOT EXISTS settings (
                key TEXT PRIMARY KEY,
                value TEXT
            );

            CREATE TABLE IF NOT EXISTS project_notification_settings (
                project_id                      INTEGER PRIMARY KEY,
                notify_assignee_on_assign       INTEGER NOT NULL DEFAULT 1,
                notify_assignee_reminder        INTEGER NOT NULL DEFAULT 1,
                notify_assignee_predecessors    INTEGER NOT NULL DEFAULT 1,
                notify_assignee_on_comment      INTEGER NOT NULL DEFAULT 1,
                notify_assignee_on_change       INTEGER NOT NULL DEFAULT 1,
                notify_assignee_on_due_day      INTEGER NOT NULL DEFAULT 1,
                notify_assignee_on_unassign     INTEGER NOT NULL DEFAULT 1,
                notify_assignee_on_attachment   INTEGER NOT NULL DEFAULT 1,
                notify_manager_any_activity     INTEGER NOT NULL DEFAULT 0,
                notify_manager_task_done        INTEGER NOT NULL DEFAULT 1,
                notify_manager_task_overdue     INTEGER NOT NULL DEFAULT 1,
                notify_manager_task_created     INTEGER NOT NULL DEFAULT 1,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS task_dependencies (
                predecessor_id INTEGER NOT NULL,
                successor_id   INTEGER NOT NULL,
                PRIMARY KEY (predecessor_id, successor_id),
                FOREIGN KEY (predecessor_id) REFERENCES tasks(id) ON DELETE CASCADE,
                FOREIGN KEY (successor_id)   REFERENCES tasks(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS task_assignees (
                id            INTEGER PRIMARY KEY AUTOINCREMENT,
                task_id       INTEGER NOT NULL,
                user_id       INTEGER,
                external_name TEXT,
                external_id   INTEGER,
                FOREIGN KEY (task_id)     REFERENCES tasks(id)             ON DELETE CASCADE,
                FOREIGN KEY (user_id)     REFERENCES users(id)             ON DELETE CASCADE,
                FOREIGN KEY (external_id) REFERENCES project_externals(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS domain_languages (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                domain      TEXT NOT NULL UNIQUE,
                language    TEXT NOT NULL,
                created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
            );
            CREATE INDEX IF NOT EXISTS idx_domain_languages_domain ON domain_languages(domain);

            CREATE TABLE IF NOT EXISTS security_events (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                event_type  TEXT NOT NULL,
                severity    TEXT NOT NULL DEFAULT 'info',
                user_id     INTEGER,
                ip          TEXT,
                details     TEXT,
                created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
            );
            CREATE INDEX IF NOT EXISTS idx_security_events_type    ON security_events(event_type);
            CREATE INDEX IF NOT EXISTS idx_security_events_user    ON security_events(user_id);
            CREATE INDEX IF NOT EXISTS idx_security_events_created ON security_events(created_at);

            CREATE TABLE IF NOT EXISTS applied_migrations (
                id          TEXT PRIMARY KEY,
                applied_at  DATETIME DEFAULT CURRENT_TIMESTAMP
            );
        ");

        // Fresh-install fast-forward: any versioned migration in /migrations/ describes
        // schema state already produced by the declarative block above. Mark them all as
        // applied without running their bodies. Existing installs upgrading later will
        // never reach this code path (DB already exists, schema() is not called).
        if (class_exists('Migrator')) {
            Migrator::markAllApplied();
        }
    }
}
