<?php
require_once __DIR__ . '/../config/database.php';

try {
    // Add new columns to the retention_settings table one by one if they don't exist
    
    // Add retention_type column if it doesn't exist
    $stmt = $pdo->query("SHOW COLUMNS FROM retention_settings LIKE 'retention_type'");
    $column_exists = $stmt->rowCount() > 0;
    if (!$column_exists) {
        $pdo->exec("ALTER TABLE retention_settings ADD COLUMN retention_type ENUM('count', 'duration') DEFAULT 'count'");
    }
    
    // Add retention_duration_days column if it doesn't exist
    $stmt = $pdo->query("SHOW COLUMNS FROM retention_settings LIKE 'retention_duration_days'");
    $column_exists = $stmt->rowCount() > 0;
    if (!$column_exists) {
        $pdo->exec("ALTER TABLE retention_settings ADD COLUMN retention_duration_days INT DEFAULT 30");
    }
    
    echo "Table 'retention_settings' updated successfully with new columns!\n";
    
    // Update existing records with default values for new columns
    $updateSql = "UPDATE retention_settings 
                  SET retention_type = 'count',
                      retention_duration_days = 30
                  WHERE retention_type IS NULL 
                     OR retention_duration_days IS NULL";
    
    $stmt = $pdo->prepare($updateSql);
    $count = $stmt->execute();
    
    echo "Updated existing records with default values for new columns.\n";
    
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>