<?php
// Handle CSV export if requested
if (isset($_GET['export']) && isset($_GET['table'])) {
    exportTableAsCSV($_GET['table']);
    exit;
}

// Function to export table as CSV
function exportTableAsCSV($table_name) {
    // Set headers FIRST before any output
    header('Content-Type: text/csv; charset=utf-8');
    header('Pragma: no-cache');
    header('Expires: 0');
    
    // Database connection details
    $db_url = "postgres://read-only:p147df9f6343b30dc75381d0198126b2851cdee0cb3ca540d2f12769b73c0e302@ec2-3-255-54-175.eu-west-1.compute.amazonaws.com:5432/d5r8pqj7lsdfmc";

    try {
        // Validate table name (basic security check)
        if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $table_name)) {
            throw new Exception('Invalid table name');
        }
        
        // Parse the database URL
        $db_parts = parse_url($db_url);
        $db_host = $db_parts['host'];
        $db_port = $db_parts['port'];
        $db_name = substr($db_parts['path'], 1);
        $db_user = $db_parts['user'];
        $db_pass = $db_parts['pass'];

        // Create PostgreSQL connection
        $conn_string = "host=$db_host port=$db_port dbname=$db_name user=$db_user password=$db_pass sslmode=require";
        $dbconn = pg_connect($conn_string);
        
        if (!$dbconn) {
            throw new Exception('Unable to establish connection with Production Passport database');
        }
        
        // First, get column names
        $column_query = "
            SELECT column_name, data_type 
            FROM information_schema.columns 
            WHERE table_schema = 'public' 
            AND table_name = $1 
            ORDER BY ordinal_position
        ";
        
        $column_result = pg_query_params($dbconn, $column_query, [$table_name]);
        
        if (!$column_result) {
            throw new Exception('Unable to fetch column information: ' . pg_last_error($dbconn));
        }
        
        $columns = [];
        while ($col = pg_fetch_assoc($column_result)) {
            $columns[] = $col['column_name'];
        }
        
        if (empty($columns)) {
            throw new Exception("Table '$table_name' not found or has no columns");
        }
        
        // Get table data with all columns
        $column_list = implode(', ', array_map(function($col) use ($dbconn) {
            return pg_escape_identifier($dbconn, $col);
        }, $columns));
        
        $data_query = "SELECT $column_list FROM public." . pg_escape_identifier($dbconn, $table_name);
        $result = pg_query($dbconn, $data_query);
        
        if (!$result) {
            throw new Exception('Query execution failed: ' . pg_last_error($dbconn));
        }
        
        // Set filename with timestamp
        $filename = $table_name . '_export_' . date('Y-m-d_H-i') . '.csv';
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        
        // Create output stream
        $output = fopen('php://output', 'w');
        fwrite($output, "\xEF\xBB\xBF"); // UTF-8 BOM for Excel compatibility
        
        // Output CSV headers (column names)
        fputcsv($output, $columns);
        
        // Output data rows
        $record_count = 0;
        while ($row = pg_fetch_assoc($result)) {
            $csv_row = [];
            foreach ($columns as $col) {
                $value = $row[$col] ?? '';
                
                // Handle special cases
                if (is_resource($value)) {
                    $value = '(BLOB)';
                } elseif ($value === null) {
                    $value = '';
                } elseif (strlen($value) > 1000) {
                    // Truncate very long values
                    $value = substr($value, 0, 1000) . '... [truncated]';
                }
                
                // Ensure proper encoding
                $csv_row[] = $value;
            }
            fputcsv($output, $csv_row);
            $record_count++;
            
            // Flush buffer every 100 rows to prevent memory issues
            if ($record_count % 100 === 0) {
                flush();
            }
        }
        
        error_log("Table export successful: $table_name - $record_count records");
        
        // Close connections
        fclose($output);
        pg_close($dbconn);
        
        exit;
        
    } catch (Exception $e) {
        // If there was an error, output an error CSV
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="error_report.csv"');
        
        $output = fopen('php://output', 'w');
        fwrite($output, "\xEF\xBB\xBF");
        
        fputcsv($output, ['Error Report - Database Table Export']);
        fputcsv($output, ['']);
        fputcsv($output, ['Status', 'Failed']);
        fputcsv($output, ['Error', $e->getMessage()]);
        fputcsv($output, ['Table', $table_name]);
        fputcsv($output, ['Time', date('Y-m-d H:i:s')]);
        
        fclose($output);
        exit;
    }
}

// Main page - list all tables
$db_url = "postgres://read-only:p147df9f6343b30dc75381d0198126b2851cdee0cb3ca540d2f12769b73c0e302@ec2-3-255-54-175.eu-west-1.compute.amazonaws.com:5432/d5r8pqj7lsdfmc";

try {
    // Parse the database URL
    $db_parts = parse_url($db_url);
    $db_host = $db_parts['host'];
    $db_port = $db_parts['port'];
    $db_name = substr($db_parts['path'], 1);
    $db_user = $db_parts['user'];
    $db_pass = $db_parts['pass'];

    // Create PostgreSQL connection
    $conn_string = "host=$db_host port=$db_port dbname=$db_name user=$db_user password=$db_pass sslmode=require";
    $dbconn = pg_connect($conn_string);
    
    if (!$dbconn) {
        throw new Exception('Unable to establish connection with Production Passport database');
    }
    
    // Get all tables from the public schema
    $query = "
    SELECT 
        table_name,
        obj_description(('public.' || table_name)::regclass, 'pg_class') as table_comment
    FROM information_schema.tables 
    WHERE table_schema = 'public' 
        AND table_type = 'BASE TABLE'
    ORDER BY table_name
    ";
    
    $result = pg_query($dbconn, $query);
    
    if (!$result) {
        // Alternative query if the first one fails
        $query = "SELECT tablename as table_name FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename";
        $result = pg_query($dbconn, $query);
    }
    
    $tables = [];
    while ($row = pg_fetch_assoc($result)) {
        // Get row count for each table
        $table_name = $row['table_name'];
        $count_query = "SELECT COUNT(*) as row_count FROM public." . pg_escape_identifier($dbconn, $table_name);
        $count_result = pg_query($dbconn, $count_query);
        $count_row = pg_fetch_assoc($count_result);
        $row['row_count'] = $count_row ? $count_row['row_count'] : 0;
        
        $tables[] = $row;
    }
    
    pg_close($dbconn);
    
} catch (Exception $e) {
    $error = $e->getMessage();
    $tables = [];
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Database Table Exporter</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.8.1/font/bootstrap-icons.css">
    <style>
        body {
            background-color: #f8f9fa;
            padding: 20px;
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Oxygen, Ubuntu, sans-serif;
        }
        .header {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            padding: 30px 0;
            margin-bottom: 30px;
            border-radius: 10px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        }
        .table-card {
            transition: transform 0.2s, box-shadow 0.2s;
            border: none;
            border-radius: 10px;
            overflow: hidden;
            height: 100%;
            background: white;
        }
        .table-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 25px rgba(0,0,0,0.15);
        }
        .table-header {
            background-color: #f1f3f4;
            padding: 15px;
            font-weight: 600;
            border-bottom: 2px solid #dee2e6;
            color: #2c3e50;
        }
        .btn-download {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            border: none;
            color: white;
            padding: 8px 20px;
            border-radius: 6px;
            transition: all 0.3s;
            font-weight: 500;
            display: flex;
            align-items: center;
            justify-content: center;
            gap: 8px;
        }
        .btn-download:hover {
            background: linear-gradient(135deg, #5a6fd8 0%, #6a4190 100%);
            color: white;
            transform: scale(1.03);
            box-shadow: 0 4px 12px rgba(102, 126, 234, 0.3);
        }
        .table-count {
            background-color: #e9ecef;
            color: #495057;
            padding: 4px 10px;
            border-radius: 15px;
            font-size: 0.85em;
            font-weight: 600;
            display: inline-block;
        }
        .search-box {
            max-width: 400px;
            margin: 0 auto 25px;
        }
        .search-box input {
            border-radius: 25px;
            padding: 12px 20px;
            border: 2px solid #e0e0e0;
            transition: all 0.3s;
        }
        .search-box input:focus {
            border-color: #667eea;
            box-shadow: 0 0 0 0.25rem rgba(102, 126, 234, 0.25);
        }
        .table-icon {
            color: #667eea;
            font-size: 1.2em;
        }
        .database-info {
            background: white;
            border-radius: 8px;
            padding: 15px;
            margin-bottom: 20px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.05);
        }
        .no-results {
            text-align: center;
            padding: 40px;
            color: #6c757d;
        }
        .card-body {
            padding: 20px;
        }
        .card-footer {
            background: #f8f9fa;
            padding: 15px;
            border-top: 1px solid #eee;
        }
        .footer {
            margin-top: 40px;
            padding: 20px;
            text-align: center;
            color: #6c757d;
            font-size: 0.9em;
        }
        @media (max-width: 768px) {
            .header {
                padding: 20px 0;
            }
            .header h1 {
                font-size: 1.8rem;
            }
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="header text-center">
            <h1 class="display-5 mb-3"><i class="bi bi-database"></i> Database Table Exporter</h1>
            <p class="lead mb-0">Browse and export any table as CSV with one click</p>
            <div class="mt-4">
                <span class="badge bg-light text-dark p-2">Total Tables: <?php echo count($tables); ?></span>
                <span class="badge bg-light text-dark p-2 ms-2">Database: <?php echo htmlspecialchars($db_name); ?></span>
            </div>
        </div>

        <?php if (isset($error)): ?>
            <div class="alert alert-danger alert-dismissible fade show" role="alert">
                <i class="bi bi-exclamation-triangle-fill me-2"></i>
                <strong>Connection Error:</strong> <?php echo htmlspecialchars($error); ?>
                <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
            </div>
        <?php endif; ?>

        <div class="database-info">
            <div class="row">
                <div class="col-md-6">
                    <p class="mb-1"><i class="bi bi-server me-2"></i><strong>Host:</strong> <?php echo htmlspecialchars($db_host); ?></p>
                    <p class="mb-1"><i class="bi bi-hdd me-2"></i><strong>Database:</strong> <?php echo htmlspecialchars($db_name); ?></p>
                </div>
                <div class="col-md-6">
                    <p class="mb-1"><i class="bi bi-person me-2"></i><strong>User:</strong> <?php echo htmlspecialchars($db_user); ?></p>
                    <p class="mb-0"><i class="bi bi-clock me-2"></i><strong>Time:</strong> <?php echo date('Y-m-d H:i:s'); ?></p>
                </div>
            </div>
        </div>

        <div class="search-box">
            <div class="input-group">
                <span class="input-group-text bg-white border-end-0">
                    <i class="bi bi-search text-muted"></i>
                </span>
                <input type="text" id="searchInput" class="form-control border-start-0" placeholder="Search tables by name...">
            </div>
        </div>

        <div class="row" id="tablesContainer">
            <?php if (count($tables) > 0): ?>
                <?php foreach ($tables as $table): ?>
                    <div class="col-xl-3 col-lg-4 col-md-6 mb-4 table-item">
                        <div class="card table-card h-100">
                            <div class="table-header">
                                <h6 class="mb-0 d-flex align-items-center">
                                    <i class="bi bi-table table-icon me-2"></i>
                                    <span class="text-truncate" title="<?php echo htmlspecialchars($table['table_name']); ?>">
                                        <?php echo htmlspecialchars($table['table_name']); ?>
                                    </span>
                                </h6>
                            </div>
                            <div class="card-body">
                                <?php if (isset($table['table_comment']) && $table['table_comment']): ?>
                                    <p class="card-text small text-muted"><?php echo htmlspecialchars($table['table_comment']); ?></p>
                                <?php else: ?>
                                    <p class="card-text small text-muted"><em>No description available</em></p>
                                <?php endif; ?>
                                
                                <div class="mb-3">
                                    <span class="table-count">
                                        <i class="bi bi-list-ol me-1"></i>
                                        <?php echo number_format($table['row_count']); ?> rows
                                    </span>
                                </div>
                            </div>
                            <div class="card-footer">
                                <a href="?export=1&table=<?php echo urlencode($table['table_name']); ?>" 
                                   class="btn btn-download w-100" 
                                   onclick="showDownloading(this)">
                                    <i class="bi bi-download"></i> Download CSV
                                </a>
                            </div>
                        </div>
                    </div>
                <?php endforeach; ?>
            <?php else: ?>
                <div class="col-12">
                    <div class="no-results">
                        <i class="bi bi-database-x display-1 text-muted mb-3"></i>
                        <h4>No tables found</h4>
                        <p class="text-muted">Could not retrieve tables from the database.</p>
                    </div>
                </div>
            <?php endif; ?>
        </div>
        
        <div class="footer">
            <p class="mb-0">
                <small>
                    <i class="bi bi-info-circle me-1"></i>
                    Click any table to download as CSV. Files are generated in real-time.
                </small>
            </p>
            <p class="mt-2 mb-0">
                <small class="text-muted">
                    Connection: <?php echo htmlspecialchars($db_host); ?>:<?php echo htmlspecialchars($db_port); ?> 
                    | Schema: public
                </small>
            </p>
        </div>
    </div>

    <!-- Bootstrap JS -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    
    <script>
        // Search functionality
        document.getElementById('searchInput').addEventListener('keyup', function() {
            const searchTerm = this.value.toLowerCase().trim();
            const tableItems = document.querySelectorAll('.table-item');
            let visibleCount = 0;
            
            tableItems.forEach(item => {
                const tableName = item.querySelector('.table-header h6 span').textContent.toLowerCase();
                if (tableName.includes(searchTerm) || searchTerm === '') {
                    item.style.display = 'block';
                    visibleCount++;
                } else {
                    item.style.display = 'none';
                }
            });
            
            // Show no results message if needed
            const noResultsElement = document.querySelector('.no-results');
            if (visibleCount === 0 && tableItems.length > 0) {
                if (!document.getElementById('noResultsMessage')) {
                    const container = document.getElementById('tablesContainer');
                    const messageDiv = document.createElement('div');
                    messageDiv.className = 'col-12 no-results';
                    messageDiv.id = 'noResultsMessage';
                    messageDiv.innerHTML = `
                        <i class="bi bi-search display-1 text-muted mb-3"></i>
                        <h4>No tables found</h4>
                        <p class="text-muted">No tables match your search "<strong>${searchTerm}</strong>"</p>
                    `;
                    container.appendChild(messageDiv);
                }
            } else if (document.getElementById('noResultsMessage')) {
                document.getElementById('noResultsMessage').remove();
            }
        });

        // Show loading state on download buttons
        function showDownloading(button) {
            const originalHTML = button.innerHTML;
            button.innerHTML = `
                <span class="spinner-border spinner-border-sm me-2" role="status" aria-hidden="true"></span>
                Downloading...
            `;
            button.disabled = true;
            
            // Revert after 5 seconds if something goes wrong
            setTimeout(() => {
                button.innerHTML = originalHTML;
                button.disabled = false;
            }, 5000);
        }

        // Add keyboard shortcut for search (Ctrl+F or Cmd+F)
        document.addEventListener('keydown', function(e) {
            if ((e.ctrlKey || e.metaKey) && e.key === 'f') {
                e.preventDefault();
                document.getElementById('searchInput').focus();
            }
        });
    </script>
</body>
</html>