<?php
/**
 * Database Connection with Secure PDO Configuration
 * Follows best practices for security, error handling, and UTF-8 support
 */

// Database configuration
$dsn = 'mysql:host=localhost;dbname=tempduzq_stsimageqr;charset=utf8mb4';
$user = 'tempduzq_stsimageqr';
$pass = 'epLAO5}-5]V6';

// PDO options for security and performance
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci',
    PDO::ATTR_PERSISTENT         => false
];

try {
    $con = new PDO($dsn, $user, $pass, $options);
    
    // Set session timeouts
    $con->exec("SET SESSION wait_timeout=28800");
    $con->exec("SET SESSION interactive_timeout=28800");
    
} catch (PDOException $e) {
    // Log error securely (don't expose sensitive info)
    error_log('Database connection failed: ' . $e->getMessage());
    
    // Return generic error to client
    http_response_code(500);
    die(json_encode(['error' => 'Database connection failed']));
}

/**
 * Function to check and reconnect if necessary
 * @param PDO $pdo Database connection object
 * @return bool Connection status
 */
function checkConnection(&$pdo)
{
    try {
        $pdo->query('SELECT 1');
        return true;
    } catch (PDOException $e) {
        try {
            // Attempt reconnection
            global $dsn, $user, $pass, $options;
            $pdo = new PDO($dsn, $user, $pass, $options);
            
            // Re-set session timeouts
            $pdo->exec("SET SESSION wait_timeout=28800");
            $pdo->exec("SET SESSION interactive_timeout=28800");
            
            return true;
        } catch (PDOException $reconnectException) {
            error_log('Database reconnection failed: ' . $reconnectException->getMessage());
            return false;
        }
    }
}

/**
 * Create required tables if they don't exist
 */
function createTables($pdo)
{
    try {
        // Create images table
        $createImagesTable = "
            CREATE TABLE IF NOT EXISTS images (
                id INT AUTO_INCREMENT PRIMARY KEY,
                filename VARCHAR(255) NOT NULL,
                original_name VARCHAR(255) NOT NULL,
                file_path VARCHAR(500) NOT NULL,
                file_size INT NOT NULL,
                mime_type VARCHAR(100) NOT NULL,
                upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_filename (filename)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ";
        
        // Create qr_codes table
        $createQRTable = "
            CREATE TABLE IF NOT EXISTS qr_codes (
                id INT AUTO_INCREMENT PRIMARY KEY,
                image_id INT NOT NULL,
                qr_filename VARCHAR(255) NOT NULL,
                qr_path VARCHAR(500) NOT NULL,
                view_link VARCHAR(500) NOT NULL,
                created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (image_id) REFERENCES images(id) ON DELETE CASCADE,
                INDEX idx_image_id (image_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ";
        
        $pdo->exec($createImagesTable);
        $pdo->exec($createQRTable);
        
        return true;
    } catch (PDOException $e) {
        error_log('Table creation failed: ' . $e->getMessage());
        return false;
    }
}

// Create tables on connection
createTables($con);
