<?php
if (!defined('SECURE_ACCESS')) {
    header("HTTP/1.1 403 Forbidden");
    exit("Direct access not allowed.");
}
require_once __DIR__ . '/config.php';

function getDBConnection($connectToDb = true) {
    $host = DB_HOST;
    $port = DB_PORT;
    $user = DB_USER;
    $pass = DB_PASS;
    $dbname = DB_NAME;

    $dsn = "pgsql:host=$host;port=$port;";
    if ($connectToDb) {
        $dsn .= "dbname=$dbname;";
    }

    try {
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ];
        return new PDO($dsn, $user, $pass, $options);
    } catch (PDOException $e) {
        // If the database does not exist and we tried connecting to it, we'll handle it during setup
        if ($connectToDb && $e->getCode() == '7' || strpos($e->getMessage(), 'does not exist') !== false) {
            throw $e;
        }
        die("Database connection failed: " . $e->getMessage());
    }
}

// Global PDO instance helper
try {
    $pdo = getDBConnection(true);
} catch (PDOException $e) {
    // If the database doesn't exist yet, we don't block yet so setup.php can run
    $pdo = null;
}

if ($pdo) {
    // Version-based schema migration: run only once per schema version (#16)
    try {
        // Check if schema_version table exists
        $check_sv = $pdo->query("SELECT 1 FROM information_schema.tables WHERE table_name='schema_version'");
        if ($check_sv->rowCount() == 0) {
            $pdo->exec("CREATE TABLE schema_version (version INTEGER NOT NULL DEFAULT 0)");
            $pdo->exec("INSERT INTO schema_version (version) VALUES (0)");
        }

        $current_version = (int)$pdo->query("SELECT version FROM schema_version LIMIT 1")->fetchColumn();
        $target_version = 13; // Increment this when adding new migrations

        if ($current_version < $target_version) {
            // Migration v1: Core schema patches
            if ($current_version < 1) {
                $check = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='registrations' AND column_name='status'");
                if ($check->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE registrations ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'registered'");
                }
                $check_fn = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='users' AND column_name='first_name'");
                if ($check_fn->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE users ADD COLUMN first_name VARCHAR(100) DEFAULT ''");
                }
                $check_ln = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='users' AND column_name='last_name'");
                if ($check_ln->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE users ADD COLUMN last_name VARCHAR(100) DEFAULT ''");
                }
                $check_cms = $pdo->query("SELECT 1 FROM information_schema.tables WHERE table_name='cms_pages'");
                if ($check_cms->rowCount() == 0) {
                    $pdo->exec("
                        CREATE TABLE cms_pages (
                            id SERIAL PRIMARY KEY,
                            slug VARCHAR(100) UNIQUE NOT NULL,
                            title VARCHAR(255) NOT NULL,
                            content TEXT NOT NULL,
                            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                        )
                    ");
                    $pdo->exec("
                        INSERT INTO cms_pages (slug, title, content) VALUES
                        ('privacy', 'Privacy Policy', 'We collect your email address, dynamic username, employee code, and transaction logs. When verifying your account, you upload your PAN Card, Aadhaar Card, and bank account information.'),
                        ('terms', 'Terms & Conditions', 'Welcome to our platform. By accessing our services, you agree to comply with our corporate placement rules, team commission guidelines, and service agreements.')
                        ON CONFLICT (slug) DO NOTHING
                    ");
                }
                $check_contact = $pdo->query("SELECT 1 FROM information_schema.tables WHERE table_name='contact_messages'");
                if ($check_contact->rowCount() == 0) {
                    $pdo->exec("
                        CREATE TABLE contact_messages (
                            id SERIAL PRIMARY KEY,
                            name VARCHAR(100) NOT NULL,
                            email VARCHAR(100) NOT NULL,
                            subject VARCHAR(150) NOT NULL,
                            message TEXT NOT NULL,
                            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            is_read BOOLEAN DEFAULT FALSE
                        )
                    ");
                }
            }

            // Migration v2: Profile fields (previously in profile.php per-request)
            if ($current_version < 2) {
                $check_col = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='users' AND column_name='headline'");
                if ($check_col->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE users ADD COLUMN headline VARCHAR(255) DEFAULT ''");
                    $pdo->exec("ALTER TABLE users ADD COLUMN location VARCHAR(150) DEFAULT ''");
                    $pdo->exec("ALTER TABLE users ADD COLUMN about_summary TEXT");
                    $pdo->exec("ALTER TABLE users ADD COLUMN profile_picture_url VARCHAR(255) DEFAULT ''");
                    $pdo->exec("ALTER TABLE users ADD COLUMN banner_picture_url VARCHAR(255) DEFAULT ''");
                    $pdo->exec("ALTER TABLE users ADD COLUMN skills TEXT");
                }
                $pdo->exec("
                    CREATE TABLE IF NOT EXISTS user_experiences (
                        id SERIAL PRIMARY KEY,
                        user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                        title VARCHAR(150) NOT NULL,
                        company VARCHAR(150) NOT NULL,
                        location VARCHAR(150),
                        start_date DATE NOT NULL,
                        end_date DATE,
                        is_current BOOLEAN DEFAULT FALSE,
                        description TEXT
                    )
                ");
                $pdo->exec("
                    CREATE TABLE IF NOT EXISTS user_educations (
                        id SERIAL PRIMARY KEY,
                        user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                        school VARCHAR(150) NOT NULL,
                        degree VARCHAR(100),
                        field_of_study VARCHAR(150),
                        start_date DATE,
                        end_date DATE,
                        description TEXT
                    )
                ");
            }

            // Migration v3: Audit log table (#14)
            if ($current_version < 3) {
                $pdo->exec("
                    CREATE TABLE IF NOT EXISTS audit_log (
                        id SERIAL PRIMARY KEY,
                        user_id INTEGER,
                        action VARCHAR(100) NOT NULL,
                        entity_type VARCHAR(50),
                        entity_id INTEGER,
                        details TEXT,
                        ip_address VARCHAR(45),
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                ");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_audit_log_user ON audit_log(user_id)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_audit_log_action ON audit_log(action)");
            }

            // Migration v4: Performance indexes + fcm_token + gst_rate setting + invitation expiry
            if ($current_version < 4) {
                // High-frequency query indexes
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_users_referrer_id ON users(referrer_id)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_users_parent_id ON users(parent_id)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_users_status ON users(status)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_earnings_user_id ON earnings(user_id)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_earnings_buyer_id ON earnings(buyer_id)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_earnings_event_id ON earnings(event_id)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_registrations_event_id ON registrations(event_id)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_registrations_user_id ON registrations(user_id)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_registrations_status ON registrations(status)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_payouts_user_id ON payouts(user_id)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_payouts_status ON payouts(status)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_notifications_user_id ON notifications(user_id)");
                $pdo->exec("CREATE INDEX IF NOT EXISTS idx_notifications_is_read ON notifications(is_read)");

                // FCM push token for users
                $check_fcm = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='users' AND column_name='fcm_token'");
                if ($check_fcm->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE users ADD COLUMN fcm_token VARCHAR(255) DEFAULT NULL");
                }

                // GST rate setting (configurable from admin panel)
                $pdo->exec("INSERT INTO system_settings (key, value) VALUES ('gst_rate', '18') ON CONFLICT (key) DO NOTHING");

                // Invitation expiry: add expires_at to user_invitations if table exists
                $check_inv = $pdo->query("SELECT 1 FROM information_schema.tables WHERE table_name='user_invitations'");
                if ($check_inv->rowCount() > 0) {
                    $check_exp = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='user_invitations' AND column_name='expires_at'");
                    if ($check_exp->rowCount() == 0) {
                        $pdo->exec("ALTER TABLE user_invitations ADD COLUMN expires_at TIMESTAMP DEFAULT NULL");
                        // Set existing pending invitations to expire 30 days from now
                        $pdo->exec("UPDATE user_invitations SET expires_at = CURRENT_TIMESTAMP + INTERVAL '30 days' WHERE status = 'pending' AND expires_at IS NULL");
                    }
                }
            }

            // Migration v5: Wallet Payments for Designations
            if ($current_version < 5) {
                $check_desig = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='designations' AND column_name='allow_wallet_payment'");
                if ($check_desig->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE designations ADD COLUMN allow_wallet_payment BOOLEAN DEFAULT FALSE");
                }
            }

            // Migration v6: Email Verification and 2FA
            if ($current_version < 6) {
                $check_ev = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='users' AND column_name='is_email_verified'");
                if ($check_ev->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE users ADD COLUMN is_email_verified BOOLEAN DEFAULT FALSE");
                    $pdo->exec("ALTER TABLE users ADD COLUMN email_verification_token VARCHAR(255) DEFAULT NULL");
                    $pdo->exec("ALTER TABLE users ADD COLUMN is_2fa_enabled BOOLEAN DEFAULT FALSE");
                    $pdo->exec("ALTER TABLE users ADD COLUMN two_factor_code VARCHAR(10) DEFAULT NULL");
                    $pdo->exec("ALTER TABLE users ADD COLUMN two_factor_expires TIMESTAMP DEFAULT NULL");
                    
                    // Add notification templates
                    $pdo->exec("
                        INSERT INTO notification_templates (template_name, subject, body, channels_csv) VALUES
                        ('email_verification', 'Verify your email address', 'Hello {{username}}, please verify your email by clicking: {{verification_link}}', 'email'),
                        ('2fa_code', 'Your Two-Factor Authentication Code', 'Hello {{username}}, your login code is: {{code}}', 'email,whatsapp')
                        ON CONFLICT (template_name) DO NOTHING
                    ");
                }
            }
            // Migration v7: Event Attachment for Organizers
            if ($current_version < 7) {
                $check_att = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='events' AND column_name='attachment_url'");
                if ($check_att->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE events ADD COLUMN attachment_url VARCHAR(255) DEFAULT ''");
                }
            }

            // Migration v8: Event edit support columns
            if ($current_version < 8) {
                $check_upd = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='events' AND column_name='updated_at'");
                if ($check_upd->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE events ADD COLUMN updated_at TIMESTAMP DEFAULT NULL");
                }
            }

            // Migration v9: KYC Verification Deposit Expiry Support
            if ($current_version < 9) {
                $check_exp = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='kyc_records' AND column_name='deposit_expires_at'");
                if ($check_exp->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE kyc_records ADD COLUMN deposit_expires_at TIMESTAMP DEFAULT NULL");
                }
            }

            // Migration v10: Designation Promotion Template
            if ($current_version < 10) {
                $pdo->exec("
                    INSERT INTO notification_templates (template_name, subject, body, channels_csv) VALUES
                    ('designation_promotion', 'Congratulations on your Promotion!', 'Hello {{username}}, you have been promoted to the rank of {{designation_title}}!', 'in_app,email')
                    ON CONFLICT (template_name) DO NOTHING
                ");
            }

            // Migration v11: Add status column to events (draft/published)
            if ($current_version < 11) {
                $check_status = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='events' AND column_name='status'");
                if ($check_status->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE events ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'draft'");
                    // Existing events should be updated to 'published' so they don't hide
                    $pdo->exec("UPDATE events SET status = 'published'");
                }
            }

            // Migration v12: PIN Based Payment System Table
            if ($current_version < 12) {
                $check_pins = $pdo->query("SELECT 1 FROM information_schema.tables WHERE table_name='registration_pins'");
                if ($check_pins->rowCount() == 0) {
                    $pdo->exec("
                        CREATE TABLE registration_pins (
                            id SERIAL PRIMARY KEY,
                            pin VARCHAR(50) UNIQUE NOT NULL,
                            amount NUMERIC(10, 2) NOT NULL,
                            status VARCHAR(20) NOT NULL DEFAULT 'active',
                            created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
                            used_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
                            event_id INTEGER REFERENCES events(id) ON DELETE SET NULL,
                            registration_id INTEGER REFERENCES registrations(id) ON DELETE SET NULL,
                            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                            used_at TIMESTAMP DEFAULT NULL
                        )
                    ");
                    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_registration_pins_code ON registration_pins(pin)");
                    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_registration_pins_status ON registration_pins(status)");
                }
            }

            // Migration text v13: Event Based Payment Gateway Support
            if ($current_version < 13) {
                $check_col = $pdo->query("SELECT column_name FROM information_schema.columns WHERE table_name='events' AND column_name='razorpay_key_id'");
                if ($check_col->rowCount() == 0) {
                    $pdo->exec("ALTER TABLE events ADD COLUMN razorpay_key_id VARCHAR(255) DEFAULT NULL");
                    $pdo->exec("ALTER TABLE events ADD COLUMN razorpay_key_secret VARCHAR(255) DEFAULT NULL");
                }
            }

            // Update schema version
            $pdo->exec("UPDATE schema_version SET version = $target_version");
        }
    } catch (PDOException $e) {
        error_log("Schema migration error: " . $e->getMessage());
    }
}
