<?php
// Enable error reporting (disable display_errors in production!)
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// ────────────────────────────────────────────────
//          DATABASE CREDENTIALS – CHANGE THESE
//          (keep these values secret!)
$host     = 'localhost';
$dbname   = 'owekczzo_quan';
$username = 'owekczzo_quan';
$password = 'owekczzo_quan';     // ← Replace with your actual password
// ────────────────────────────────────────────────

try {
    $conn = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $username,
        $password
    );
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die("Connection failed: " . htmlspecialchars($e->getMessage()));
}

// Settings
$rowsPerPage = 20; // ← You can change this (10, 25, 50, etc.)
$page = isset($_GET['page']) && is_numeric($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$offset = ($page - 1) * $rowsPerPage;

$tableName = isset($_GET['table']) ? trim($_GET['table']) : null;

// Helper: display message
function displayMessage($type, $message) {
    $classes = [
        'success' => 'alert alert-success',
        'error'   => 'alert alert-error',
        'warning' => 'alert alert-warning'
    ];
    $class = $classes[$type] ?? 'alert';
    echo "<div class=\"$class\">" . htmlspecialchars($message) . "</div>";
}

// ────────────────────────────────────────────────
// Handle POST actions
$action = $_POST['action'] ?? '';

if ($action === 'edit' && !empty($_POST['id'])) {
    $id     = $_POST['id'];
    $sets   = [];
    $params = [];

    foreach ($_POST as $key => $value) {
        if (str_starts_with($key, 'value_')) {
            $column   = substr($key, 6);
            $sets[]   = "`$column` = ?";
            $params[] = $value;
        }
    }

    if ($sets) {
        $params[] = $id;
        $sql = "UPDATE `$tableName` SET " . implode(', ', $sets) . " WHERE id = ?";
        $stmt = $conn->prepare($sql);
        $stmt->execute($params);
        displayMessage('success', 'Record updated successfully');
    } else {
        displayMessage('warning', 'No fields were changed.');
    }
}

if ($action === 'delete' && !empty($_POST['id'])) {
    $stmt = $conn->prepare("DELETE FROM `$tableName` WHERE id = ?");
    $stmt->execute([$_POST['id']]);
    displayMessage('success', 'Record deleted successfully');
}

if ($action === 'insert') {
    $columns      = [];
    $placeholders = [];
    $values       = [];

    foreach ($_POST as $key => $value) {
        if (str_starts_with($key, 'new_value_')) {
            $column       = substr($key, 10);
            $columns[]     = "`$column`";
            $placeholders[] = '?';
            $values[]      = $value;
        }
    }

    if ($columns) {
        $sql = "INSERT INTO `$tableName` (" . implode(', ', $columns) . ")
                VALUES (" . implode(', ', $placeholders) . ")";
        $stmt = $conn->prepare($sql);
        $stmt->execute($values);
        displayMessage('success', 'Record inserted successfully');
    } else {
        displayMessage('warning', 'No data to insert.');
    }
}

if ($action === 'export' && !empty($_POST['columns'])) {
    $cols = array_map(fn($c) => "`$c`", $_POST['columns']);
    $sql  = "SELECT " . implode(', ', $cols) . " FROM `$tableName`";
    $stmt = $conn->prepare($sql);
    $stmt->execute();
    $rows = $stmt->fetchAll();

    if ($rows) {
        $filename = $tableName . '_export_' . date('Y-m-d') . '.csv';
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        $out = fopen('php://output', 'w');
        fputcsv($out, $_POST['columns']);
        foreach ($rows as $row) {
            fputcsv($out, $row);
        }
        fclose($out);
        exit;
    } else {
        displayMessage('warning', 'No data to export.');
    }
}

// ────────────────────────────────────────────────
// Get all tables
$tables = $conn->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);

// Get current table data + pagination info
$totalRows = 0;
$data      = [];
$fields    = [];

if ($tableName) {
    // Get total count
    $stmt = $conn->prepare("SELECT COUNT(*) FROM `$tableName`");
    $stmt->execute();
    $totalRows = (int) $stmt->fetchColumn();

    // Get paginated data – FIXED with explicit integer binding
    $stmt = $conn->prepare("SELECT * FROM `$tableName` LIMIT :limit OFFSET :offset");
    $stmt->bindValue(':limit',  $rowsPerPage, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset,      PDO::PARAM_INT);
    $stmt->execute();
    $data = $stmt->fetchAll();

    if ($data) {
        $fields = array_keys($data[0]);
    }
}

// Pagination math
$totalPages = max(1, ceil($totalRows / $rowsPerPage));
$startRow   = $offset + 1;
$endRow     = min($offset + $rowsPerPage, $totalRows);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Database Table Editor</title>
    <style>
        :root {
            --primary: #2c5282;
            --primary-dark: #2a4365;
            --success: #38a169;
            --danger: #e53e3e;
            --warning: #d69e2e;
            --light: #f7fafc;
            --gray: #718096;
        }
        body {
            font-family: 'Segoe UI', system-ui, sans-serif;
            background: linear-gradient(135deg, #edf2f7, #e2e8f0);
            margin: 0;
            padding: 20px;
            color: #2d3748;
        }
        .container {
            max-width: 1400px;
            margin: 0 auto;
            background: white;
            border-radius: 12px;
            box-shadow: 0 10px 25px rgba(0,0,0,0.08);
            padding: 2rem;
        }
        h1, h2 { color: #2d3748; margin-bottom: 1rem; }
        table {
            width: 100%;
            border-collapse: collapse;
            margin: 1.5rem 0;
            border-radius: 8px;
            overflow: hidden;
            box-shadow: 0 2px 8px rgba(0,0,0,0.06);
        }
        th {
            background: var(--primary);
            color: white;
            padding: 12px 15px;
            font-weight: 600;
            text-align: left;
        }
        td { padding: 12px 15px; border-bottom: 1px solid #e2e8f0; }
        tr:nth-child(even) { background: #f8fafc; }
        tr:hover { background: #edf2f7; }
        .pagination {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin: 1.5rem 0;
            flex-wrap: wrap;
            gap: 1rem;
        }
        .page-numbers a, .page-numbers span {
            padding: 8px 14px;
            margin: 0 4px;
            border-radius: 6px;
            text-decoration: none;
            background: #edf2f7;
            color: #2d3748;
        }
        .page-numbers a:hover { background: var(--primary); color: white; }
        .page-numbers .current { background: var(--primary); color: white; font-weight: bold; }
        .alert {
            padding: 1rem;
            margin: 1rem 0;
            border-radius: 8px;
            font-weight: 500;
        }
        .alert-success { background: #e6fffa; border-left: 5px solid var(--success); }
        .alert-error   { background: #fff5f5; border-left: 5px solid var(--danger); }
        .alert-warning { background: #fefcbf; border-left: 5px solid var(--warning); }
        .btn {
            background: var(--primary);
            color: white;
            border: none;
            padding: 10px 18px;
            border-radius: 6px;
            cursor: pointer;
            font-weight: 500;
            transition: 0.2s;
        }
        .btn:hover { background: var(--primary-dark); }
        .btn-delete { background: var(--danger); }
        .btn-delete:hover { background: #c53030; }
        .table-list { display: flex; flex-wrap: wrap; gap: 1rem; margin-bottom: 2rem; }
        .table-btn {
            padding: 10px 16px;
            background: #edf2f7;
            border-radius: 8px;
            text-decoration: none;
            color: #2d3748;
        }
        .table-btn.active {
            background: #bee3f8;
            font-weight: 600;
        }
        input[type="text"] {
            padding: 9px 12px;
            border: 1px solid #e2e8f0;
            border-radius: 6px;
            width: 100%;
            box-sizing: border-box;
        }
        .form-inline { display: flex; flex-wrap: wrap; gap: 1.2rem; align-items: flex-end; }
        .checkbox-list { column-count: 3; max-height: 220px; overflow-y: auto; }
    </style>
</head>
<body>

<div class="container">
    <h1>Database Table Editor</h1>

    <h2>Available Tables</h2>
    <div class="table-list">
        <?php foreach ($tables as $t): ?>
            <a href="?table=<?= urlencode($t) ?>"
               class="table-btn <?= $tableName === $t ? 'active' : '' ?>">
                <?= htmlspecialchars($t) ?>
            </a>
        <?php endforeach; ?>
    </div>

    <?php if ($tableName): ?>
        <h2>Table: <?= htmlspecialchars($tableName) ?>
            <small>(<?= number_format($totalRows) ?> rows)</small>
        </h2>

        <?php if ($totalRows > 0): ?>
            <div class="pagination">
                <div>
                    Showing <strong><?= $startRow ?>–<?= $endRow ?></strong> of <?= number_format($totalRows) ?>
                </div>
                <div class="page-numbers">
                    <?php if ($page > 1): ?>
                        <a href="?table=<?= urlencode($tableName) ?>&page=<?= $page-1 ?>">« Prev</a>
                    <?php endif; ?>

                    <?php
                    $range = 2;
                    $start = max(1, $page - $range);
                    $end   = min($totalPages, $page + $range);

                    if ($start > 1) {
                        echo '<a href="?table='.urlencode($tableName).'&page=1">1</a> … ';
                    }
                    for ($i = $start; $i <= $end; $i++) {
                        $class = ($i === $page) ? 'current' : '';
                        echo "<a href=\"?table=" . urlencode($tableName) . "&page=$i\" class=\"$class\">$i</a> ";
                    }
                    if ($end < $totalPages) {
                        echo '… <a href="?table='.urlencode($tableName).'&page='.$totalPages.'">'.$totalPages.'</a>';
                    }
                    ?>

                    <?php if ($page < $totalPages): ?>
                        <a href="?table=<?= urlencode($tableName) ?>&page=<?= $page+1 ?>">Next »</a>
                    <?php endif; ?>
                </div>
            </div>
        <?php endif; ?>

        <?php if ($data): ?>
            <table>
                <thead>
                    <tr>
                        <?php foreach ($fields as $f): ?>
                            <th><?= htmlspecialchars($f) ?></th>
                        <?php endforeach; ?>
                        <th style="width:160px;">Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($data as $row): ?>
                        <tr>
                            <form method="post" style="display:contents;">
                                <input type="hidden" name="action" value="edit">
                                <input type="hidden" name="id" value="<?= htmlspecialchars((string)($row['id'] ?? '')) ?>">
                                <?php foreach ($row as $col => $val): ?>
                                    <td>
                                       <input type="text" name="value_<?= htmlspecialchars($col) ?>"
       value="<?= htmlspecialchars((string)($val ?? '')) ?>">
                                    </td>
                                <?php endforeach; ?>
                                <td>
                                    <button type="submit" class="btn">Update</button>
                            </form>
                            <form method="post" style="display:inline;"
                                  onsubmit="return confirm('Delete this record permanently?');">
                                <input type="hidden" name="action" value="delete">
                                <input type="hidden" name="id" value="<?= htmlspecialchars((string)($row['id'] ?? '')) ?>">
                                <button type="submit" class="btn btn-delete">Delete</button>
                            </form>
                                </td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        <?php else: ?>
            <p style="color:var(--gray); font-style:italic;">This table is empty or does not contain any records.</p>
        <?php endif; ?>

        <h2>Insert New Record</h2>
        <form method="post" class="form-inline">
            <input type="hidden" name="action" value="insert">
            <?php foreach ($fields as $f): ?>
                <?php if ($f !== 'id'): ?>
                    <div>
                        <label style="display:block; margin-bottom:4px; font-weight:500;">
                            <?= htmlspecialchars($f) ?>
                        </label>
                        <input type="text" name="new_value_<?= htmlspecialchars($f) ?>">
                    </div>
                <?php endif; ?>
            <?php endforeach; ?>
            <button type="submit" class="btn">Insert Record</button>
        </form>

        <h2>Export Data</h2>
        <form method="post" class="form-inline">
            <input type="hidden" name="action" value="export">
            <div>
                <strong>Select columns to export:</strong><br>
                <div class="checkbox-list">
                    <?php foreach ($fields as $f): ?>
                        <label>
                            <input type="checkbox" name="columns[]" value="<?= htmlspecialchars($f) ?>" checked>
                            <?= htmlspecialchars($f) ?>
                        </label>
                    <?php endforeach; ?>
                </div>
            </div>
            <button type="submit" class="btn">Export to CSV</button>
        </form>

    <?php endif; ?>
</div>

</body>
</html>