<?php

/**
 * Database Connection - MySQL Only for Keelance SaaS Platform
 * Production-ready MySQL database with PDO
 */

function getPDO(): PDO
{
    static $pdo = null;

    if ($pdo instanceof PDO) {
        return $pdo;
    }

    // MySQL configuration only - no SQLite fallback
    $host = $_ENV['DB_HOST'] ?? 'localhost';
    $port = $_ENV['DB_PORT'] ?? '3306';
    $database = $_ENV['DB_DATABASE'] ?? 'keelance';
    $username = $_ENV['DB_USERNAME'] ?? 'root';
    $password = $_ENV['DB_PASSWORD'] ?? '';

    $dsn = "mysql:host={$host};port={$port};dbname={$database};charset=utf8mb4";

    $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"
    ];

    try {
        $pdo = new PDO($dsn, $username, $password, $options);
        
        // Enable foreign key constraints
        $pdo->exec('SET foreign_key_checks = 1');
        
        return $pdo;
        
    } catch (PDOException $e) {
        // MySQL failed - show detailed error
        if (defined('ENVIRONMENT') && ENVIRONMENT === 'development') {
            throw new PDOException('MySQL connection failed: ' . $e->getMessage() . "\n\n💡 SOLUTIONS:\n" .
                "1. Make sure MySQL server is running\n" .
                "2. Check database credentials in .env file\n" .
                "3. Create database: CREATE DATABASE keelance;\n" .
                "4. Run: php final_mysql_setup.php\n" .
                "5. Ensure MySQL user has proper permissions");
        }
        
        // In production, log and show generic error
        error_log('MySQL connection failed: ' . $e->getMessage());
        throw new PDOException('Database connection failed - please check MySQL configuration');
    }
}

/**
 * Get database connection with tenant scoping
 */
function getTenantPDO(): PDO
{
    $pdo = getPDO();
    
    // Set tenant context if available
    if (class_exists('TenantManager')) {
        $companyId = TenantManager::getCurrentCompanyId();
        if ($companyId) {
            // Set session variable for row-level security (if implemented)
            $pdo->exec("SET @company_id = {$companyId}");
        }
    }
    
    return $pdo;
}

/**
 * Execute a query with automatic tenant scoping
 */
function executeTenantQuery(string $query, array $params = []): PDOStatement
{
    $pdo = getTenantPDO();
    
    // Automatically add company_id filtering if tenant context exists
    if (class_exists('TenantManager')) {
        $companyId = TenantManager::getCurrentCompanyId();
        if ($companyId && strpos($query, 'company_id') === false) {
            // Add company_id condition to WHERE clause
            if (stripos($query, 'WHERE') !== false) {
                $query = preg_replace('/WHERE\s+/i', 'WHERE (company_id = ? AND ', $query, 1);
                $query .= ')';
            } else {
                $query .= ' WHERE company_id = ?';
            }
            array_unshift($params, $companyId);
        }
    }
    
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    
    return $stmt;
}

/**
 * Begin database transaction
 */
function beginTransaction(): void
{
    getPDO()->beginTransaction();
}

/**
 * Commit database transaction
 */
function commitTransaction(): void
{
    getPDO()->commit();
}

/**
 * Rollback database transaction
 */
function rollbackTransaction(): void
{
    getPDO()->rollback();
}

/**
 * Get last insert ID
 */
function lastInsertId(): string
{
    return getPDO()->lastInsertId();
}

/**
 * Check if table exists
 */
function tableExists(string $tableName): bool
{
    $pdo = getPDO();
    $stmt = $pdo->prepare("SHOW TABLES LIKE ?");
    $stmt->execute([$tableName]);
    return $stmt->rowCount() > 0;
}

/**
 * Get table columns
 */
function getTableColumns(string $tableName): array
{
    $pdo = getPDO();
    $stmt = $pdo->prepare("SHOW COLUMNS FROM {$tableName}");
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

/**
 * Run database migration
 */
function runMigration(string $migrationFile): bool
{
    if (!file_exists($migrationFile)) {
        throw new Exception("Migration file not found: {$migrationFile}");
    }
    
    $sql = file_get_contents($migrationFile);
    $statements = array_filter(array_map('trim', explode(';', $sql)));
    
    $pdo = getPDO();
    
    try {
        $pdo->beginTransaction();
        
        foreach ($statements as $statement) {
            if (!empty($statement)) {
                $pdo->exec($statement);
            }
        }
        
        $pdo->commit();
        return true;
        
    } catch (Exception $e) {
        $pdo->rollback();
        error_log("Migration failed: " . $e->getMessage());
        throw $e;
    }
}

/**
 * Create database if it doesn't exist
 */
function createDatabaseIfNotExists(): void
{
    $host = $_ENV['DB_HOST'] ?? 'localhost';
    $port = $_ENV['DB_PORT'] ?? '3306';
    $username = $_ENV['DB_USERNAME'] ?? 'root';
    $password = $_ENV['DB_PASSWORD'] ?? '';
    $database = $_ENV['DB_DATABASE'] ?? 'keelance';
    
    $dsn = "mysql:host={$host};port={$port};charset=utf8mb4";
    
    try {
        $pdo = new PDO($dsn, $username, $password, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        ]);
        
        $stmt = $pdo->prepare("CREATE DATABASE IF NOT EXISTS `{$database}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        $stmt->execute();
        
    } catch (PDOException $e) {
        throw new Exception("Failed to create database: " . $e->getMessage());
    }
}
