<?php
/**
 * Database Explorer API Handler
 * Handles db_query, db_update, db_insert, db_delete actions.
 * Requires admin-level RBAC.
 */
declare(strict_types=1);
if (!defined('DASHBOARD_ROOT')) { http_response_code(403); exit; }

CSRF::enforce();
RBAC::enforce('view.database');

// Read JSON body
$raw = file_get_contents('php://input');
$input = json_decode($raw, true);
if (!$input) {
    exit(json_encode(['error' => 'Invalid request.']));
}

$dbAction = $input['system_action'] ?? '';

// Validate table name against real tables
function dbValidateTable(string $table): bool {
    if (!preg_match('/^[a-zA-Z0-9_]+$/', $table)) return false;
    try {
        $tables = Database::fetchAll("SHOW TABLES");
        foreach ($tables as $row) {
            if (array_values($row)[0] === $table) return true;
        }
    } catch (\Throwable $e) {}
    return false;
}

// Validate column name
function dbValidateColumn(string $table, string $column): bool {
    if (!preg_match('/^[a-zA-Z0-9_]+$/', $column)) return false;
    try {
        $cols = Database::fetchAll("DESCRIBE `{$table}`");
        foreach ($cols as $c) {
            if ($c['Field'] === $column) return true;
        }
    } catch (\Throwable $e) {}
    return false;
}

// ─── Read-only SQL ──────────────────────────────────────
if ($dbAction === 'db_query') {
    $sql = trim($input['sql'] ?? '');
    if (!$sql) exit(json_encode(['error' => 'No SQL provided.']));

    // Only allow read-only statements
    $upper = strtoupper(ltrim($sql));
    $allowed = ['SELECT', 'SHOW', 'DESCRIBE', 'DESC', 'EXPLAIN'];
    $ok = false;
    foreach ($allowed as $kw) {
        if (strpos($upper, $kw) === 0) { $ok = true; break; }
    }
    if (!$ok) {
        exit(json_encode(['error' => 'Only SELECT, SHOW, DESCRIBE, and EXPLAIN queries are allowed.']));
    }

    try {
        $rows = Database::fetchAll($sql);
        // Auto-decrypt encrypted strings for viewing
        foreach ($rows as &$row) {
            foreach ($row as $k => $v) {
                if (is_string($v) && Crypt::isEncrypted($v)) {
                    $row[$k] = Crypt::d($v);
                }
            }
        }
        unset($row);
        exit(json_encode(['rows' => $rows, 'affected' => count($rows)]));
    } catch (\Throwable $e) {
        exit(json_encode(['error' => 'SQL Error: ' . $e->getMessage()]));
    }
}

// ─── Update Row ─────────────────────────────────────────
if ($dbAction === 'db_update') {
    $table = $input['table'] ?? '';
    $pkField = $input['pk_field'] ?? '';
    $pkValue = $input['pk_value'] ?? '';
    $fields = $input['fields'] ?? [];

    if (!dbValidateTable($table)) exit(json_encode(['error' => 'Invalid table.']));
    if (!dbValidateColumn($table, $pkField)) exit(json_encode(['error' => 'Invalid primary key column.']));
    if (empty($fields)) exit(json_encode(['error' => 'No fields to update.']));

    $setParts = [];
    $params = [':pk' => $pkValue];
    $i = 0;
    $secureMap = [
        'users' => ['email', 'username', 'name', 'phone', 'address', 'city', 'province', 'postal_code', 'first_name', 'last_name', 'country'],
        'products' => ['price', 'sale_price', 'base_price', 'display_order', 'unit_price_per', 'unit_increment', 'title', 'description', 'short_desc', 'pitch_title', 'pitch_body', 'notes', 'quality', 'type', 'variant_label', 'unit_label', 'name', 'sku', 'slug'],
        'product_variants' => ['price', 'sale_price', 'sku'],
        'product_sizes' => ['price', 'amount', 'label', 'display_order'],
        'product_qualities' => ['name', 'quality_name', 'base_price', 'display_order'],
        'product_types' => ['name', 'type_name', 'base_price', 'display_order'],
        'categories' => ['name', 'description', 'title']
    ];

    foreach ($fields as $col => $val) {
        if (!dbValidateColumn($table, $col)) continue;
        
        $shouldEncrypt = isset($secureMap[$table]) && in_array($col, $secureMap[$table]);
        
        if ($shouldEncrypt && is_string($val) && $val !== '' && !Crypt::isEncrypted($val)) {
            $domain = ($table === 'users') ? Crypt::DOMAIN_USER : Crypt::DOMAIN_PRODUCT;
            
            // Format numeric prices properly before encryption
            if (in_array($col, ['price', 'sale_price', 'base_price', 'unit_price_per', 'amount'])) {
                $val = (string)(float)$val; 
            }
            
            $val = Crypt::encrypt($val, $domain);
            if ($table === 'users' && $col === 'email') {
                $setParts[] = "`email_hash` = :eh_auto";
                $params[':eh_auto'] = hash('sha256', strtolower(trim(Crypt::d($val))));
            }
        }
        if ($val === null) {
            $setParts[] = "`{$col}` = NULL";
        } else {
            $setParts[] = "`{$col}` = :v{$i}";
            $params[":v{$i}"] = $val;
        }
        $i++;
    }

    if (empty($setParts)) exit(json_encode(['error' => 'No valid fields.']));

    $sql = "UPDATE `{$table}` SET " . implode(', ', $setParts) . " WHERE `{$pkField}` = :pk LIMIT 1";
    try {
        $affected = Database::execute($sql, $params);
        exit(json_encode(['ok' => true, 'affected' => $affected]));
    } catch (\Throwable $e) {
        exit(json_encode(['error' => 'Update failed: ' . $e->getMessage()]));
    }
}

// ─── Insert Row ─────────────────────────────────────────
if ($dbAction === 'db_insert') {
    $table = $input['table'] ?? '';
    $fields = $input['fields'] ?? [];

    if (!dbValidateTable($table)) exit(json_encode(['error' => 'Invalid table.']));
    if (empty($fields)) exit(json_encode(['error' => 'No fields to insert.']));

    $cols = [];
    $vals = [];
    $params = [];
    $i = 0;
    $secureMap = [
        'users' => ['email', 'username', 'name', 'phone', 'address', 'city', 'province', 'postal_code', 'first_name', 'last_name', 'country'],
        'products' => ['price', 'sale_price', 'base_price', 'display_order', 'unit_price_per', 'unit_increment', 'title', 'description', 'short_desc', 'pitch_title', 'pitch_body', 'notes', 'quality', 'type', 'variant_label', 'unit_label', 'name', 'sku', 'slug'],
        'product_variants' => ['price', 'sale_price', 'sku'],
        'product_sizes' => ['price', 'amount', 'label', 'display_order'],
        'product_qualities' => ['name', 'quality_name', 'base_price', 'display_order'],
        'product_types' => ['name', 'type_name', 'base_price', 'display_order'],
        'categories' => ['name', 'description', 'title']
    ];

    foreach ($fields as $col => $val) {
        if (!dbValidateColumn($table, $col)) continue;
        
        $shouldEncrypt = isset($secureMap[$table]) && in_array($col, $secureMap[$table]);
        
        if ($shouldEncrypt && is_string($val) && $val !== '' && !Crypt::isEncrypted($val)) {
            $domain = ($table === 'users') ? Crypt::DOMAIN_USER : Crypt::DOMAIN_PRODUCT;
            
            if (in_array($col, ['price', 'sale_price', 'base_price', 'unit_price_per', 'amount'])) {
                $val = (string)(float)$val; 
            }
            
            $val = Crypt::encrypt($val, $domain);
            if ($table === 'users' && $col === 'email') {
                $cols[] = "`email_hash`";
                $vals[] = ":eh_auto";
                $params[':eh_auto'] = hash('sha256', strtolower(trim(Crypt::d($val))));
            }
        }
        if ($val === '' && $col === 'id') continue; // Skip auto-increment
        $cols[] = "`{$col}`";
        if ($val === null || $val === '') {
            $vals[] = 'NULL';
        } else {
            $vals[] = ":v{$i}";
            $params[":v{$i}"] = $val;
        }
        $i++;
    }

    if (empty($cols)) exit(json_encode(['error' => 'No valid fields.']));

    $sql = "INSERT INTO `{$table}` (" . implode(', ', $cols) . ") VALUES (" . implode(', ', $vals) . ")";
    try {
        $id = Database::insert($sql, $params);
        exit(json_encode(['ok' => true, 'id' => $id]));
    } catch (\Throwable $e) {
        exit(json_encode(['error' => 'Insert failed: ' . $e->getMessage()]));
    }
}

// ─── Delete Row ─────────────────────────────────────────
if ($dbAction === 'db_delete') {
    $table = $input['table'] ?? '';
    $pkField = $input['pk_field'] ?? '';
    $pkValue = $input['pk_value'] ?? '';

    if (!dbValidateTable($table)) exit(json_encode(['error' => 'Invalid table.']));
    if (!dbValidateColumn($table, $pkField)) exit(json_encode(['error' => 'Invalid column.']));

    $sql = "DELETE FROM `{$table}` WHERE `{$pkField}` = :pk LIMIT 1";
    try {
        $affected = Database::execute($sql, [':pk' => $pkValue]);
        exit(json_encode(['ok' => true, 'affected' => $affected]));
    } catch (\Throwable $e) {
        exit(json_encode(['error' => 'Delete failed: ' . $e->getMessage()]));
    }
}

exit(json_encode(['error' => 'Unknown database action.']));
