<?php
/**
 * Special Access Links Migration
 * Creates the necessary database tables for the special access links feature
 * 
 * This migration will:
 * 1. Create the special_access_links table
 * 2. Add referral_code column to registrations table
 * 3. Create necessary indexes
 * 4. Handle rollback if needed
 */

// Find db_config.php from current directory
$dbConfigPath = __DIR__ . '/../../db_config.php';
if (!file_exists($dbConfigPath)) {
    // Try alternative path
    $dbConfigPath = __DIR__ . '/../db_config.php';
}
if (!file_exists($dbConfigPath)) {
    // Try from current working directory
    $dbConfigPath = 'db_config.php';
}
require_once $dbConfigPath;

class SpecialAccessLinksMigration {
    private $pdo;
    private $migrationName = 'special_access_links_migration';
    private $version = '2025_01_15_001';
    
    public function __construct() {
        $this->pdo = getDBConnection();
    }
    
    /**
     * Run the migration
     */
    public function up() {
        try {
            // Check if migration already ran
            if ($this->isMigrationApplied()) {
                echo "Migration {$this->migrationName} already applied.\n";
                return true;
            }
            
            echo "Starting migration: {$this->migrationName}\n";
            
            // Create special_access_links table
            $this->createSpecialAccessLinksTable();
            
            // Add referral_code to registrations table
            $this->addReferralCodeToRegistrations();
            
            // Create indexes
            $this->createIndexes();
            
            // Record migration (this needs to be in a transaction)
            if (!$this->isMigrationApplied()) {
                $this->pdo->beginTransaction();
                try {
                    $this->recordMigration();
                    $this->pdo->commit();
                } catch (Exception $e) {
                    if ($this->pdo->inTransaction()) {
                        $this->pdo->rollBack();
                    }
                    throw $e;
                }
            }
            
            echo "Migration {$this->migrationName} completed successfully!\n";
            return true;
            
        } catch (Exception $e) {
            echo "Migration failed: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    /**
     * Rollback the migration
     */
    public function down() {
        try {
            if (!$this->isMigrationApplied()) {
                echo "Migration {$this->migrationName} not found.\n";
                return true;
            }
            
            echo "Rolling back migration: {$this->migrationName}\n";
            
            // Drop special_access_links table
            $this->pdo->exec("DROP TABLE IF EXISTS special_access_links");
            
            // Remove referral_code column from registrations
            $this->removeReferralCodeFromRegistrations();
            
            // Remove migration record (this needs to be in a transaction)
            $this->pdo->beginTransaction();
            try {
                $this->removeMigrationRecord();
                $this->pdo->commit();
            } catch (Exception $e) {
                if ($this->pdo->inTransaction()) {
                    $this->pdo->rollBack();
                }
                throw $e;
            }
            
            echo "Migration {$this->migrationName} rolled back successfully!\n";
            return true;
            
        } catch (Exception $e) {
            echo "Rollback failed: " . $e->getMessage() . "\n";
            return false;
        }
    }
    
    /**
     * Create special_access_links table
     */
    private function createSpecialAccessLinksTable() {
        $sql = "
        CREATE TABLE IF NOT EXISTS special_access_links (
            id INT AUTO_INCREMENT PRIMARY KEY,
            link_name VARCHAR(255) NOT NULL,
            link_code VARCHAR(100) NOT NULL UNIQUE,
            description TEXT,
            custom_message TEXT,
            product_ids JSON NOT NULL,
            include_visible_products BOOLEAN DEFAULT TRUE,
            is_active BOOLEAN DEFAULT TRUE,
            expires_at DATETIME NULL,
            access_count INT DEFAULT 0,
            last_accessed_at DATETIME NULL,
            conversion_count INT DEFAULT 0,
            created_by INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            
            INDEX idx_link_code (link_code),
            INDEX idx_is_active (is_active),
            INDEX idx_expires_at (expires_at),
            INDEX idx_created_by (created_by),
            INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ";
        
        $this->pdo->exec($sql);
        echo "✓ Created special_access_links table\n";
    }
    
    /**
     * Add referral_code column to registrations table
     */
    private function addReferralCodeToRegistrations() {
        try {
            // Check if column already exists
            $stmt = $this->pdo->query("SHOW COLUMNS FROM registrations LIKE 'referral_code'");
            if ($stmt->rowCount() > 0) {
                echo "✓ referral_code column already exists in registrations table\n";
                return;
            }
            
            $sql = "ALTER TABLE registrations ADD COLUMN referral_code VARCHAR(100) NULL AFTER payment_status";
            $this->pdo->exec($sql);
            
            // Add index for referral_code
            $this->pdo->exec("ALTER TABLE registrations ADD INDEX idx_referral_code (referral_code)");
            
            echo "✓ Added referral_code column to registrations table\n";
        } catch (Exception $e) {
            // If column already exists, that's okay
            if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
                echo "✓ referral_code column already exists in registrations table\n";
                return;
            }
            throw $e;
        }
    }
    
    /**
     * Remove referral_code column from registrations table
     */
    private function removeReferralCodeFromRegistrations() {
        // Check if column exists
        $stmt = $this->pdo->query("SHOW COLUMNS FROM registrations LIKE 'referral_code'");
        if ($stmt->rowCount() == 0) {
            echo "✓ referral_code column not found in registrations table\n";
            return;
        }
        
        // Drop index first
        $this->pdo->exec("ALTER TABLE registrations DROP INDEX IF EXISTS idx_referral_code");
        
        // Drop column
        $this->pdo->exec("ALTER TABLE registrations DROP COLUMN referral_code");
        
        echo "✓ Removed referral_code column from registrations table\n";
    }
    
    /**
     * Create additional indexes
     */
    private function createIndexes() {
        // Additional indexes for better performance
        $indexes = [
            "CREATE INDEX IF NOT EXISTS idx_sal_active_expires ON special_access_links (is_active, expires_at)",
            "CREATE INDEX IF NOT EXISTS idx_sal_created_at_desc ON special_access_links (created_at DESC)",
        ];
        
        $created = 0;
        $skipped = 0;
        
        foreach ($indexes as $index) {
            try {
                $this->pdo->exec($index);
                $created++;
            } catch (Exception $e) {
                // Index might already exist, continue
                $skipped++;
            }
        }
        
        if ($created > 0) {
            echo "✓ Created {$created} additional indexes\n";
        }
        if ($skipped > 0) {
            echo "Note: {$skipped} indexes skipped (may already exist)\n";
        }
    }
    
    /**
     * Check if migration was already applied
     */
    private function isMigrationApplied() {
        $this->createMigrationsTable();
        
        $stmt = $this->pdo->prepare("
            SELECT COUNT(*) FROM migrations 
            WHERE migration_name = ? AND version = ?
        ");
        $stmt->execute([$this->migrationName, $this->version]);
        
        return $stmt->fetchColumn() > 0;
    }
    
    /**
     * Record migration as applied
     */
    private function recordMigration() {
        $this->createMigrationsTable();
        
        // Check if already recorded
        if ($this->isMigrationApplied()) {
            echo "Migration already recorded in database.\n";
            return;
        }
        
        $stmt = $this->pdo->prepare("
            INSERT INTO migrations (migration_name, version, applied_at) 
            VALUES (?, ?, NOW())
        ");
        $stmt->execute([$this->migrationName, $this->version]);
        echo "✓ Migration recorded in database\n";
    }
    
    /**
     * Remove migration record
     */
    private function removeMigrationRecord() {
        $stmt = $this->pdo->prepare("
            DELETE FROM migrations 
            WHERE migration_name = ? AND version = ?
        ");
        $stmt->execute([$this->migrationName, $this->version]);
    }
    
    /**
     * Create migrations tracking table
     */
    private function createMigrationsTable() {
        $sql = "
        CREATE TABLE IF NOT EXISTS migrations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            migration_name VARCHAR(255) NOT NULL,
            version VARCHAR(50) NOT NULL,
            applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_migration (migration_name, version)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ";
        
        $this->pdo->exec($sql);
    }
    
    /**
     * Check if tables exist
     */
    public function checkTablesExist() {
        $tables = ['special_access_links'];
        $existing = [];
        $missing = [];
        
        foreach ($tables as $table) {
            $stmt = $this->pdo->query("SHOW TABLES LIKE '$table'");
            if ($stmt->rowCount() > 0) {
                $existing[] = $table;
            } else {
                $missing[] = $table;
            }
        }
        
        return [
            'existing' => $existing,
            'missing' => $missing,
            'all_exist' => empty($missing)
        ];
    }
    
    /**
     * Get migration status
     */
    public function getStatus() {
        $status = [
            'migration_name' => $this->migrationName,
            'version' => $this->version,
            'applied' => $this->isMigrationApplied(),
            'tables' => $this->checkTablesExist()
        ];
        
        return $status;
    }
}

// CLI execution - only run if this file is executed directly
if (php_sapi_name() === 'cli' && basename($_SERVER['PHP_SELF']) === 'special_access_links_migration.php') {
    $migration = new SpecialAccessLinksMigration();
    
    $command = $argv[1] ?? 'up';
    
    switch ($command) {
        case 'up':
            $migration->up();
            break;
        case 'down':
            $migration->down();
            break;
        case 'status':
            $status = $migration->getStatus();
            echo "Migration Status:\n";
            echo "Name: {$status['migration_name']}\n";
            echo "Version: {$status['version']}\n";
            echo "Applied: " . ($status['applied'] ? 'Yes' : 'No') . "\n";
            echo "Tables: " . ($status['tables']['all_exist'] ? 'All exist' : 'Missing: ' . implode(', ', $status['tables']['missing'])) . "\n";
            break;
        default:
            echo "Usage: php special_access_links_migration.php [up|down|status]\n";
    }
}
?>
