<?php

/**
 * Database Migration Script
 * This file contains all database table creation scripts
 * Each table is checked for existence before creation
 */

require_once __DIR__ . '/../vendor/autoload.php';

try {
    // Create database directory and file if they don't exist
    $dbPath = \Config::PATH['root'] . '/database/database.db';
    $dbDir = dirname($dbPath);
    
    if (!is_dir($dbDir)) {
        mkdir($dbDir, 0755, true);
        echo "✓ Created database directory\n";
    }
    
    if (!file_exists($dbPath)) {
        touch($dbPath);
        echo "✓ Created database file\n";
    }
    
    $db = db();
    
    echo "Starting database migration...\n";
    
    // Migration: Create settings table
    $settingsTableSQL = "
    CREATE TABLE IF NOT EXISTS settings (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        grupa VARCHAR(255) NOT NULL,
        name VARCHAR(255) NOT NULL,
        value TEXT
    )";
    
    $db->execute($settingsTableSQL);
    echo " Settings table checked/created\n";
    
    // Create index for settings table
    $settingsIndexSQL = "CREATE INDEX IF NOT EXISTS idx_settings_grupa_name ON settings(grupa, name)";
    $db->execute($settingsIndexSQL);
    echo " Settings table index checked/created\n";
    
    // Migration: Create pages table
    $pagesTableSQL = "
    CREATE TABLE IF NOT EXISTS pages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name VARCHAR(255) NOT NULL,
        slug VARCHAR(255) UNIQUE NOT NULL,
        title VARCHAR(255),
        content TEXT,
        extra_fields JSON,
        layout VARCHAR(255) DEFAULT 'default',
        is_homepage BOOLEAN DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )";
    
    $db->execute($pagesTableSQL);
    echo " Pages table checked/created\n";
    
    // Create index for pages table
    $pagesIndexSQL = "CREATE INDEX IF NOT EXISTS idx_pages_slug ON pages(slug)";
    $db->execute($pagesIndexSQL);
    echo " Pages table index checked/created\n";
    
    
    // Insert default settings if they don't exist
    $defaultSettings = [
        // Content settings
        ['grupa' => 'content', 'name' => 'title', 'value' => 'Welcome to Amazing Casino - Your Ultimate Gaming Destination'],
        ['grupa' => 'content', 'name' => 'top_text', 'value' => '<h2>Best Online Casino Experience</h2><p>Discover the most exciting online casino games with amazing bonuses and instant payouts!</p>'],
        ['grupa' => 'content', 'name' => 'bottom_text', 'value' => '<p>Join thousands of players who trust our platform for secure gaming. Play responsibly and enjoy the thrill of winning big!</p>'],
        ['grupa' => 'content', 'name' => 'author-name', 'value' => 'John Smith'],
        ['grupa' => 'content', 'name' => 'modified-date', 'value' => '2024-01-15'],
        ['grupa' => 'content', 'name' => 'login', 'value' => 'Login Now'],
        ['grupa' => 'content', 'name' => 'password', 'value' => 'Reset Password'],
        ['grupa' => 'content', 'name' => 'faq-title', 'value' => 'Frequently Asked Questions'],
        ['grupa' => 'content', 'name' => 'footer__text', 'value' => 'Your trusted casino partner since 2020. Licensed and regulated for your safety.'],
        ['grupa' => 'content', 'name' => 'copyright', 'value' => '© 2024 Amazing Casino. All rights reserved.'],
        ['grupa' => 'content', 'name' => 'contact-menu-name', 'value' => 'Contacts'],
        ['grupa' => 'content', 'name' => 'review-count', 'value' => '1250'],
        ['grupa' => 'content', 'name' => 'listing', 'value' => 'Top Rated Casinos'],
        ['grupa' => 'content', 'name' => 'content_btn', 'value' => 'Play Now'],
        ['grupa' => 'content', 'name' => 'hide-btns', 'value' => '0'],
        ['grupa' => 'content', 'name' => 'header-logo', 'value' => '{"src":"media/logo.png","title":"Amazing Casino Logo","alt":"Amazing Casino - Best Online Gaming"}'],
        ['grupa' => 'content', 'name' => 'banner-logo', 'value' => '{"src":"media/banner.jpg","title":"Welcome Bonus Banner","alt":"Get 200% Welcome Bonus","width":"800","height":"400"}'],
        ['grupa' => 'content', 'name' => 'footer-logo', 'value' => '{"src":"media/footer-logo.png","title":"Footer Logo","alt":"Amazing Casino Footer"}'],
        ['grupa' => 'content', 'name' => 'favicon', 'value' => 'media/favicon.ico'],
        ['grupa' => 'content', 'name' => 'author-img', 'value' => 'media/author.jpg'],
        ['grupa' => 'content', 'name' => 'hash', 'value' => 'ac123main'],
        ['grupa' => 'content', 'name' => 'hash-1', 'value' => 'ac123promo'],
        ['grupa' => 'content', 'name' => 'hash-2', 'value' => 'ac123bonus'],
        ['grupa' => 'content', 'name' => 'hash-3', 'value' => 'ac123games'],
        ['grupa' => 'content', 'name' => 'hash-4', 'value' => 'ac123vip'],
        ['grupa' => 'content', 'name' => 'hash-5', 'value' => 'ac123support'],
        ['grupa' => 'content', 'name' => 'casino_list', 'value' => '[{"heading":"Royal Casino","text":"200% Welcome Bonus + 50 Free Spins","button":"Claim Bonus","rating":"10"},{"heading":"Golden Palace","text":"100% Match Bonus up to $500","button":"Play Now","rating":"9.5"},{"heading":"Diamond Club","text":"$1000 Welcome Package + VIP Treatment","button":"Join VIP","rating":"9"}]'],
        ['grupa' => 'content', 'name' => 'faq', 'value' => '[{"question":"How do I create an account?","answer":"Click the Register button and fill in your details. Verification usually takes 5-10 minutes."},{"question":"What payment methods do you accept?","answer":"We accept credit cards, e-wallets, bank transfers, and cryptocurrencies."},{"question":"How long do withdrawals take?","answer":"Most withdrawals are processed within 24 hours."}]'],
        ['grupa' => 'content', 'name' => 'menu', 'value' => '[{"title":"Home","anchor":"#home"},{"title":"Games","anchor":"#games"},{"title":"Bonuses","anchor":"#bonuses"},{"title":"Reviews","anchor":"#reviews"},{"title":"Contact","anchor":"#contact"}]'],
        
        // Style settings
        ['grupa' => 'styles', 'name' => 'header_color', 'value' => '#ED0039'],
        ['grupa' => 'styles', 'name' => 'body_bg_color', 'value' => '#FFF'],
        ['grupa' => 'styles', 'name' => 'content_bg_color', 'value' => '#F6F6F6'],
        ['grupa' => 'styles', 'name' => 'text_color', 'value' => '#000'],
        ['grupa' => 'styles', 'name' => 'link_color', 'value' => '#ED0039'],
        ['grupa' => 'styles', 'name' => 'accent_color', 'value' => '#ED0039'],
        ['grupa' => 'styles', 'name' => 'content_border_color', 'value' => '#E4E4E4'],
        ['grupa' => 'styles', 'name' => 'table_bg_color', 'value' => '#fff'],
        ['grupa' => 'styles', 'name' => 'table_header_color', 'value' => '#FFF'],
        ['grupa' => 'styles', 'name' => 'footer_bg_color', 'value' => '#000'],

        // SEO settings
        ['grupa' => 'seo', 'name' => 'hreflang', 'value' => '[{"hreflang":"en","href":"https://amazing-casino.com/en/"},{"hreflang":"es","href":"https://amazing-casino.com/es/"},{"hreflang":"fr","href":"https://amazing-casino.com/fr/"},{"hreflang":"x-default","href":"https://amazing-casino.com/"}]'],
    ];
    
    foreach ($defaultSettings as $setting) {
        $exists = $db->fetchOne(
            "SELECT id FROM settings WHERE grupa = ? AND name = ?",
            [$setting['grupa'], $setting['name']]
        );
        
        if (!$exists) {
            $db->execute(
                "INSERT INTO settings (grupa, name, value) VALUES (?, ?, ?)",
                [$setting['grupa'], $setting['name'], $setting['value']]
            );
            echo "✓ Default setting {$setting['grupa']}.{$setting['name']} created\n";
        }
    }
    
    // Migration: Create seo_metas table
    $seoMetasTableSQL = "
    CREATE TABLE IF NOT EXISTS seo_metas (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        seoable_type VARCHAR(255) NOT NULL,
        seoable_id INTEGER NOT NULL,
        title VARCHAR(255) NULL,
        description TEXT NULL,
        keywords TEXT NULL,
        canonical VARCHAR(255) NULL,
        image VARCHAR(255) NULL,
        extra_fields JSON NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )";
    
    $db->execute($seoMetasTableSQL);
    echo "✓ SEO Metas table checked/created\n";
    
    // Create index for seo_metas table
    $seoMetasIndexSQL = "CREATE INDEX IF NOT EXISTS idx_seo_metas_seoable ON seo_metas(seoable_type, seoable_id)";
    $db->execute($seoMetasIndexSQL);
    echo "✓ SEO Metas table index checked/created\n";
    
    // Migration: Create faqs table
    $faqsTableSQL = "
        CREATE TABLE IF NOT EXISTS faqs (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        faqable_type VARCHAR(255) NOT NULL,
        faqable_id INTEGER NOT NULL,
        title VARCHAR(255) NULL,
        extra_fields JSON NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )";
    $db->execute($faqsTableSQL);
    echo "✓ FAQs table checked/created\n";
    
    // Create indexes for faqs
    $faqsIndexSQL = "CREATE INDEX IF NOT EXISTS idx_faqs_faqable ON faqs(faqable_type, faqable_id)";
    $db->execute($faqsIndexSQL);
    echo "✓ FAQs table index checked/created\n";
    
    // Migration: Create slots table
    $slotsTableSQL = "
        CREATE TABLE IF NOT EXISTS slots (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name VARCHAR(255) NOT NULL,
        image VARCHAR(255) NULL,
        provider VARCHAR(255) NULL,
        rtp DECIMAL(5,2) NULL,
        demo_url VARCHAR(500) NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )";
    $db->execute($slotsTableSQL);
    echo "✓ Slots table checked/created\n";
    
    // Create indexes for slots
    $slotsNameIndexSQL = "CREATE INDEX IF NOT EXISTS idx_slots_name ON slots(name)";
    $db->execute($slotsNameIndexSQL);
    $slotsProviderIndexSQL = "CREATE INDEX IF NOT EXISTS idx_slots_provider ON slots(provider)";
    $db->execute($slotsProviderIndexSQL);
    echo "✓ Slots table indexes checked/created\n";
    
    // Migration: Create authors table
    $authorsTableSQL = "
        CREATE TABLE IF NOT EXISTS authors (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name VARCHAR(255) NOT NULL,
        image VARCHAR(255) NULL,
        description TEXT NULL,
        social_links JSON NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )";
    $db->execute($authorsTableSQL);
    echo "✓ Authors table checked/created\n";
    
    // Create index for authors
    $authorsNameIndexSQL = "CREATE INDEX IF NOT EXISTS idx_authors_name ON authors(name)";
    $db->execute($authorsNameIndexSQL);
    echo "✓ Authors table index checked/created\n";
    
    echo "Migration completed successfully!\n";
    
} catch (Exception $e) {
    echo " Migration failed: " . $e->getMessage() . "\n";
    exit(1);
}