<?php
/**
 * Fortnight Stock Management - Database Migration Runner
 * Run this file to apply database migrations
 */

require_once __DIR__ . '/../../includes/init.php';

// Only allow admin to run migrations
if (!hasAdminAccess()) {
    die('Access denied. Only administrators can run migrations.');
}

$migrationFile = __DIR__ . '/fortnight_migration.sql';

if (!file_exists($migrationFile)) {
    die('Migration file not found: ' . $migrationFile);
}

try {
    $db = getDB();
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Read SQL file
    $sql = file_get_contents($migrationFile);
    
    // Split by semicolons but preserve those inside strings
    $statements = array_filter(
        array_map('trim', explode(';', $sql)),
        function($stmt) {
            return !empty($stmt) && 
                   !preg_match('/^\s*--/', $stmt) && 
                   !preg_match('/^\s*SELECT/i', $stmt);
        }
    );
    
    $db->beginTransaction();
    
    $executed = 0;
    $errors = [];
    
    foreach ($statements as $statement) {
        if (empty(trim($statement))) continue;
        
        try {
            // Handle IF NOT EXISTS and ADD COLUMN IF NOT EXISTS
            if (preg_match('/ADD COLUMN IF NOT EXISTS/i', $statement)) {
                // MySQL doesn't support IF NOT EXISTS for ALTER TABLE ADD COLUMN
                // So we'll skip these and handle them manually
                continue;
            }
            
            $db->exec($statement);
            $executed++;
        } catch (PDOException $e) {
            // Ignore "column already exists" errors
            if (strpos($e->getMessage(), 'Duplicate column name') === false &&
                strpos($e->getMessage(), 'already exists') === false) {
                $errors[] = $e->getMessage();
            }
        }
    }
    
    // Now handle ADD COLUMN IF NOT EXISTS manually
    $columnsToAdd = [
        // fortnight_periods columns
        ['table' => 'fortnight_periods', 'column' => 'period_name', 'def' => 'varchar(100) DEFAULT NULL', 'after' => 'period_id'],
        ['table' => 'fortnight_periods', 'column' => 'start_date', 'def' => 'date NOT NULL', 'after' => 'period_name'],
        ['table' => 'fortnight_periods', 'column' => 'end_date', 'def' => 'date NOT NULL', 'after' => 'start_date'],
        ['table' => 'fortnight_periods', 'column' => 'month', 'def' => 'int(2) NOT NULL', 'after' => 'end_date'],
        ['table' => 'fortnight_periods', 'column' => 'year', 'def' => 'int(4) NOT NULL', 'after' => 'month'],
        ['table' => 'fortnight_periods', 'column' => 'site_id', 'def' => 'int(11) NOT NULL DEFAULT \'0\'', 'after' => 'year'],
        ['table' => 'fortnight_periods', 'column' => 'is_locked', 'def' => 'tinyint(1) DEFAULT \'0\'', 'after' => 'site_id'],
        ['table' => 'fortnight_periods', 'column' => 'created_by', 'def' => 'int(11) DEFAULT \'0\'', 'after' => 'is_locked'],
        ['table' => 'fortnight_periods', 'column' => 'created_at', 'def' => 'timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP', 'after' => 'created_by'],
        ['table' => 'fortnight_periods', 'column' => 'updated_at', 'def' => 'timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP', 'after' => 'created_at'],
        
        // fortnight_inventory columns
        ['table' => 'fortnight_inventory', 'column' => 'sr_no', 'def' => 'int(11) DEFAULT NULL', 'after' => 'period_id'],
        ['table' => 'fortnight_inventory', 'column' => 'material_hsn_code', 'def' => 'varchar(50) DEFAULT NULL', 'after' => 'sr_no'],
        ['table' => 'fortnight_inventory', 'column' => 'material_description', 'def' => 'varchar(255) NOT NULL DEFAULT \'\'', 'after' => 'material_hsn_code'],
        ['table' => 'fortnight_inventory', 'column' => 'category', 'def' => 'varchar(100) DEFAULT NULL', 'after' => 'material_description'],
        ['table' => 'fortnight_inventory', 'column' => 'net_rate', 'def' => 'decimal(10,2) DEFAULT \'0.00\'', 'after' => 'category'],
        ['table' => 'fortnight_inventory', 'column' => 'opening_stock_qty', 'def' => 'decimal(10,3) DEFAULT \'0.000\'', 'after' => 'net_rate'],
        ['table' => 'fortnight_inventory', 'column' => 'opening_stock_value', 'def' => 'decimal(15,2) DEFAULT \'0.00\'', 'after' => 'opening_stock_qty'],
        ['table' => 'fortnight_inventory', 'column' => 'purchase_qty', 'def' => 'decimal(10,3) DEFAULT \'0.000\'', 'after' => 'opening_stock_value'],
        ['table' => 'fortnight_inventory', 'column' => 'purchase_value', 'def' => 'decimal(15,2) DEFAULT \'0.00\'', 'after' => 'purchase_qty'],
        ['table' => 'fortnight_inventory', 'column' => 'consumption_qty', 'def' => 'decimal(10,3) DEFAULT \'0.000\'', 'after' => 'purchase_value'],
        ['table' => 'fortnight_inventory', 'column' => 'consumption_value', 'def' => 'decimal(15,2) DEFAULT \'0.00\'', 'after' => 'consumption_qty'],
        ['table' => 'fortnight_inventory', 'column' => 'closing_stock_qty', 'def' => 'decimal(10,3) DEFAULT \'0.000\'', 'after' => 'consumption_value'],
        ['table' => 'fortnight_inventory', 'column' => 'closing_stock_value', 'def' => 'decimal(15,2) DEFAULT \'0.00\'', 'after' => 'closing_stock_qty'],
        ['table' => 'fortnight_inventory', 'column' => 'supplier_name', 'def' => 'varchar(255) DEFAULT NULL', 'after' => 'closing_stock_value'],
        ['table' => 'fortnight_inventory', 'column' => 'bill_challan_no', 'def' => 'varchar(100) DEFAULT NULL', 'after' => 'supplier_name'],
        ['table' => 'fortnight_inventory', 'column' => 'site_id', 'def' => 'int(11) NOT NULL DEFAULT \'0\'', 'after' => 'bill_challan_no'],
        ['table' => 'fortnight_inventory', 'column' => 'created_by', 'def' => 'int(11) DEFAULT \'0\'', 'after' => 'site_id'],
        ['table' => 'fortnight_inventory', 'column' => 'updated_by', 'def' => 'int(11) DEFAULT \'0\'', 'after' => 'created_by'],
        ['table' => 'fortnight_inventory', 'column' => 'created_at', 'def' => 'timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP', 'after' => 'updated_by'],
        ['table' => 'fortnight_inventory', 'column' => 'updated_at', 'def' => 'timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP', 'after' => 'created_at'],
    ];
    
    foreach ($columnsToAdd as $col) {
        // Check if column exists
        $checkStmt = $db->prepare("
            SELECT COUNT(*) as cnt 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = ? 
            AND COLUMN_NAME = ?
        ");
        $checkStmt->execute([$col['table'], $col['column']]);
        $exists = $checkStmt->fetch()['cnt'] > 0;
        
        if (!$exists) {
            try {
                $afterClause = $col['after'] ? " AFTER `{$col['after']}`" : '';
                $stmt = "ALTER TABLE `{$col['table']}` ADD COLUMN `{$col['column']}` {$col['def']}{$afterClause}";
                $db->exec($stmt);
                $executed++;
            } catch (PDOException $e) {
                if (strpos($e->getMessage(), 'Duplicate column') === false) {
                    $errors[] = "Error adding {$col['table']}.{$col['column']}: " . $e->getMessage();
                }
            }
        }
    }
    
    // Handle column rename (from_date/to_date to start_date/end_date)
    $checkFromDate = $db->prepare("
        SELECT COUNT(*) as cnt 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'fortnight_periods' 
        AND COLUMN_NAME = 'from_date'
    ");
    $checkFromDate->execute();
    $hasFromDate = $checkFromDate->fetch()['cnt'] > 0;
    
    $checkStartDate = $db->prepare("
        SELECT COUNT(*) as cnt 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'fortnight_periods' 
        AND COLUMN_NAME = 'start_date'
    ");
    $checkStartDate->execute();
    $hasStartDate = $checkStartDate->fetch()['cnt'] > 0;
    
    if ($hasFromDate && !$hasStartDate) {
        try {
            $db->exec("ALTER TABLE `fortnight_periods` CHANGE COLUMN `from_date` `start_date` date NOT NULL");
            $db->exec("ALTER TABLE `fortnight_periods` CHANGE COLUMN `to_date` `end_date` date NOT NULL");
            $executed += 2;
        } catch (PDOException $e) {
            $errors[] = "Column rename error: " . $e->getMessage();
        }
    }
    
    // Remove old item_id column from fortnight_inventory if it exists and material_description exists
    $checkItemId = $db->prepare("
        SELECT COUNT(*) as cnt 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'fortnight_inventory' 
        AND COLUMN_NAME = 'item_id'
    ");
    $checkItemId->execute();
    $hasItemId = $checkItemId->fetch()['cnt'] > 0;
    
    $checkMaterialDesc = $db->prepare("
        SELECT COUNT(*) as cnt 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'fortnight_inventory' 
        AND COLUMN_NAME = 'material_description'
    ");
    $checkMaterialDesc->execute();
    $hasMaterialDesc = $checkMaterialDesc->fetch()['cnt'] > 0;
    
    if ($hasItemId && $hasMaterialDesc) {
        try {
            // Drop foreign key first if exists
            $db->exec("ALTER TABLE `fortnight_inventory` DROP FOREIGN KEY IF EXISTS `fk_fi_item`");
            $db->exec("ALTER TABLE `fortnight_inventory` DROP COLUMN `item_id`");
            $executed++;
        } catch (PDOException $e) {
            if (strpos($e->getMessage(), "doesn't exist") === false) {
                $errors[] = "Error removing item_id: " . $e->getMessage();
            }
        }
    }
    
    $db->commit();
    
    echo "<!DOCTYPE html><html><head><title>Fortnight Migration</title>";
    echo "<style>body{font-family:Arial;padding:20px;} .success{color:green;} .error{color:red;}</style>";
    echo "</head><body>";
    echo "<h2>Fortnight Database Migration</h2>";
    echo "<p class='success'>Executed $executed statements successfully.</p>";
    
    if (!empty($errors)) {
        echo "<h3 class='error'>Warnings (non-critical):</h3>";
        echo "<ul>";
        foreach ($errors as $error) {
            echo "<li>" . htmlspecialchars($error) . "</li>";
        }
        echo "</ul>";
    } else {
        echo "<p class='success'><strong>✓ No errors! All tables and columns are ready.</strong></p>";
    }
    
    echo "<hr>";
    echo "<h3>Next Steps:</h3>";
    echo "<ol>";
    echo "<li><a href='" . BASE_URL . "/modules/fortnight/list.php'>Go to Fortnight Management</a></li>";
    echo "<li>Add items to <strong>item_master</strong> table for dropdowns</li>";
    echo "<li>Add suppliers to <strong>supplier_master</strong> table for dropdowns</li>";
    echo "</ol>";
    echo "</body></html>";
    
} catch (Exception $e) {
    if ($db->inTransaction()) {
        $db->rollBack();
    }
    echo "<h2 style='color: red;'>Migration Failed</h2>";
    echo "<p>Error: " . htmlspecialchars($e->getMessage()) . "</p>";
    echo "<pre>" . htmlspecialchars($e->getTraceAsString()) . "</pre>";
}
