<?php
class ScopedDb
{
    private PDO $pdo;
    private string $userId;
    private static array $allowedTables = [
        'user_profiles', 'trading_accounts', 'prop_firm_phases',
        'strategies', 'strategy_versions', 'checklist_items', 'checklist_results',
        'trades', 'partial_closes', 'sl_moves',
        'broker_reconciliations', 'unmatched_broker_trades',
        'screenshot_analyses', 'pine_trades', 'mql5_trades', 'agent_trades',
        'challenge_accounts', 'prop_firm_phase_events',
        'account_goals', 'risk_streak_reviews', 'trade_screenshots', 'colex_screenshots',
    ];

    public function __construct(PDO $pdo, string $userId)
    {
        $this->pdo = $pdo;
        $this->userId = $userId;
    }

    private function safeTable(string $table): string
    {
        if (!in_array($table, self::$allowedTables, true)) {
            throw new \RuntimeException("Table not in allowlist: {$table}");
        }
        return $table;
    }

    public function select(string $table, string $where = '', array $params = [], string $columns = '*', string $orderBy = '', ?int $limit = null, ?int $offset = null): array
    {
        $table = $this->safeTable($table);
        $sql = "SELECT {$columns} FROM {$table} WHERE user_id = :uid";
        if ($where) { $sql .= " AND ({$where})"; }
        if ($orderBy) { $sql .= " ORDER BY {$orderBy}"; }
        if ($limit !== null) { $sql .= " LIMIT {$limit}"; if ($offset !== null) { $sql .= " OFFSET {$offset}"; } }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':uid' => $this->userId] + $params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function selectById(string $table, string $id, string $idColumn = 'id'): ?array
    {
        $table = $this->safeTable($table);
        $sql = "SELECT * FROM {$table} WHERE user_id = :uid AND {$idColumn} = :id LIMIT 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':uid' => $this->userId, ':id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function insert(string $table, array $data): string
    {
        $table = $this->safeTable($table);
        $data['user_id'] = $this->userId;
        if (!isset($data['id'])) { $data['id'] = $this->uuid(); }
        $columns = implode(', ', array_keys($data));
        $placeholders = ':' . implode(', :', array_keys($data));
        $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);
        return $data['id'];
    }

    public function update(string $table, string $id, array $data, string $idColumn = 'id'): bool
    {
        $table = $this->safeTable($table);
        $sets = [];
        $params = [':uid' => $this->userId, ':id' => $id];
        foreach ($data as $col => $val) {
            $key = ":set_{$col}";
            $sets[] = "{$col} = {$key}";
            $params[$key] = $val;
        }
        $sql = "UPDATE {$table} SET " . implode(', ', $sets) . " WHERE user_id = :uid AND {$idColumn} = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount() > 0;
    }

    public function softDelete(string $table, string $id, string $idColumn = 'id'): bool
    {
        return $this->update($table, $id, ['deleted_at' => date('Y-m-d H:i:s')], $idColumn);
    }

    public function count(string $table, string $where = '', array $params = []): int
    {
        $table = $this->safeTable($table);
        $sql = "SELECT COUNT(*) FROM {$table} WHERE user_id = :uid";
        if ($where) { $sql .= " AND ({$where})"; }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':uid' => $this->userId] + $params);
        return (int) $stmt->fetchColumn();
    }

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

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

    public static function uuid(): string
    {
        $data = random_bytes(16);
        $data[6] = chr(ord($data[6]) & 0x0f | 0x40);
        $data[8] = chr(ord($data[8]) & 0x3f | 0x80);
        return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
    }

    public static function mt5CommentHash(string $tradeLifecycleId, string $accountId, string $capturedAt): string
    {
        return substr(hash('sha256', $tradeLifecycleId . $accountId . substr($capturedAt, 0, 10)), 0, 8);
    }
}