<?php
// setup.php
session_start();
$step = isset($_GET['step']) ? (int)$_GET['step'] : 1;
$msg = '';
$error = '';

$configFile = 'config/database.php';

// --- Step 2 Handler: Test Connection & Save Config ---
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'save_config') {
    $host = $_POST['host'];
    $db = $_POST['db_name'];
    $user = $_POST['db_user'];
    $pass = $_POST['db_pass'];
    
    try {
        // Shared hosting usually requires connecting directly to a pre-created database
        $dsn = "mysql:host=$host;dbname=$db;charset=utf8mb4"; 
        $pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
        
        // If we got here, we are connected to the existing DB
        
        // Save Config File
        $content = "<?php\n// config/database.php\n\n";
        $content .= "\$host = '$host';\n";
        $content .= "\$db   = '$db';\n";
        $content .= "\$user = '$user';\n";
        $content .= "\$pass = '$pass';\n";
        $content .= "\$charset = 'utf8mb4';\n\n";
        $content .= "\$dsn = \"mysql:host=\$host;dbname=\$db;charset=\$charset\";\n";
        $content .= "\$options = [\n";
        $content .= "    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,\n";
        $content .= "    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,\n";
        $content .= "    PDO::ATTR_EMULATE_PREPARES   => false,\n";
        $content .= "];\n\n";
        $content .= "try {\n";
        $content .= "    \$pdo = new PDO(\$dsn, \$user, \$pass, \$options);\n";
        $content .= "} catch (\PDOException \$e) {\n";
        $content .= "    throw new \PDOException(\$e->getMessage(), (int)\$e->getCode());\n";
        $content .= "}\n?>";
        
        file_put_contents($configFile, $content);
        
        // Redirect to Step 3
        header("Location: setup.php?step=3");
        exit;
        
    } catch (PDOException $e) {
        $error = "Connection Failed: " . $e->getMessage();
    }
}

// --- Step 3 Handler: Install / Overwrite ---
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action']) && $_POST['action'] === 'install_db') {
    require_once $configFile;
    
    $mode = $_POST['install_mode']; // 'fresh' (Overwrite) or 'update'
    
    try {
        if ($mode === 'fresh') {
            // Force clean slate
            $pdo->exec("SET FOREIGN_KEY_CHECKS = 0");
            $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
            foreach($tables as $t) {
                $pdo->exec("DROP TABLE IF EXISTS `$t` ");
            }
            // Explicitly drop views too
            $pdo->exec("DROP VIEW IF EXISTS `member_attendance` ");
            $pdo->exec("SET FOREIGN_KEY_CHECKS = 1");
            
            $sqlMaster = <<<'SQL'
-- afmwmaln_wcsadmin.sql - Master Launch Schema (Consolidated V2.1)
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `calendar_events`, `radio_manual_logs`, `youtube_manual_logs`, `youtube_auto_logs`, `youtube_streams`, `stream_stats`, `settings`, `worship_reports`, `issues`, `attendance`, `roster_assignments`, `rosters`, `services`, `users`, `settings_options`, `members`;

CREATE TABLE `members` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `full_name` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `whatsapp` varchar(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `state_origin` varchar(100) DEFAULT NULL,
  `marital_status` varchar(20) DEFAULT NULL,
  `wedding_anniversary` date DEFAULT NULL,
  `next_of_kin` text DEFAULT NULL,
  `employment` text DEFAULT NULL,
  `education` text DEFAULT NULL,
  `referees` text DEFAULT NULL,
  `past_assignments` text DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'Available', -- Changed from ENUM to VARCHAR to prevent restore errors
  `unit` varchar(100) DEFAULT NULL,
  `unit_role` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `branch` varchar(100) DEFAULT NULL,
  `date_joined` date DEFAULT NULL,
  `experiences` longtext DEFAULT NULL,
  `category` varchar(50) DEFAULT 'Member',
  `is_approved` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` enum('admin','coordinator','roster_manager','member','viewer','roster_admin') NOT NULL DEFAULT 'viewer',
  `member_id` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `fk_user_member` (`member_id`),
  CONSTRAINT `fk_user_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `services` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` varchar(50) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `service_date` date NOT NULL,
  `service_time` time NOT NULL,
  `status` enum('Open','Closed') NOT NULL DEFAULT 'Open',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `rosters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `service_id` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('Draft','Approved','Closed') DEFAULT 'Draft',
  `approved_by` int(10) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `closed_by` int(10) UNSIGNED DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_roster_service` (`service_id`),
  CONSTRAINT `fk_roster_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `roster_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roster_id` int(11) NOT NULL,
  `role_name` varchar(100) NOT NULL,
  `member_id` int(10) UNSIGNED DEFAULT NULL,
  `person_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `roster_id` (`roster_id`),
  KEY `member_id` (`member_id`),
  CONSTRAINT `fk_roster_assign` FOREIGN KEY (`roster_id`) REFERENCES `rosters` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_assign_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `member_id` int(10) UNSIGNED NOT NULL,
  `in_time` datetime NOT NULL,
  `out_time` datetime DEFAULT NULL,
  `was_late` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `member_id` (`member_id`),
  CONSTRAINT `fk_attendance_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `issues` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` varchar(100) NOT NULL,
  `priority` enum('urgent','normal','low') NOT NULL DEFAULT 'normal',
  `department` varchar(100) NOT NULL,
  `unit` varchar(100) DEFAULT NULL,
  `location` varchar(100) DEFAULT NULL,
  `description` text NOT NULL,
  `description_summary` varchar(255) DEFAULT NULL,
  `reported_by` int(10) UNSIGNED DEFAULT NULL,
  `assigned_to` int(10) UNSIGNED DEFAULT NULL,
  `service_id` int(10) UNSIGNED DEFAULT NULL,
  `status` enum('Open','In Progress','Resolved','Closed') NOT NULL DEFAULT 'Open',
  `resolved_by` int(10) UNSIGNED DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `resolution_notes` text DEFAULT NULL,
  `date_reported` datetime DEFAULT current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_issue_reporter` (`reported_by`),
  KEY `fk_issue_assignee` (`assigned_to`),
  KEY `fk_issue_resolver` (`resolved_by`),
  KEY `fk_issue_service` (`service_id`),
  CONSTRAINT `fk_issue_reporter` FOREIGN KEY (`reported_by`) REFERENCES `members` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_issue_assignee` FOREIGN KEY (`assigned_to`) REFERENCES `members` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_issue_resolver` FOREIGN KEY (`resolved_by`) REFERENCES `members` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_issue_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `worship_reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `service_date` date NOT NULL,
  `ss_congregational_songs` text DEFAULT NULL,
  `ss_opening_prayer` varchar(255) DEFAULT NULL,
  `ss_distribution_to_classes` varchar(255) DEFAULT NULL,
  `ss_lesson_topic` varchar(255) DEFAULT NULL,
  `ss_internet_teacher` varchar(255) DEFAULT NULL,
  `ss_yoruba_teacher` varchar(255) DEFAULT NULL,
  `ss_french_teacher` varchar(255) DEFAULT NULL,
  `ss_song_after_lesson` varchar(255) DEFAULT NULL,
  `ss_reviewer` varchar(255) DEFAULT NULL,
  `ss_closing_song` varchar(255) DEFAULT NULL,
  `ss_closing_prayer` varchar(255) DEFAULT NULL,
  `ds_prelude` varchar(255) DEFAULT NULL,
  `ds_welcome_address` varchar(255) DEFAULT NULL,
  `ds_congregational_song` varchar(255) DEFAULT NULL,
  `ds_congregational_prayer` varchar(255) DEFAULT NULL,
  `ds_announcements` varchar(255) DEFAULT NULL,
  `ds_newcomers` varchar(255) DEFAULT NULL,
  `ds_baby_dedication` varchar(255) DEFAULT NULL,
  `ds_anthem` varchar(255) DEFAULT NULL,
  `ds_scripture_reading` varchar(255) DEFAULT NULL,
  `ds_bible_text` varchar(255) DEFAULT NULL,
  `ds_special_song` varchar(255) DEFAULT NULL,
  `ds_sermon_preacher` varchar(255) DEFAULT NULL,
  `ds_sermon_title` varchar(255) DEFAULT NULL,
  `ds_bible_references` text DEFAULT NULL,
  `ds_summary_prayer_points` text DEFAULT NULL,
  `ds_invitation_song` varchar(255) DEFAULT NULL,
  `ds_benediction` varchar(255) DEFAULT NULL,
  `ds_altar_prayer` varchar(255) DEFAULT NULL,
  `ys_welcome_address` varchar(255) DEFAULT NULL,
  `ys_prelude` varchar(255) DEFAULT NULL,
  `ys_congregational_songs` text DEFAULT NULL,
  `ys_opening_prayer` varchar(255) DEFAULT NULL,
  `ys_announcement` text DEFAULT NULL,
  `ys_group_testifying` varchar(255) DEFAULT NULL,
  `ys_testimonies_choruses` varchar(255) DEFAULT NULL,
  `ys_special_song` varchar(255) DEFAULT NULL,
  `ys_sermon_preacher` varchar(255) DEFAULT NULL,
  `ys_sermon_title` varchar(255) DEFAULT NULL,
  `ys_sermon_summary` text DEFAULT NULL,
  `ys_invitation_song` varchar(255) DEFAULT NULL,
  `ys_benediction` varchar(255) DEFAULT NULL,
  `ys_altar_prayer` varchar(255) DEFAULT NULL,
  `yps_prelude` varchar(255) DEFAULT NULL,
  `yps_songs_after_prelude` text DEFAULT NULL,
  `yps_congregational_songs` text DEFAULT NULL,
  `yps_opening_prayer` varchar(255) DEFAULT NULL,
  `yps_announcement` text DEFAULT NULL,
  `yps_songs_before_testimony` text DEFAULT NULL,
  `yps_choruses` varchar(255) DEFAULT NULL,
  `yps_testimonies_count` varchar(255) DEFAULT NULL,
  `yps_special_song` varchar(255) DEFAULT NULL,
  `yps_sermon_preacher` varchar(255) DEFAULT NULL,
  `yps_sermon_title` varchar(255) DEFAULT NULL,
  `yps_bible_references` text DEFAULT NULL,
  `yps_notes` text DEFAULT NULL,
  `yps_invitation_closing_song` varchar(255) DEFAULT NULL,
  `yps_benediction` varchar(255) DEFAULT NULL,
  `yps_altar_prayer` varchar(255) DEFAULT NULL,
  `att_morning_prayer_m` int(11) DEFAULT 0,
  `att_morning_prayer_f` int(11) DEFAULT 0,
  `att_morning_prayer_t` int(11) DEFAULT 0,
  `att_evening_prayer_m` int(11) DEFAULT 0,
  `att_evening_prayer_f` int(11) DEFAULT 0,
  `att_evening_prayer_t` int(11) DEFAULT 0,
  `att_sunday_school_m` int(11) DEFAULT 0,
  `att_sunday_school_f` int(11) DEFAULT 0,
  `att_sunday_school_t` int(11) DEFAULT 0,
  `att_children_service_m` int(11) DEFAULT 0,
  `att_children_service_f` int(11) DEFAULT 0,
  `att_children_service_t` int(11) DEFAULT 0,
  `att_devotional_m` int(11) DEFAULT 0,
  `att_devotional_f` int(11) DEFAULT 0,
  `att_devotional_t` int(11) DEFAULT 0,
  `att_revival_m` int(11) DEFAULT 0,
  `att_revival_f` int(11) DEFAULT 0,
  `att_revival_t` int(11) DEFAULT 0,
  `bless_salvation` int(11) DEFAULT 0,
  `bless_sanctification` int(11) DEFAULT 0,
  `bless_holy_ghost` int(11) DEFAULT 0,
  `bless_re_anointing` int(11) DEFAULT 0,
  `bless_healing` int(11) DEFAULT 0,
  `bless_others` int(11) DEFAULT 0,
  `bless_total` int(11) DEFAULT 0,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `settings_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(50) NOT NULL,
  `value` varchar(100) NOT NULL,
  `display_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings_options` (`category`, `value`, `display_order`) VALUES
('issue_types', 'Facility', 1), ('issue_types', 'Equipment', 2), ('issue_types', 'Personnel', 3), ('issue_types', 'Safety', 4), ('issue_types', 'Technical', 5),
('departments', 'Operations', 1), ('departments', 'Maintenance', 2), ('departments', 'Administration', 3), ('departments', 'IT/Media', 4),
('units', 'Ushering', 1), ('units', 'Media', 2), ('units', 'Protocol', 3), ('units', 'Choir', 4), ('units', 'Children', 5), ('units', 'Visitor', 6),
('unit_roles', 'Member', 1), ('unit_roles', 'Head', 2), ('unit_roles', 'Supervisor', 3), ('unit_roles', 'Intern', 4), ('unit_roles', 'Visitor', 5),
('branches', 'Main Branch', 1), ('branches', 'Branch A', 2), ('branches', 'Branch B', 3),
('member_departments', 'WCS', 1), ('member_departments', 'Music', 2), ('member_departments', 'Ushering', 3),
('locations', 'Main Sanctuary', 1), ('locations', 'Fellowship Hall', 2), ('locations', 'Children\'s Wing', 3), ('locations', 'Parking Lot', 4), ('locations', 'Office Area', 5), ('locations', 'Media Room', 6), ('locations', 'Kitchen', 7), ('locations', 'Restrooms', 8), ('locations', 'Outdoor Grounds', 9), ('locations', 'Other', 10);

CREATE TABLE `settings` (
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('youtube_api_key', ''),
('youtube_update_frequency', '5');

CREATE TABLE `youtube_streams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `video_id` varchar(100) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `last_status` enum('live','offline','error','checked') DEFAULT 'checked',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `youtube_auto_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) NOT NULL,
  `viewers` int(11) DEFAULT 0,
  `logged_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `stream_id` (`stream_id`),
  CONSTRAINT `fk_yl_stream` FOREIGN KEY (`stream_id`) REFERENCES `youtube_streams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `youtube_manual_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stream_id` int(11) NOT NULL,
  `peak_viewers` int(11) DEFAULT 0,
  `physical_attendance` int(11) DEFAULT 0,
  `service_date` date NOT NULL,
  `service_type` varchar(100) DEFAULT 'General',
  `description` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `stream_id` (`stream_id`),
  CONSTRAINT `fk_yml_stream` FOREIGN KEY (`stream_id`) REFERENCES `youtube_streams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `radio_manual_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `service_type` varchar(100) DEFAULT 'General',
  `listener_count` int(11) DEFAULT 0,
  `service_date` date NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `calendar_events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `event_date` date NOT NULL,
  `event_time` time DEFAULT NULL,
  `prayer_time` time DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `activity_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL,
  `action` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE VIEW `member_attendance` AS
SELECT m.id as member_id, m.full_name, m.unit, m.branch, s.service_date as event_date, CASE WHEN a.id IS NOT NULL AND a.was_late = 1 THEN 'late' WHEN a.id IS NOT NULL THEN 'present' WHEN m.status IN ('Traveled', 'Travelled', 'Gospel Errand', 'Not Available') THEN m.status ELSE 'absent' END as status FROM members m CROSS JOIN (SELECT DISTINCT service_date FROM services) s LEFT JOIN attendance a ON m.id = a.member_id AND DATE(a.in_time) = s.service_date WHERE (m.unit != 'Visitor' OR m.unit IS NULL);
SET FOREIGN_KEY_CHECKS = 1;
SQL;

            if ($sqlMaster) {
                $queries = preg_split('/;[\s]*[\r\n]+|;[\s]*$/', $sqlMaster);
                $exec_count = 0;
                $err_count = 0;
                foreach ($queries as $query) {
                    $query = trim($query);
                    if (empty($query)) continue;
                    
                    try {
                        $pdo->exec($query);
                        $exec_count++;
                    } catch (PDOException $e) {
                        $err_count++;
                        $msg .= " <small style='color:#777'>[Q#$exec_count Error: " . htmlspecialchars(substr($e->getMessage(), 0, 50)) . "...]</small> ";
                    }
                }
                $msg .= " <b>Success:</b> $exec_count queries. ";
                if ($err_count > 0) $msg .= " <b style='color:orange'>Warnings:</b> $err_count issues. ";
                
                // Seed Admin User
                $passHash = password_hash('password', PASSWORD_DEFAULT);
                $stmt = $pdo->prepare("INSERT INTO users (username, password_hash, role) VALUES ('admin', ?, 'admin')");
                try {
                    $stmt->execute([$passHash]);
                    $msg .= " Admin user created (admin/password). ";
                } catch (Exception $e) {
                    // Ignore if already exists (shouldn't happen in fresh mode but good practice)
                }
            }
        } else {
            // mode === 'update' - Robust Migration
            // 1. Repair worship_reports
            $checkCol = $pdo->query("SHOW COLUMNS FROM worship_reports LIKE 'ds_sermon_preacher'")->fetch();
            if (!$checkCol) {
                // Add missing V2 columns
                $pdo->exec("ALTER TABLE worship_reports 
                    ADD COLUMN `ds_sermon_preacher` VARCHAR(255) DEFAULT NULL AFTER `ds_special_song`,
                    ADD COLUMN `ds_sermon_title` VARCHAR(255) DEFAULT NULL AFTER `ds_sermon_preacher`,
                    ADD COLUMN `ss_lesson_topic` VARCHAR(255) DEFAULT NULL AFTER `ss_distribution_to_classes`,
                    ADD COLUMN `att_devotional_t` INT(11) DEFAULT 0 AFTER `att_devotional_f`
                ");
                $msg .= " Worship Reports upgraded. ";
            }

            // 2. Repair issues table
            $checkIssueCol = $pdo->query("SHOW COLUMNS FROM issues LIKE 'date_reported'")->fetch();
            if (!$checkIssueCol) {
                $pdo->exec("ALTER TABLE `issues` ADD COLUMN `date_reported` DATETIME DEFAULT CURRENT_TIMESTAMP AFTER `resolution_notes` ");
                $pdo->exec("UPDATE issues SET date_reported = created_at WHERE date_reported IS NULL");
                $msg .= " Issues table (date_reported) repaired. ";
            }
            $checkDescCol = $pdo->query("SHOW COLUMNS FROM issues LIKE 'description_summary'")->fetch();
            if (!$checkDescCol) {
                $pdo->exec("ALTER TABLE `issues` ADD COLUMN `description_summary` VARCHAR(255) DEFAULT NULL AFTER `description` ");
                $msg .= " Issues table (summary) repaired. ";
            }
            
            // 2b. Repair members table (Change enum to varchar to avoid truncation errors)
            try {
                // We blindly change it to VARCHAR(50) to allow more flexibility and empty strings during restore
                $pdo->exec("ALTER TABLE members MODIFY `status` VARCHAR(50) NOT NULL DEFAULT 'Available'");
                $msg .= " Members table status upgraded. ";
            } catch (Exception $e) { /* Ignore */ }

            // 3. Add YouTube Tracking Tables
            $pdo->exec("CREATE TABLE IF NOT EXISTS `settings` (
                `setting_key` varchar(100) NOT NULL,
                `setting_value` text DEFAULT NULL,
                `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
                PRIMARY KEY (`setting_key`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

            // Seed settings if empty
            $checkSettings = $pdo->query("SELECT COUNT(*) FROM settings")->fetchColumn();
            if ($checkSettings == 0) {
                 $pdo->exec("INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES ('youtube_api_key', ''), ('youtube_video_id', ''), ('youtube_update_frequency', '5')");
            }

            $pdo->exec("CREATE TABLE IF NOT EXISTS `stream_stats` (
                `video_id` varchar(50) NOT NULL,
                `current_viewers` int(11) DEFAULT 0,
                `peak_viewers` int(11) DEFAULT 0,
                `last_updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
                PRIMARY KEY (`video_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

            // 3b. Add Expanded Streaming Tables (V2)
            $pdo->exec("CREATE TABLE IF NOT EXISTS `youtube_streams` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `name` varchar(100) NOT NULL,
                `video_id` varchar(100) NOT NULL,
                `is_active` tinyint(1) DEFAULT 1,
                `last_status` enum('live','offline','error','checked') DEFAULT 'checked',
                `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

            $pdo->exec("CREATE TABLE IF NOT EXISTS `youtube_auto_logs` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `stream_id` int(11) NOT NULL,
                `viewers` int(11) DEFAULT 0,
                `logged_at` timestamp NOT NULL DEFAULT current_timestamp(),
                PRIMARY KEY (`id`),
                KEY `stream_id` (`stream_id`),
                CONSTRAINT `fk_yl_stream` FOREIGN KEY (`stream_id`) REFERENCES `youtube_streams` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

            $pdo->exec("CREATE TABLE IF NOT EXISTS `youtube_manual_logs` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `stream_id` int(11) NOT NULL,
                `peak_viewers` int(11) DEFAULT 0,
                `physical_attendance` int(11) DEFAULT 0,
                `service_date` date NOT NULL,
                `service_type` varchar(100) DEFAULT 'General',
                `description` varchar(255) DEFAULT NULL,
                `notes` text DEFAULT NULL,
                `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
                PRIMARY KEY (`id`),
                KEY `stream_id` (`stream_id`),
                CONSTRAINT `fk_yml_stream` FOREIGN KEY (`stream_id`) REFERENCES `youtube_streams` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

            $pdo->exec("CREATE TABLE IF NOT EXISTS `radio_manual_logs` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `service_type` varchar(100) DEFAULT 'General',
                `listener_count` int(11) DEFAULT 0,
                `service_date` date NOT NULL,
                `notes` text DEFAULT NULL,
                `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

            // 4. Add Calendar Events Table
            $pdo->exec("CREATE TABLE IF NOT EXISTS `calendar_events` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `title` varchar(255) NOT NULL,
                `event_date` date NOT NULL,
                `event_time` time DEFAULT NULL,
                `prayer_time` time DEFAULT NULL,
                `description` text DEFAULT NULL,
                `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            
            $msg .= " Calendar tables verified. ";

            // 4b. Repair youtube_manual_logs for missing columns
            try {
                $checkYtCol = $pdo->query("SHOW COLUMNS FROM youtube_manual_logs LIKE 'service_type'")->fetch();
                if (!$checkYtCol) {
                    $pdo->exec("ALTER TABLE youtube_manual_logs 
                        ADD COLUMN `service_type` VARCHAR(100) DEFAULT 'General' AFTER `service_date`,
                        ADD COLUMN `description` VARCHAR(255) DEFAULT NULL AFTER `service_type`
                    ");
                    $msg .= " Streaming Logs (service_type) upgraded. ";
                }
                
                $checkYtPhy = $pdo->query("SHOW COLUMNS FROM youtube_manual_logs LIKE 'physical_attendance'")->fetch();
                if (!$checkYtPhy) {
                    $pdo->exec("ALTER TABLE youtube_manual_logs ADD COLUMN `physical_attendance` INT(11) DEFAULT 0 AFTER `peak_viewers`");
                    $msg .= " Streaming Logs (physical_attendance) upgraded. ";
                }
            } catch (Exception $e) {}

            // 4c. Repair radio_manual_logs for missing columns
            try {
                $checkRadio = $pdo->query("SHOW COLUMNS FROM radio_manual_logs LIKE 'service_type'")->fetch();
                if (!$checkRadio) {
                    try { $pdo->exec("ALTER TABLE radio_manual_logs ADD COLUMN `id` INT AUTO_INCREMENT PRIMARY KEY FIRST"); } catch(Exception $e){}
                    $pdo->exec("ALTER TABLE radio_manual_logs ADD COLUMN `service_type` VARCHAR(100) DEFAULT 'General' AFTER `id`");
                    $msg .= " Radio Logs upgraded. ";
                }
            } catch (Exception $e) {}

            // 5. Add Activity Logs Table
            $pdo->exec("CREATE TABLE IF NOT EXISTS `activity_logs` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `user_id` int(11) DEFAULT NULL,
                `username` varchar(100) DEFAULT NULL,
                `action` varchar(50) NOT NULL,
                `description` text DEFAULT NULL,
                `ip_address` varchar(45) DEFAULT NULL,
                `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
                PRIMARY KEY (`id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            $msg .= " Activity Logs table checked. ";

            // 5. Recreate views (Renumbered)
            $pdo->exec("DROP VIEW IF EXISTS `member_attendance` ");
            $viewSql = "CREATE VIEW `member_attendance` AS SELECT m.id as member_id, m.full_name, m.unit, m.branch, s.service_date as event_date, CASE WHEN a.id IS NOT NULL AND a.was_late = 1 THEN 'late' WHEN a.id IS NOT NULL THEN 'present' WHEN m.status IN ('Traveled', 'Travelled', 'Gospel Errand', 'Not Available') THEN m.status ELSE 'absent' END as status FROM members m CROSS JOIN (SELECT DISTINCT service_date FROM services) s LEFT JOIN attendance a ON m.id = a.member_id AND DATE(a.in_time) = s.service_date WHERE (m.unit != 'Visitor' OR m.unit IS NULL)";
            $pdo->exec($viewSql);
            $msg .= " Views refreshed. ";

            // 6. Ensure Admin User Exists (Restoration/Fix)
            $checkAdmin = $pdo->query("SELECT COUNT(*) FROM users WHERE username = 'admin'")->fetchColumn();
            if ($checkAdmin == 0) {
                $passHash = password_hash('password', PASSWORD_DEFAULT);
                $stmt = $pdo->prepare("INSERT INTO users (username, password_hash, role) VALUES ('admin', ?, 'admin')");
                $stmt->execute([$passHash]);
                $msg .= " Admin account restored (admin/password). ";
            }

            // 7. Add Category Column (V2.2)
            try {
                $checkCat = $pdo->query("SHOW COLUMNS FROM members LIKE 'category'")->fetch();
                if (!$checkCat) {
                    $pdo->exec("ALTER TABLE members ADD COLUMN `category` VARCHAR(50) DEFAULT 'Member' AFTER `status`");
                    $msg .= " Members (category) upgraded. ";
                }
            } catch (Exception $e) {}

            // 8. Add Roster Assignments member_id (V2.2)
            try {
                $checkMemId = $pdo->query("SHOW COLUMNS FROM roster_assignments LIKE 'member_id'")->fetch();
                if (!$checkMemId) {
                    $pdo->exec("ALTER TABLE roster_assignments ADD COLUMN `member_id` INT(10) UNSIGNED DEFAULT NULL AFTER `role_name`");
                    $pdo->exec("ALTER TABLE roster_assignments ADD CONSTRAINT `fk_assign_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE SET NULL");
                    $msg .= " Roster Logic (member_id) upgraded. ";
                }
            } catch (Exception $e) {}
            
            // 8b. Add created_at to Members (V2.3 Fix)
            try {
                $checkCreated = $pdo->query("SHOW COLUMNS FROM members LIKE 'created_at'")->fetch();
                if (!$checkCreated) {
                    $pdo->exec("ALTER TABLE members ADD COLUMN `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP");
                    $msg .= " Members (created_at) added. ";
                }
            } catch (Exception $e) {}

            // 9. Add Roster Closed Status (V2.2)
            try {
                 // Check if 'Closed' is in the enum list for status
                 $row = $pdo->query("SHOW COLUMNS FROM rosters LIKE 'status'")->fetch(PDO::FETCH_ASSOC);
                 if (strpos($row['Type'], "'Closed'") === false) {
                     $pdo->exec("ALTER TABLE rosters MODIFY COLUMN `status` ENUM('Draft','Approved','Closed') DEFAULT 'Draft'");
                     $pdo->exec("ALTER TABLE rosters ADD COLUMN `closed_by` INT(10) UNSIGNED DEFAULT NULL AFTER `approved_at`");
                     $pdo->exec("ALTER TABLE rosters ADD COLUMN `closed_at` DATETIME DEFAULT NULL AFTER `closed_by`");
                     $msg .= " Roster Status (Closed) upgraded. ";
                 }
            } catch (Exception $e) {}

            // 10. Add Member Approval Status (V2.3)
            try {
                $checkApp = $pdo->query("SHOW COLUMNS FROM members LIKE 'is_approved'")->fetch();
                if (!$checkApp) {
                    $pdo->exec("ALTER TABLE members ADD COLUMN `is_approved` TINYINT(1) DEFAULT 1 AFTER `category`");
                    $msg .= " Members (approval) upgraded. ";
                }
            } catch (Exception $e) {}

            // 11. Add Admin Backup Email Setting (V2.5)
            try {
                $checkBackup = $pdo->query("SELECT id FROM settings_options WHERE category='admin_backup_email'")->fetch();
                if (!$checkBackup) {
                    $pdo->exec("INSERT INTO settings_options (category, value, display_order, is_active) VALUES ('admin_backup_email', '', 1, 1)");
                    $msg .= " Admin Backup Email setting added. ";
                }
            } catch (Exception $e) {}

            // 12. Add SMTP Settings (V2.5.1)
            try {
                $smtpKeys = [
                    'smtp_host' => 'mail.example.com',
                    'smtp_user' => 'admin@example.com', 
                    'smtp_pass' => '',
                    'smtp_port' => '587',
                    'smtp_from_email' => 'admin@example.com',
                    'smtp_from_name' => 'WCS Admin'
                ];
                foreach ($smtpKeys as $key => $default) {
                    $check = $pdo->query("SELECT id FROM settings_options WHERE category='$key'")->fetch();
                    if (!$check) {
                        $pdo->prepare("INSERT INTO settings_options (category, value, display_order, is_active) VALUES (?, ?, 1, 1)")
                            ->execute([$key, $default]);
                    }
                }
                $msg .= " SMTP Settings added. ";
            } catch (Exception $e) {}

            // 10b. Add Extended Member Fields (V2.4 - Form Update)
            try {
                $checkDOB = $pdo->query("SHOW COLUMNS FROM members LIKE 'dob'")->fetch();
                if (!$checkDOB) {
                    $pdo->exec("ALTER TABLE members 
                        ADD COLUMN `whatsapp` VARCHAR(20) DEFAULT NULL AFTER `phone`,
                        ADD COLUMN `dob` DATE DEFAULT NULL AFTER `whatsapp`,
                        ADD COLUMN `gender` VARCHAR(10) DEFAULT NULL AFTER `dob`,
                        ADD COLUMN `state_origin` VARCHAR(100) DEFAULT NULL AFTER `gender`,
                        ADD COLUMN `marital_status` VARCHAR(20) DEFAULT NULL AFTER `state_origin`,
                        ADD COLUMN `wedding_anniversary` DATE DEFAULT NULL AFTER `marital_status`,
                        ADD COLUMN `next_of_kin` TEXT DEFAULT NULL AFTER `wedding_anniversary`,
                        ADD COLUMN `employment` TEXT DEFAULT NULL AFTER `next_of_kin`,
                        ADD COLUMN `education` TEXT DEFAULT NULL AFTER `employment`,
                        ADD COLUMN `referees` TEXT DEFAULT NULL AFTER `education`,
                        ADD COLUMN `past_assignments` TEXT DEFAULT NULL AFTER `referees`
                    ");
                    $msg .= " Members (Extended Fields) upgraded. ";
                }
            } catch (Exception $e) {}

            // 11. Add Access Tokens Table (V2.3)
            $pdo->exec("CREATE TABLE IF NOT EXISTS `access_tokens` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `token` varchar(64) NOT NULL,
                `type` enum('registration','update') NOT NULL,
                `member_id` int(10) UNSIGNED DEFAULT NULL,
                `created_by` int(10) UNSIGNED DEFAULT NULL,
                `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
                `expires_at` datetime NOT NULL,
                `is_used` tinyint(1) DEFAULT 0,
                PRIMARY KEY (`id`),
                UNIQUE KEY `token` (`token`),
                KEY `fk_token_member` (`member_id`),
                CONSTRAINT `fk_token_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            $msg .= " Access Tokens table checked. ";
        }
        
        $msg = "Database " . ($mode === 'fresh' ? 'Installed' : 'Updated') . " successfully. " . $msg;
        
        // Redirect to Finish
        header("Location: setup.php?step=4&msg=" . urlencode($msg));
        exit;
        
    } catch (Exception $e) {
        $error = "Installation Failed: " . $e->getMessage();
    }
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>WCS Admin Setup</title>
    <link rel="stylesheet" href="assets/css/style.css">
    <style>
        body { display: flex; justify-content: center; align-items: center; min-height: 100vh; background: #111; }
        .setup-card { width: 100%; max-width: 500px; background: #222; padding: 30px; border-radius: 8px; box-shadow: 0 4px 10px rgba(0,0,0,0.5); }
        .step-indicator { display: flex; justify-content: space-between; margin-bottom: 20px; border-bottom: 1px solid #444; padding-bottom: 10px; }
        .step { color: #555; font-weight: bold; }
        .step.active { color: var(--primary-color); }
        .step.completed { color: var(--success-color); }
    </style>
</head>
<body>

<div class="setup-card">
    <div class="step-indicator">
        <span class="step <?php echo $step >= 1 ? 'active' : ''; ?> <?php echo $step > 1 ? 'completed' : ''; ?>">1. Start</span>
        <span class="step <?php echo $step >= 2 ? 'active' : ''; ?> <?php echo $step > 2 ? 'completed' : ''; ?>">2. Config</span>
        <span class="step <?php echo $step >= 3 ? 'active' : ''; ?> <?php echo $step > 3 ? 'completed' : ''; ?>">3. Database</span>
        <span class="step <?php echo $step >= 4 ? 'active' : ''; ?>">4. Finish</span>
    </div>

    <h1>WCS Admin Setup</h1>
    
    <?php if ($error): ?>
        <div class="alert alert-danger"><?php echo $error; ?></div>
    <?php endif; ?>

    <!-- Step 1: Welcome -->
    <?php if ($step === 1): ?>
        <p>Welcome to the WCS Admin Installer.</p>
        <p>This wizard will help you configure your database connection and install the necessary tables.</p>
        
        <h3>System Check:</h3>
        <ul>
            <li>PHP Version: <?php echo phpversion(); ?> (Required: 7.4+)</li>
            <li>PDO Extension: <?php echo extension_loaded('pdo') ? '<span style="color:var(--success-color)">Installed</span>' : '<span style="color:var(--danger-color)">Missing</span>'; ?></li>
            <li>Write Permission (config/): <?php echo is_writable('config') ? '<span style="color:var(--success-color)">Writable</span>' : '<span style="color:var(--danger-color)">Not Writable</span>'; ?></li>
        </ul>
        
        <div style="margin-top: 20px;">
            <a href="setup.php?step=2" class="btn btn-block">Start Setup</a>
        </div>
    <?php endif; ?>

    <!-- Step 2: DB Config -->
    <?php if ($step === 2): ?>
        <form method="POST">
            <input type="hidden" name="action" value="save_config">
            <div class="form-group">
                <label>Database Host</label>
                <input type="text" name="host" class="form-control" value="localhost" required>
            </div>
            <div class="form-group">
                <label>Database Name</label>
                <input type="text" name="db_name" class="form-control" value="afmwmaln_wcsadmin" required>
            </div>
            <div class="form-group">
                <label>Database User</label>
                <input type="text" name="db_user" class="form-control" value="root" required>
            </div>
            <div class="form-group">
                <label>Database Password</label>
                <input type="password" name="db_pass" class="form-control" placeholder="">
            </div>
            <button type="submit" class="btn btn-block">Test Connection & Save</button>
        </form>
    <?php endif; ?>

    <!-- Step 3: DB Install -->
    <?php if ($step === 3): ?>
        <p class="alert alert-success">Configuration saved successfully!</p>
        <form method="POST">
            <input type="hidden" name="action" value="install_db">
            
            <div class="card" style="margin-bottom: 20px; border: 1px solid #444;">
                <label style="display: flex; gap: 10px; align-items: flex-start; cursor: pointer;">
                    <input type="radio" name="install_mode" value="fresh" checked style="margin-top: 5px;">
                    <div>
                        <strong>Fresh Install (Overwrite)</strong>
                        <p style="font-size: 0.9rem; color: #888;">Wipes existing data. Installs standard tables and sample data.</p>
                    </div>
                </label>
            </div>
            
            <div class="card" style="margin-bottom: 20px; border: 1px solid #444;">
                <label style="display: flex; gap: 10px; align-items: flex-start; cursor: pointer;">
                    <input type="radio" name="install_mode" value="update" style="margin-top: 5px;">
                    <div>
                        <strong>Update Only</strong>
                        <p style="font-size: 0.9rem; color: #888;">Keeps existing data. Only adds missing tables (Services, Issues).</p>
                    </div>
                </label>
            </div>
            
            <button type="submit" class="btn btn-block" style="background-color: var(--accent-color); color: #000;">Proceed</button>
        </form>
    <?php endif; ?>

    <!-- Step 4: Finish -->
    <?php if ($step === 4): ?>
        <div style="text-align: center;">
            <div style="font-size: 4rem; color: var(--success-color); margin-bottom: 10px;"><i class="fas fa-check-circle"></i></div>
            <h2>Setup Complete!</h2>
            <p><?php echo isset($_GET['msg']) ? htmlspecialchars($_GET['msg']) : ''; ?></p>
            <div style="margin-top: 30px;">
                <a href="index.php" class="btn btn-block">Go to Login</a>
                <a href="pages/user_guide.php" class="btn btn-block" style="background: #444; margin-top: 10px;">View User Guide</a>
            </div>
        </div>
    <?php endif; ?>

</div>

</body>
</html>
