<?php
define('SECURE_ACCESS', true);
require_once __DIR__ . '/../includes/config.php';
require_once __DIR__ . '/../includes/db.php';

// Prevent re-running setup if users table and admins already exist
$isCli = (php_sapi_name() === 'cli');
if (!$isCli && $pdo !== null) {
    try {
        $stmt = $pdo->query("SELECT 1 FROM information_schema.tables WHERE table_name = 'users'");
        if ($stmt->fetchColumn()) {
            $stmt2 = $pdo->query("SELECT COUNT(*) FROM users WHERE role = 'admin'");
            if ($stmt2->fetchColumn() > 0) {
                http_response_code(403);
                exit("Forbidden: Setup cannot be re-run because the application is already configured.");
            }
        }
    } catch (PDOException $e) {
        // Table doesn't exist yet, allow setup
    }
}

$status_messages = [];
$success = false;
$setup_attempted = false;

if ($isCli || ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['run_setup']))) {
    $setup_attempted = true;
    $success = true;

    try {
        // First try to connect to the configured database directly (for cPanel environments where DB is pre-created)
        $db = getDBConnection(true);
        $status_messages[] = ["info", "Database '" . DB_NAME . "' is successfully connected."];
    } catch (PDOException $e) {
        // If that fails, try to connect to PostgreSQL server (without specific DB) to create it
        try {
            $pdo_init = getDBConnection(false);
            $db_name = DB_NAME;
            $pdo_init->exec("CREATE DATABASE \"$db_name\"");
            $status_messages[] = ["success", "Database '$db_name' successfully created."];
        } catch (PDOException $e2) {
            $status_messages[] = ["danger", "Failed to connect to PostgreSQL database '" . DB_NAME . "' or server: " . $e->getMessage()];
            $success = false;
        }
    }

if ($success) {
    try {
        // 2. Connect to the database
        $db = getDBConnection(true);

        // Drop existing tables in reverse dependency order
        $db->exec("DROP TABLE IF EXISTS registration_pins CASCADE");
        $db->exec("DROP TABLE IF EXISTS notifications CASCADE");
        $db->exec("DROP TABLE IF EXISTS notification_templates CASCADE");
        $db->exec("DROP TABLE IF EXISTS notification_channels CASCADE");
        $db->exec("DROP TABLE IF EXISTS payouts CASCADE");
        $db->exec("DROP TABLE IF EXISTS kyc_records CASCADE");
        $db->exec("DROP TABLE IF EXISTS event_sponsors CASCADE");
        $db->exec("DROP TABLE IF EXISTS event_speakers CASCADE");
        $db->exec("DROP TABLE IF EXISTS event_agendas CASCADE");
        $db->exec("DROP TABLE IF EXISTS registrations CASCADE");
        $db->exec("DROP TABLE IF EXISTS events CASCADE");
        $db->exec("DROP TABLE IF EXISTS speaker_profiles CASCADE");
        $db->exec("DROP TABLE IF EXISTS speaker_invitations CASCADE");
        $db->exec("DROP TABLE IF EXISTS organizer_profiles CASCADE");
        $db->exec("DROP TABLE IF EXISTS organizer_invitations CASCADE");
        $db->exec("DROP TABLE IF EXISTS sponsor_profiles CASCADE");
        $db->exec("DROP TABLE IF EXISTS sponsor_invitations CASCADE");
        $db->exec("DROP TABLE IF EXISTS user_experiences CASCADE");
        $db->exec("DROP TABLE IF EXISTS user_educations CASCADE");
        $db->exec("DROP TABLE IF EXISTS user_designation_history CASCADE");
        $db->exec("DROP TABLE IF EXISTS user_invitations CASCADE");
        $db->exec("DROP TABLE IF EXISTS system_settings CASCADE");
        $db->exec("DROP TABLE IF EXISTS earnings CASCADE");
        $db->exec("DROP TABLE IF EXISTS users CASCADE");
        $db->exec("DROP TABLE IF EXISTS designations CASCADE");
        $db->exec("DROP TABLE IF EXISTS cms_pages CASCADE");
        $db->exec("DROP TABLE IF EXISTS contact_messages CASCADE");
        $db->exec("DROP TABLE IF EXISTS schema_version CASCADE");
        
        $status_messages[] = ["success", "Cleared old database tables for schema recreation."];

        // 3. Create Designations Table
        $create_designations = "
            CREATE TABLE designations (
                id SERIAL PRIMARY KEY,
                title VARCHAR(100) NOT NULL,
                description TEXT,
                is_default BOOLEAN DEFAULT FALSE,
                matrix_width INTEGER DEFAULT 4,
                matrix_depth INTEGER DEFAULT 5,
                level_commissions VARCHAR(100) DEFAULT '10,5,5,10,20',
                criteria_direct_referrals INTEGER DEFAULT 0,
                criteria_team_size INTEGER DEFAULT 0,
                criteria_min_payout NUMERIC(10, 2) DEFAULT 0.00,
                criteria_max_earning_cap NUMERIC(10, 2) DEFAULT 0.00,
                criteria_min_duration_months INTEGER DEFAULT 0,
                accessible_event_ids TEXT DEFAULT '', -- Comma-separated list of event IDs
                allow_wallet_payment BOOLEAN DEFAULT FALSE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_designations);
        $status_messages[] = ["success", "Table 'designations' created."];

        // 4. Create Users Table (with matrix relationships and designations)
        $create_users = "
            CREATE TABLE users (
                id SERIAL PRIMARY KEY,
                username VARCHAR(50) UNIQUE NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                phone VARCHAR(30) UNIQUE,
                password VARCHAR(255) NOT NULL,
                role VARCHAR(20) NOT NULL DEFAULT 'user', -- 'admin', 'user', 'speaker', 'organizer', 'sponsor'
                status VARCHAR(20) NOT NULL DEFAULT 'active',
                employee_code VARCHAR(50) UNIQUE,
                designation_id INTEGER REFERENCES designations(id) ON DELETE SET NULL,
                referrer_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
                parent_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
                headline VARCHAR(255) DEFAULT '',
                first_name VARCHAR(100) DEFAULT '',
                last_name VARCHAR(100) DEFAULT '',
                location VARCHAR(150) DEFAULT '',
                about_summary TEXT,
                profile_picture_url VARCHAR(255) DEFAULT '',
                banner_picture_url VARCHAR(255) DEFAULT '',
                skills TEXT,
                reset_token VARCHAR(255) DEFAULT NULL,
                reset_token_expires TIMESTAMP DEFAULT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_users);
        $status_messages[] = ["success", "Table 'users' created with matrix structures."];

        // 5. Create System Settings Table
        $create_settings = "
            CREATE TABLE system_settings (
                key VARCHAR(50) PRIMARY KEY,
                value TEXT NOT NULL
            )
        ";
        $db->exec($create_settings);
        $status_messages[] = ["success", "Table 'system_settings' created."];

        // 5a. Create User Experiences Table
        $create_user_experiences = "
            CREATE TABLE 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
            )
        ";
        $db->exec($create_user_experiences);
        $status_messages[] = ["success", "Table 'user_experiences' created."];

        // 5b. Create User Educations Table
        $create_user_educations = "
            CREATE TABLE 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
            )
        ";
        $db->exec($create_user_educations);
        $status_messages[] = ["success", "Table 'user_educations' created."];

        // 5c. Create User Designation History Table
        $create_user_designation_history = "
            CREATE TABLE user_designation_history (
                id SERIAL PRIMARY KEY,
                user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                designation_id INTEGER REFERENCES designations(id) ON DELETE CASCADE,
                achieved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_user_designation_history);
        $status_messages[] = ["success", "Table 'user_designation_history' created."];

        // 5d. Create Trigger for User Designation Change Logging
        $db->exec("
            CREATE OR REPLACE FUNCTION log_user_designation_change()
            RETURNS TRIGGER AS $$
            BEGIN
                IF (TG_OP = 'INSERT') THEN
                    IF (NEW.designation_id IS NOT NULL) THEN
                        INSERT INTO user_designation_history (user_id, designation_id, achieved_at)
                        VALUES (NEW.id, NEW.designation_id, NEW.created_at);
                    END IF;
                ELSIF (TG_OP = 'UPDATE') THEN
                    IF (COALESCE(NEW.designation_id, 0) != COALESCE(OLD.designation_id, 0)) THEN
                        IF (NEW.designation_id IS NOT NULL) THEN
                            INSERT INTO user_designation_history (user_id, designation_id, achieved_at)
                            VALUES (NEW.id, NEW.designation_id, CURRENT_TIMESTAMP);
                        END IF;
                    END IF;
                END IF;
                RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;
        ");
        $db->exec("DROP TRIGGER IF EXISTS user_designation_change_trigger ON users");
        $db->exec("
            CREATE TRIGGER user_designation_change_trigger
            AFTER INSERT OR UPDATE ON users
            FOR EACH ROW
            EXECUTE PROCEDURE log_user_designation_change();
        ");
        $status_messages[] = ["success", "Created auto-logging trigger for user designation promotion history."];

        // 6. Create User Invitations Table
        $create_user_invitations = "
            CREATE TABLE user_invitations (
                id SERIAL PRIMARY KEY,
                email VARCHAR(100) UNIQUE NOT NULL,
                token VARCHAR(64) UNIQUE NOT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_user_invitations);
        $status_messages[] = ["success", "Table 'user_invitations' created."];

        // 7. Create Speaker Profiles Table
        $create_speaker_profiles = "
            CREATE TABLE speaker_profiles (
                id SERIAL PRIMARY KEY,
                user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                full_name VARCHAR(100) NOT NULL,
                bio TEXT,
                expertise VARCHAR(150),
                organization VARCHAR(100)
            )
        ";
        $db->exec($create_speaker_profiles);
        $status_messages[] = ["success", "Table 'speaker_profiles' created."];

        // 8. Create Speaker Invitations Table
        $create_invitations = "
            CREATE TABLE speaker_invitations (
                id SERIAL PRIMARY KEY,
                email VARCHAR(100) UNIQUE NOT NULL,
                token VARCHAR(64) UNIQUE NOT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_invitations);
        $status_messages[] = ["success", "Table 'speaker_invitations' created."];

        // 9. Create Organizer Profiles Table
        $create_organizer_profiles = "
            CREATE TABLE organizer_profiles (
                id SERIAL PRIMARY KEY,
                user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                full_name VARCHAR(100) NOT NULL,
                phone VARCHAR(30),
                company VARCHAR(100),
                department VARCHAR(100)
            )
        ";
        $db->exec($create_organizer_profiles);
        $status_messages[] = ["success", "Table 'organizer_profiles' created."];

        // 10. Create Organizer Invitations Table
        $create_org_invitations = "
            CREATE TABLE organizer_invitations (
                id SERIAL PRIMARY KEY,
                email VARCHAR(100) UNIQUE NOT NULL,
                token VARCHAR(64) UNIQUE NOT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_org_invitations);
        $status_messages[] = ["success", "Table 'organizer_invitations' created."];

        // 11. Create Sponsor Profiles Table
        $create_sponsor_profiles = "
            CREATE TABLE sponsor_profiles (
                id SERIAL PRIMARY KEY,
                user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                company_name VARCHAR(150) NOT NULL,
                logo_url VARCHAR(255),
                website_url VARCHAR(255),
                default_tier VARCHAR(30) NOT NULL DEFAULT 'Silver',
                description TEXT
            )
        ";
        $db->exec($create_sponsor_profiles);
        $status_messages[] = ["success", "Table 'sponsor_profiles' created."];

        // 12. Create Sponsor Invitations Table
        $create_sponsor_invitations = "
            CREATE TABLE sponsor_invitations (
                id SERIAL PRIMARY KEY,
                email VARCHAR(100) UNIQUE NOT NULL,
                token VARCHAR(64) UNIQUE NOT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_sponsor_invitations);
        $status_messages[] = ["success", "Table 'sponsor_invitations' created."];

        // 13. Create Events Table
        $create_events = "
            CREATE TABLE events (
                id SERIAL PRIMARY KEY,
                title VARCHAR(150) NOT NULL,
                description TEXT,
                start_date TIMESTAMP NOT NULL,
                end_date TIMESTAMP NOT NULL,
                location VARCHAR(150) NOT NULL,
                capacity INTEGER NOT NULL DEFAULT 0,
                banner_url VARCHAR(255) DEFAULT '',
                event_type VARCHAR(20) NOT NULL DEFAULT 'physical',
                google_map_embed TEXT DEFAULT '',
                virtual_platform VARCHAR(50) DEFAULT '',
                virtual_link VARCHAR(255) DEFAULT '',
                payment_type VARCHAR(20) NOT NULL DEFAULT 'free',
                price NUMERIC(10, 2) DEFAULT 0.00,
                gst_option VARCHAR(20) DEFAULT 'excluding',
                created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
                is_recurring BOOLEAN DEFAULT FALSE,
                recurrence_pattern VARCHAR(20) DEFAULT NULL,
                recurrence_end_date TIMESTAMP DEFAULT NULL,
                recurrence_group_id VARCHAR(50) DEFAULT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'draft',
                razorpay_key_id VARCHAR(255) DEFAULT NULL,
                razorpay_key_secret VARCHAR(255) DEFAULT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_events);
        $status_messages[] = ["success", "Table 'events' created."];

        // 14. Create Registrations Table
        $create_registrations = "
            CREATE TABLE registrations (
                id SERIAL PRIMARY KEY,
                event_id INTEGER REFERENCES events(id) ON DELETE CASCADE,
                user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                reminder_sent BOOLEAN DEFAULT FALSE,
                registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                base_price NUMERIC(10, 2) DEFAULT 0.00,
                gst_rate NUMERIC(5, 2) DEFAULT 0.00,
                gst_amount NUMERIC(10, 2) DEFAULT 0.00,
                total_payable NUMERIC(10, 2) DEFAULT 0.00,
                razorpay_payment_id VARCHAR(100) DEFAULT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'registered',
                UNIQUE (event_id, user_id)
            )
        ";
        $db->exec($create_registrations);
        $status_messages[] = ["success", "Table 'registrations' created."];

        // 14b. Create CMS Pages Table
        $create_cms_pages = "
            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
            )
        ";
        $db->exec($create_cms_pages);
        $status_messages[] = ["success", "Table 'cms_pages' created."];

        // 14c. Create Contact Messages Table
        $create_contact_messages = "
            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
            )
        ";
        $db->exec($create_contact_messages);
        $status_messages[] = ["success", "Table 'contact_messages' created."];

        // 15. Create Event Agendas Table
        $create_agendas = "
            CREATE TABLE event_agendas (
                id SERIAL PRIMARY KEY,
                event_id INTEGER REFERENCES events(id) ON DELETE CASCADE,
                speaker_profile_id INTEGER REFERENCES speaker_profiles(id) ON DELETE SET NULL,
                agenda_date DATE NOT NULL,
                start_time TIME NOT NULL,
                end_time TIME NOT NULL,
                title VARCHAR(150) NOT NULL,
                speaker VARCHAR(100),
                description TEXT
            )
        ";
        $db->exec($create_agendas);
        $status_messages[] = ["success", "Table 'event_agendas' created."];

        // 16. Create Event Sponsors Map Table
        $create_event_sponsors = "
            CREATE TABLE event_sponsors (
                id SERIAL PRIMARY KEY,
                event_id INTEGER REFERENCES events(id) ON DELETE CASCADE,
                sponsor_profile_id INTEGER REFERENCES sponsor_profiles(id) ON DELETE CASCADE,
                tier VARCHAR(30) NOT NULL,
                UNIQUE (event_id, sponsor_profile_id)
            )
        ";
        $db->exec($create_event_sponsors);
        $status_messages[] = ["success", "Table 'event_sponsors' created."];

        // 16b. Create Event Speakers Map Table
        $create_event_speakers = "
            CREATE TABLE event_speakers (
                id SERIAL PRIMARY KEY,
                event_id INTEGER REFERENCES events(id) ON DELETE CASCADE,
                speaker_profile_id INTEGER REFERENCES speaker_profiles(id) ON DELETE CASCADE,
                UNIQUE (event_id, speaker_profile_id)
            )
        ";
        $db->exec($create_event_speakers);
        $status_messages[] = ["success", "Table 'event_speakers' created."];

        // 17. Create Earnings Table (Commissions tracking)
        $create_earnings = "
            CREATE TABLE earnings (
                id SERIAL PRIMARY KEY,
                user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                buyer_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
                event_id INTEGER REFERENCES events(id) ON DELETE SET NULL,
                amount NUMERIC(10, 2) NOT NULL,
                level INTEGER DEFAULT 0,
                percentage NUMERIC(5, 2) DEFAULT 0.00,
                type VARCHAR(50) DEFAULT 'commission',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_earnings);
        $status_messages[] = ["success", "Table 'earnings' created."];

        // 17.5. Create KYC Records Table
        $create_kyc = "
            CREATE TABLE kyc_records (
                id SERIAL PRIMARY KEY,
                user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                doc1_type VARCHAR(50),
                doc1_number VARCHAR(50),
                doc1_file VARCHAR(255),
                doc1_status VARCHAR(20) DEFAULT 'pending',
                doc2_type VARCHAR(50),
                doc2_number VARCHAR(50),
                doc2_file VARCHAR(255),
                doc2_status VARCHAR(20) DEFAULT 'pending',
                bank_name VARCHAR(100),
                account_holder VARCHAR(100),
                account_number VARCHAR(100),
                routing_number VARCHAR(50),
                deposit_1 NUMERIC(4, 2),
                deposit_2 NUMERIC(4, 2),
                bank_status VARCHAR(20) DEFAULT 'unverified',
                deposit_expires_at TIMESTAMP DEFAULT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_kyc);
        $status_messages[] = ["success", "Table 'kyc_records' created."];

        // 17.6 Create Payouts Table
        $create_payouts = "
            CREATE TABLE payouts (
                id SERIAL PRIMARY KEY,
                user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                amount NUMERIC(10, 2) NOT NULL,
                platform_fee NUMERIC(10, 2) DEFAULT 0.00,
                tds_amount NUMERIC(10, 2) DEFAULT 0.00,
                net_amount NUMERIC(10, 2) DEFAULT 0.00,
                status VARCHAR(20) DEFAULT 'pending',
                bank_name VARCHAR(100),
                account_number VARCHAR(100),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_payouts);
        $status_messages[] = ["success", "Table 'payouts' created."];

        // 17.65 Create Registration PINs Table
        $create_pins = "
            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
            )
        ";
        $db->exec($create_pins);
        $status_messages[] = ["success", "Table 'registration_pins' created."];

        // 17.7 Create Notification Channels Table
        $create_channels = "
            CREATE TABLE notification_channels (
                id SERIAL PRIMARY KEY,
                channel_name VARCHAR(50) UNIQUE NOT NULL,
                is_enabled BOOLEAN DEFAULT TRUE,
                config_json TEXT NOT NULL
            )
        ";
        $db->exec($create_channels);
        $status_messages[] = ["success", "Table 'notification_channels' created."];

        // 17.8 Create Notification Templates Table
        $create_templates = "
            CREATE TABLE notification_templates (
                id SERIAL PRIMARY KEY,
                template_name VARCHAR(100) UNIQUE NOT NULL,
                subject VARCHAR(255),
                body TEXT NOT NULL,
                channels_csv VARCHAR(150) NOT NULL
            )
        ";
        $db->exec($create_templates);
        $status_messages[] = ["success", "Table 'notification_templates' created."];

        // 17.9 Create Notifications Table
        $create_notifications = "
            CREATE TABLE notifications (
                id SERIAL PRIMARY KEY,
                user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
                title VARCHAR(150) NOT NULL,
                message TEXT NOT NULL,
                channels_sent VARCHAR(150) NOT NULL,
                delivery_logs TEXT,
                is_read BOOLEAN DEFAULT FALSE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $db->exec($create_notifications);
        $status_messages[] = ["success", "Table 'notifications' created."];

        // Seed Notification Channels
        $ins_channel = $db->prepare("INSERT INTO notification_channels (channel_name, is_enabled, config_json) VALUES (?, ?, ?)");
        $ins_channel->execute(['whatsapp', true, json_encode(['api_url' => 'https://api.whatsapp.com/v1/send', 'token' => 'wa_mock_token_xyz_123'])]);
        $ins_channel->execute(['telegram', true, json_encode(['bot_token' => 'bot123456789:mockTelegramBotToken', 'chat_id' => '@event_app_channel'])]);
        $ins_channel->execute(['email', true, json_encode(['smtp_host' => 'smtp.mailtrap.io', 'smtp_port' => '2525', 'smtp_user' => 'mock_user', 'smtp_pass' => 'mock_pass'])]);
        $ins_channel->execute(['sms', true, json_encode(['provider' => 'twilio', 'account_sid' => 'ACmockTwilioAccountSid12345', 'auth_token' => 'mockTwilioAuthToken'])]);
        $ins_channel->execute(['in_app', true, json_encode(['inbox_enabled' => 'true'])]);
        $ins_channel->execute(['push', true, json_encode(['app_id' => 'mock-onesignal-app-id-12345', 'api_key' => 'mock-onesignal-rest-api-key'])]);
        $status_messages[] = ["success", "Seeded default notification channels."];

        // Seed Notification Templates
        $ins_template = $db->prepare("INSERT INTO notification_templates (template_name, subject, body, channels_csv) VALUES (?, ?, ?, ?)");
        $ins_template->execute([
            'event_registration',
            'RSVP Confirmed: {{event_title}}',
            'Hello {{username}}, your ticket for the event "{{event_title}}" has been successfully confirmed. We look forward to seeing you at {{location}} starting on {{start_date}}.',
            'email,in_app,sms'
        ]);
        $ins_template->execute([
            'kyc_status',
            'Security Compliance: KYC {{status}}',
            'Hello {{username}}, your uploaded KYC verification documents have been reviewed by our compliance team and marked as: {{status}}.',
            'email,in_app,push'
        ]);
        $ins_template->execute([
            'payout_status',
            'Financial Transfer: Payout {{status}}',
            'Hello {{username}}, your commission withdrawal request of ₹{{amount}} has been officially processed and marked as: {{status}}.',
            'email,in_app,whatsapp,telegram'
        ]);
        $ins_template->execute([
            'event_reminder',
            'Reminder: Upcoming Event {{event_title}}',
            'Hello {{username}}, this is a friendly reminder that the event "{{event_title}}" is starting soon on {{start_date}} at {{location}}. We look forward to your attendance!',
            'email,in_app,sms'
        ]);
        $status_messages[] = ["success", "Seeded default notification templates."];

        // Seed default CMS pages
        $db->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.')
        ");
        $status_messages[] = ["success", "Seeded default CMS pages (Privacy and Terms)."];

        // 18. Seed Designations (using raw SQL execution to prevent boolean bind conversions)
        $db->exec("
            INSERT INTO designations (title, description, is_default, matrix_width, matrix_depth, level_commissions, criteria_direct_referrals, criteria_team_size, criteria_min_payout, criteria_max_earning_cap, criteria_min_duration_months, accessible_event_ids) 
            VALUES (
                'Trainee', 
                'Step 1: Default entry level position. Total 10% commission on 1 level.', 
                TRUE, 
                4, 
                5, 
                '10,5,5,10,20', 
                0, 
                0, 
                0.00, 
                1000.00, 
                0, 
                ''
            )
        ");
        $default_desig_id = $db->lastInsertId();

        $db->exec("
            INSERT INTO designations (title, description, is_default, matrix_width, matrix_depth, level_commissions, criteria_direct_referrals, criteria_team_size, criteria_min_payout, criteria_max_earning_cap, criteria_min_duration_months, accessible_event_ids) 
            VALUES (
                'Executive', 
                'Step 2: Earn 15% commissions down 2 levels.', 
                FALSE, 
                4, 
                5, 
                '10,5,5,10,20', 
                2, 
                10, 
                100.00, 
                3000.00, 
                1, 
                ''
            )
        ");

        $db->exec("
            INSERT INTO designations (title, description, is_default, matrix_width, matrix_depth, level_commissions, criteria_direct_referrals, criteria_team_size, criteria_min_payout, criteria_max_earning_cap, criteria_min_duration_months, accessible_event_ids) 
            VALUES (
                'Manager', 
                'Step 3: Earn 20% commissions down 3 levels.', 
                FALSE, 
                4, 
                5, 
                '10,5,5,10,20', 
                4, 
                30, 
                400.00, 
                8000.00, 
                2, 
                ''
            )
        ");

        $db->exec("
            INSERT INTO designations (title, description, is_default, matrix_width, matrix_depth, level_commissions, criteria_direct_referrals, criteria_team_size, criteria_min_payout, criteria_max_earning_cap, criteria_min_duration_months, accessible_event_ids) 
            VALUES (
                'Director', 
                'Step 4: Earn 30% commissions down 4 levels.', 
                FALSE, 
                4, 
                5, 
                '10,5,5,10,20', 
                6, 
                70, 
                1000.00, 
                15000.00, 
                3, 
                ''
            )
        ");

        $db->exec("
            INSERT INTO designations (title, description, is_default, matrix_width, matrix_depth, level_commissions, criteria_direct_referrals, criteria_team_size, criteria_min_payout, criteria_max_earning_cap, criteria_min_duration_months, accessible_event_ids) 
            VALUES (
                'Vice President', 
                'Step 5: Earn 50% commissions down 5 levels (Maximum level incentives).', 
                FALSE, 
                4, 
                5, 
                '10,5,5,10,20', 
                10, 
                150, 
                3000.00, 
                40000.00, 
                6, 
                ''
            )
        ");
        $status_messages[] = ["success", "Seeded default designations: Trainee to Vice President (5 steps)."];

        // 19. Seed System Settings (Employee Code Defaults)
        $ins_setting = $db->prepare("INSERT INTO system_settings (\"key\", value) VALUES (?, ?)");
        $ins_setting->execute(['emp_code_prefix', 'REPL']);
        $ins_setting->execute(['emp_code_length', '15']);
        $ins_setting->execute(['emp_code_default_type', 'generated']);
        $ins_setting->execute(['emp_code_custom_value', 'EMP-DEFAULT']);
        $ins_setting->execute(['signup_bonus', '1000']);
        $ins_setting->execute(['referral_bonus', '500']);
        $ins_setting->execute(['referral_bonus_levels', '5']);
        $ins_setting->execute(['referral_bonus_cap', '4000']);
        $ins_setting->execute(['gst_rate', '18.00']);
        $ins_setting->execute(['default_kyc_required_docs', '2']);
        $ins_setting->execute(['bank_verification_min_deposit', '0.01']);
        $ins_setting->execute(['bank_verification_max_deposit', '0.99']);
        $ins_setting->execute(['site_name', 'RENENET EDUCARE']);
        $ins_setting->execute(['site_tagline', 'Event Management & Corporate Hierarchy']);
        $ins_setting->execute(['site_logo', '']);
        $ins_setting->execute(['site_favicon', '']);
        $ins_setting->execute(['hide_site_name', '0']);
        $ins_setting->execute(['company_address', 'Bandra Kurla Complex, Mumbai, Maharashtra - 400051']);
        $ins_setting->execute(['company_gstin', '27AAACE1234F1Z0']);
        $ins_setting->execute(['razorpay_key_id', '']);
        $ins_setting->execute(['razorpay_key_secret', '']);
        $ins_setting->execute(['registration_mode', 'open']);
        $ins_setting->execute(['default_referrer_code', 'REPLUS123456789']);
        $ins_setting->execute(['payout_platform_fee', '5.00']);
        $ins_setting->execute(['payout_tds_with_pan', '5.00']);
        $ins_setting->execute(['payout_tds_without_pan', '20.00']);
        
        // Seed backup settings defaults
        $ins_setting->execute(['backup_frequency', 'disabled']);
        $ins_setting->execute(['backup_custom_interval', '24']);
        $ins_setting->execute(['backup_custom_unit', 'hours']);
        $ins_setting->execute(['backup_dest_local', '1']);
        $ins_setting->execute(['backup_dest_gdrive', '0']);
        $ins_setting->execute(['backup_dest_onedrive', '0']);
        $ins_setting->execute(['backup_prune', '0']);
        $ins_setting->execute(['backup_prune_max', '5']);
        
        // Seed Google Analytics settings defaults
        $ins_setting->execute(['google_analytics_enabled', '0']);
        $ins_setting->execute(['google_analytics_id', '']);

        $status_messages[] = ["success", "Seeded default employee code, bonus, compliance, branding, backup, and analytics system configuration parameters."];

        // 20. Seed Users (with initial employee codes and rank)
        $admin_pass = password_hash('Prash@0281', PASSWORD_DEFAULT);
        $user_pass = password_hash('user123', PASSWORD_DEFAULT);
        $speaker_pass = password_hash('speaker123', PASSWORD_DEFAULT);
        $organizer_pass = password_hash('organizer123', PASSWORD_DEFAULT);
        $sponsor_pass = password_hash('sponsor123', PASSWORD_DEFAULT);

        $insert_user = $db->prepare("INSERT INTO users (username, email, phone, password, role, status, employee_code, designation_id, first_name, last_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING id");
        
        $insert_user->execute(['admin', 'admin@reneneteducare.com', '+919876543200', $admin_pass, 'admin', 'active', 'REPLAD123456789', null, 'Admin', 'User']);
        $admin_id = $insert_user->fetchColumn();
        $insert_user->closeCursor();

        // Only Admin user seeded.

        // 25. Seed Default Events
        $insert_event = $db->prepare("
            INSERT INTO events (title, description, start_date, end_date, location, capacity, banner_url, event_type, google_map_embed, virtual_platform, virtual_link, payment_type, price, gst_option, created_by) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        
        $start_1 = date('Y-m-d H:i:s', strtotime('+2 days 09:00:00'));
        $end_1 = date('Y-m-d H:i:s', strtotime('+4 days 17:00:00'));

        $start_2 = date('Y-m-d H:i:s', strtotime('+8 days 10:00:00'));
        $end_2 = date('Y-m-d H:i:s', strtotime('+9 days 16:00:00'));

        $map_embed_1 = '<iframe src="https://www.google.com/maps/embed?pb=!1m18!1m12!1m3!1d3151.835434509374!2d144.9537353153158!3d-37.81732767975171!2m3!1f0!2f0!3f0!3m2!1i1024!2i768!4f13.1!3m3!1m2!1s0x6ad65d4c2b349649%3A0xb6899234e561db11!2sEnvato!5e0!3m2!1sen!2sus!4v1625068225239!5m2!1sen!2sus" width="100%" height="250" style="border:0;" allowfullscreen="" loading="lazy"></iframe>';

        $insert_event->execute([
            'Tech Conference 2026',
            'Welcome to the premier tech event of 2026. This conference features multiple tracks, keynotes from industry veterans, and massive networking opportunities.',
            $start_1,
            $end_1,
            'Grand Pavilion Hall, Melbourne VIC',
            150,
            'https://images.unsplash.com/photo-1540575467063-178a50c2df87?w=1200&auto=format&fit=crop',
            'physical',
            $map_embed_1,
            '',
            '',
            'paid',
            100.00,
            'excluding',
            $admin_id
        ]);
        $event_id_1 = $db->lastInsertId();

        $insert_event->execute([
            'Web Design Bootcamp',
            'An immersive web design bootcamp covering modern workflows, responsive CSS techniques, and web design fundamentals.',
            $start_2,
            $end_2,
            'Online Webcast',
            80,
            'https://images.unsplash.com/photo-1515378791036-0648a3ef77b2?w=1200&auto=format&fit=crop',
            'virtual',
            '',
            'zoom',
            'https://zoom.us/j/9876543210?pwd=TkVzWkZscjhYZEtQdz05',
            'free',
            0.00,
            'excluding',
            $admin_id
        ]);
        $event_id_2 = $db->lastInsertId();

        // 26. Seed Agenda items
        $insert_agenda = $db->prepare("
            INSERT INTO event_agendas (event_id, speaker_profile_id, agenda_date, start_time, end_time, title, speaker, description) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ");

        $date_day1 = date('Y-m-d', strtotime('+2 days'));
        $date_day2 = date('Y-m-d', strtotime('+3 days'));

        $insert_agenda->execute([$event_id_1, null, $date_day1, '09:00:00', '10:00:00', 'Opening Ceremony & Keynote: Autonomous AI Systems', 'Dr. Jane Speaker', 'An in-depth perspective on the next decade of agentic AI execution.']);
        $insert_agenda->execute([$event_id_1, null, $date_day1, '10:30:00', '12:00:00', 'Building Agentic Workflows', 'Alex Rivera', 'Deep dive into implementing autonomous AI agents in production systems.']);
        $insert_agenda->execute([$event_id_1, null, $date_day2, '10:00:00', '11:30:00', 'Panel: The Future of Web Development', 'Dr. Jane Speaker', 'Roundtable chat on server rendering and WebAssembly.']);

    } catch (PDOException $e) {
        $status_messages[] = ["danger", "Error setting up upgraded database schemas: " . $e->getMessage()];
        $success = false;
    }
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>RENENET EDUCARE - First Time Setup</title>
    <!-- Bootstrap 5 CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <!-- Bootstrap Icons -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.css" rel="stylesheet">
    <style>
        html { font-size: 16px; }
        @media (max-width: 576px) { html { font-size: 14px; } }
        body { font-size: calc(14px + 0.2vw); background-color: #f8f9fa; }
        .setup-card {
            border: none;
            border-radius: 12px;
            box-shadow: 0 8px 24px rgba(0,0,0,0.05);
            overflow: hidden;
        }
        .setup-header {
            background: linear-gradient(135deg, #0d6efd, #0dcaf0);
            padding: 2.5rem;
            color: white;
            text-align: center;
        }
        .log-container {
            max-height: 400px;
            overflow-y: auto;
            background: #f1f3f5;
            padding: 1rem;
            border-radius: 8px;
            border: 1px solid #e9ecef;
        }
    </style>
</head>
<body class="py-5">
    <div class="container-fluid px-3 px-md-5">
        <div class="row justify-content-center w-100 m-0">
            <div class="col-12 col-xl-8 col-lg-10 p-0">
                <div class="card setup-card">
                    <div class="setup-header">
                        <i class="bi bi-hdd-network-fill display-3 mb-3"></i>
                        <h2 class="fw-bold mb-0">System Installation & Setup</h2>
                        <p class="mb-0 mt-2 text-white-50">First-Time Database & Module Configuration</p>
                    </div>
                    <div class="card-body p-4 p-md-5">
                        <?php if (!$setup_attempted): ?>
                            <div class="text-center mb-5">
                                <h4 class="mb-3">Welcome to RENENET EDUCARE!</h4>
                                <p class="text-muted mb-4">
                                    Before we can start, we need to initialize the database, configure tables, and set up default accounts.
                                    This process will drop any existing tables in the database <strong><?php echo htmlspecialchars(DB_NAME); ?></strong> and create a fresh installation.
                                </p>
                                <form method="POST" action="">
                                    <button type="submit" name="run_setup" class="btn btn-primary btn-lg px-5 py-3 fw-bold rounded-pill shadow-sm">
                                        <i class="bi bi-play-circle-fill me-2"></i> Run Initial Setup
                                    </button>
                                </form>
                            </div>
                        <?php else: ?>
                            <div class="mb-4">
                                <h4 class="mb-3 d-flex align-items-center">
                                    <i class="bi bi-journal-text me-2 text-primary"></i> Setup Log
                                </h4>
                                <div class="log-container">
                                    <?php foreach ($status_messages as $msg): ?>
                                        <div class="alert alert-<?php echo $msg[0]; ?> d-flex align-items-center mb-2 py-2" role="alert">
                                            <?php if ($msg[0] == 'success'): ?>
                                                <i class="bi bi-check-circle-fill me-2"></i>
                                            <?php elseif ($msg[0] == 'danger'): ?>
                                                <i class="bi bi-exclamation-triangle-fill me-2"></i>
                                            <?php else: ?>
                                                <i class="bi bi-info-circle-fill me-2"></i>
                                            <?php endif; ?>
                                            <div><?php echo htmlspecialchars($msg[1]); ?></div>
                                        </div>
                                    <?php endforeach; ?>
                                </div>
                            </div>

                            <?php if ($success): ?>
                                <div class="alert alert-success py-4 border-0 bg-success text-white rounded-3">
                                    <h4 class="alert-heading fw-bold"><i class="bi bi-check-circle-fill me-2"></i> Database Configured Successfully!</h4>
                                    <p class="mb-0 text-white-50">The system has been fully configured and seeded. You can now log in using the testing credentials.</p>
                                </div>
                                <div class="d-grid gap-3 d-md-flex justify-content-md-center mt-4">
                                    <a href="login" class="btn btn-primary btn-lg px-5 rounded-pill shadow-sm">Go to Login Page <i class="bi bi-arrow-right ms-2"></i></a>
                                </div>
                            <?php else: ?>
                                <div class="alert alert-danger py-4 border-0 rounded-3">
                                    <h4 class="alert-heading fw-bold"><i class="bi bi-exclamation-triangle-fill me-2"></i> Setup Failed!</h4>
                                    <p class="mb-0">Please check your configuration in <code>config.php</code> and ensure PostgreSQL is running.</p>
                                </div>
                                <div class="d-grid gap-3 d-md-flex justify-content-md-center mt-4">
                                    <a href="setup" class="btn btn-danger btn-lg px-5 rounded-pill shadow-sm"><i class="bi bi-arrow-counterclockwise me-2"></i> Retry Setup</a>
                                </div>
                            <?php endif; ?>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>
    </div>
    
    <!-- Bootstrap JS Bundle with Popper -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
