<?php
/**
 * Migration Script: Convert SQLite single-tenant to MySQL multi-tenant
 * Run this script to migrate existing data to the new SaaS structure
 */

require_once __DIR__ . '/../src/db.php';

class MultiTenantMigration {
    private $pdo;
    private $defaultCompanyId = 1;
    
    public function __construct() {
        $this->pdo = getPDO();
    }
    
    public function migrate() {
        echo "Starting multi-tenant migration...\n";
        
        try {
            // 1. Create SaaS tables
            $this->createSaaSTables();
            
            // 2. Migrate existing data with company_id
            $this->migrateExistingTables();
            
            // 3. Create default admin user
            $this->createDefaultAdmin();
            
            echo "Migration completed successfully!\n";
            
        } catch (Exception $e) {
            echo "Migration failed: " . $e->getMessage() . "\n";
            throw $e;
        }
    }
    
    private function createSaaSTables() {
        echo "Creating SaaS tables...\n";
        
        $sql = file_get_contents(__DIR__ . '/saas_schema.sql');
        $statements = array_filter(array_map('trim', explode(';', $sql)));
        
        foreach ($statements as $statement) {
            if (!empty($statement)) {
                $this->pdo->exec($statement);
            }
        }
        
        echo "SaaS tables created.\n";
    }
    
    private function migrateExistingTables() {
        echo "Migrating existing tables...\n";
        
        // Add company_id to existing tables
        $tables = [
            'clients' => 'clients',
            'proposals' => 'proposals', 
            'invoices' => 'invoices',
            'contracts' => 'contracts',
            'invoice_items' => 'invoice_items',
            'invoice_items_line' => 'invoice_items_line',
            'invoice_payments' => 'invoice_payments',
            'tax_brackets' => 'tax_brackets',
            'invoice_settings' => 'invoice_settings',
            'templates' => 'templates',
            'modules' => 'modules',
            'template_modules' => 'template_modules',
            'contract_modules' => 'contract_modules'
        ];
        
        foreach ($tables as $table) {
            if ($this->tableExists($table)) {
                $this->addCompanyIdToTable($table);
                $this->updateTableWithCompanyId($table);
            }
        }
        
        echo "Existing tables migrated.\n";
    }
    
    private function tableExists($table) {
        $stmt = $this->pdo->prepare("SHOW TABLES LIKE ?");
        $stmt->execute([$table]);
        return $stmt->rowCount() > 0;
    }
    
    private function addCompanyIdToTable($table) {
        try {
            $this->pdo->exec("ALTER TABLE {$table} ADD COLUMN company_id INT NOT NULL DEFAULT {$this->defaultCompanyId}");
            echo "Added company_id to {$table}\n";
        } catch (Exception $e) {
            echo "company_id already exists in {$table} or error: " . $e->getMessage() . "\n";
        }
    }
    
    private function updateTableWithCompanyId($table) {
        $this->pdo->exec("UPDATE {$table} SET company_id = {$this->defaultCompanyId} WHERE company_id IS NULL OR company_id = 0");
        echo "Updated {$table} with default company_id\n";
    }
    
    private function createDefaultAdmin() {
        echo "Creating default admin user...\n";
        
        // Check if admin already exists
        $stmt = $this->pdo->prepare("SELECT id FROM users WHERE email = 'admin@keelance.com'");
        $stmt->execute();
        
        if ($stmt->rowCount() === 0) {
            $passwordHash = password_hash('admin123', PASSWORD_DEFAULT);
            
            $stmt = $this->pdo->prepare("
                INSERT INTO users (company_id, name, email, password_hash, role, email_verified_at) 
                VALUES (?, ?, ?, ?, ?, NOW())
            ");
            
            $stmt->execute([
                $this->defaultCompanyId,
                'Admin User',
                'admin@keelance.com', 
                $passwordHash,
                'super_admin'
            ]);
            
            echo "Default admin user created (admin@keelance.com / admin123)\n";
        } else {
            echo "Admin user already exists.\n";
        }
    }
}

// Run migration
if (php_sapi_name() === 'cli') {
    $migration = new MultiTenantMigration();
    $migration->migrate();
} else {
    echo "This script must be run from the command line.\n";
    echo "Usage: php migrate_to_multitenant.php\n";
}
