<?php
// api/restore.php
session_start();
require_once '../config/database.php';
require_once '../includes/functions.php';

if (!hasRole('admin')) {
    redirect('../index.php?page=login');
}

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['backup_file'])) {
    $file = $_FILES['backup_file'];
    
    if ($file['error'] !== UPLOAD_ERR_OK) {
        redirect('../index.php?page=settings&error=Upload failed');
    }
    
    $ext = pathinfo($file['name'], PATHINFO_EXTENSION);
    if (strtolower($ext) !== 'sql') {
         redirect('../index.php?page=settings&error=Invalid file type. Only .sql allowed.');
    }
    
    // Read and Execute
    // Note: Parsing SQL Dump by semicolon is risky if data contains semicolons. 
    // Ideally use mysql command line, but PHP fallback:
    
    $sqlContent = file_get_contents($file['tmp_name']);
    
    // Basic split by line to remove comments, then reconstruct
    // This is simple/naive implementing. For production with huge data, use `mysql` cli.
    // Given the environment constraints, we try PDO->exec with the whole blob if possible, or split statements.
    
    // --- SANITIZE SQL ---
    // Remove DEFINER clauses (they cause failures in new environments with different DB users)
    // Format: DEFINER=`user`@`host` or DEFINER=user@host
    $sqlContent = preg_replace('/DEFINER\s*=\s*`[^`]+`@`[^`]+`/', '', $sqlContent);
    $sqlContent = preg_replace('/DEFINER\s*=\s*[^\s@]+@[^\s\s]+/', '', $sqlContent);

    // FIX: Convert 'status' ENUM to VARCHAR(50) to prevent "Data truncated" error on empty values
    // Matches: `status` enum(...) ...
    $sqlContent = preg_replace('/`status`\s+enum\([^)]+\)/i', '`status` varchar(50)', $sqlContent);


    // Build queries one by one
    $queries = [];
    $tempQuery = '';
    
    // Normalize line endings to \n
    $sqlContent = str_replace(["\r\n", "\r"], "\n", $sqlContent);
    $lines = explode("\n", $sqlContent);
    
    foreach ($lines as $line) {
        $trimmedLine = trim($line);
        // Skip comments and empty lines
        if ($trimmedLine === '' || strpos($trimmedLine, '--') === 0 || strpos($trimmedLine, '/*') === 0 || strpos($trimmedLine, '#') === 0) {
            continue;
        }

        $tempQuery .= $line . " "; // Add space instead of newline to combine multi-line statements safely

        if (substr($trimmedLine, -1) === ';') {
            // Special Case: Filter out INSERT statements for member_attendance (it's a view)
            if (stripos($tempQuery, 'INSERT INTO `member_attendance`') === false && 
                stripos($tempQuery, 'INSERT INTO member_attendance') === false) {
                $queries[] = trim($tempQuery);
            }
            $tempQuery = '';
        }
    }

    try {
        // Disable FK checks
        $pdo->exec("SET FOREIGN_KEY_CHECKS = 0");
        $pdo->exec("SET names utf8mb4");
        
        // Aggressively cleanup known tables/views
        $parents = ['members', 'services', 'users', 'settings_options'];
        $children = ['worship_reports', 'issues', 'attendance', 'roster_assignments', 'rosters'];
        $views = ['member_attendance'];
        
        foreach ($views as $v) {
            $pdo->exec("DROP VIEW IF EXISTS `$v` ");
            $pdo->exec("DROP TABLE IF EXISTS `$v` ");
        }
        foreach ($children as $t) $pdo->exec("DROP TABLE IF EXISTS `$t` ");
        foreach ($parents as $t) $pdo->exec("DROP TABLE IF EXISTS `$t` ");
        
        // Execute each valid statement
        $successCount = 0;
        foreach ($queries as $q) {
            if (empty($q)) continue;
            try {
                $pdo->exec($q);
                $successCount++;
            } catch (Exception $e) {
                // Log the specific failed query but continue if it's non-critical? 
                // No, better to fail and tell the user, but for Views we might want to skip.
                if (stripos($q, 'CREATE VIEW') !== false || stripos($q, 'CREATE ALGORITHM') !== false) {
                    // If view creation fails, we can often still use the app
                    continue; 
                }
                throw new Exception("Error at query " . ($successCount + 1) . ": " . $e->getMessage() . " | Query: " . substr($q, 0, 100) . "...");
            }
        }
        
        // --- POST-RESTORE UPGRADE (V2 PROTECTION) ---
        // Handles old V1 backups restored into V2 app
        try {
            // 1. Check if we need to upgrade
            $checkCol = $pdo->query("SHOW COLUMNS FROM worship_reports LIKE 'ds_sermon_preacher'")->fetch();
            if (!$checkCol) {
                // Table is V1, apply V2 Upgrade logic
                // First, check if V1 columns exist so we can migrate data
                $v1Cols = $pdo->query("SHOW COLUMNS FROM worship_reports")->fetchAll(PDO::FETCH_COLUMN);
                
                // Add all V2 columns
                $pdo->exec("ALTER TABLE `worship_reports` 
                    ADD COLUMN `ss_congregational_songs` TEXT AFTER `service_date`,
                    ADD COLUMN `ss_opening_prayer` VARCHAR(255) AFTER `ss_congregational_songs`,
                    ADD COLUMN `ss_distribution_to_classes` VARCHAR(255) AFTER `ss_opening_prayer`,
                    ADD COLUMN `ss_lesson_topic` VARCHAR(255) AFTER `ss_distribution_to_classes`,
                    ADD COLUMN `ss_internet_teacher` VARCHAR(255) AFTER `ss_lesson_topic`,
                    ADD COLUMN `ss_yoruba_teacher` VARCHAR(255) AFTER `ss_internet_teacher`,
                    ADD COLUMN `ss_french_teacher` VARCHAR(255) AFTER `ss_yoruba_teacher`,
                    ADD COLUMN `ss_song_after_lesson` VARCHAR(255) AFTER `ss_french_teacher`,
                    ADD COLUMN `ss_reviewer` VARCHAR(255) AFTER `ss_song_after_lesson`,
                    ADD COLUMN `ss_closing_song` VARCHAR(255) AFTER `ss_reviewer`,
                    ADD COLUMN `ss_closing_prayer` VARCHAR(255) AFTER `ss_closing_song`,
                    ADD COLUMN `ds_prelude` VARCHAR(255) AFTER `ss_closing_prayer`,
                    ADD COLUMN `ds_welcome_address` VARCHAR(255) AFTER `ds_prelude`,
                    ADD COLUMN `ds_congregational_song` VARCHAR(255) AFTER `ds_welcome_address`,
                    ADD COLUMN `ds_congregational_prayer` VARCHAR(255) AFTER `ds_congregational_song`,
                    ADD COLUMN `ds_announcements` VARCHAR(255) AFTER `ds_congregational_prayer`,
                    ADD COLUMN `ds_newcomers` VARCHAR(255) AFTER `ds_announcements`,
                    ADD COLUMN `ds_baby_dedication` VARCHAR(255) AFTER `ds_newcomers`,
                    ADD COLUMN `ds_anthem` VARCHAR(255) AFTER `ds_baby_dedication`,
                    ADD COLUMN `ds_scripture_reading` VARCHAR(255) AFTER `ds_anthem`,
                    ADD COLUMN `ds_bible_text` VARCHAR(255) AFTER `ds_scripture_reading`,
                    ADD COLUMN `ds_special_song` VARCHAR(255) AFTER `ds_bible_text`,
                    ADD COLUMN `ds_sermon_preacher` VARCHAR(255) AFTER `ds_special_song`,
                    ADD COLUMN `ds_sermon_title` VARCHAR(255) AFTER `ds_sermon_preacher`,
                    ADD COLUMN `ds_bible_references` TEXT AFTER `ds_sermon_title`,
                    ADD COLUMN `ds_summary_prayer_points` TEXT AFTER `ds_bible_references`,
                    ADD COLUMN `ds_invitation_song` VARCHAR(255) AFTER `ds_summary_prayer_points`,
                    ADD COLUMN `ds_benediction` VARCHAR(255) AFTER `ds_invitation_song`,
                    ADD COLUMN `ds_altar_prayer` VARCHAR(255) AFTER `ds_benediction`,
                    ADD COLUMN `ys_welcome_address` VARCHAR(255) AFTER `ds_altar_prayer`,
                    ADD COLUMN `ys_prelude` VARCHAR(255) AFTER `ys_welcome_address`,
                    ADD COLUMN `ys_congregational_songs` TEXT AFTER `ys_prelude`,
                    ADD COLUMN `ys_opening_prayer` VARCHAR(255) AFTER `ys_congregational_songs`,
                    ADD COLUMN `ys_announcement` TEXT AFTER `ys_opening_prayer`,
                    ADD COLUMN `ys_group_testifying` VARCHAR(255) AFTER `ys_announcement`,
                    ADD COLUMN `ys_testimonies_choruses` VARCHAR(255) AFTER `ys_group_testifying`,
                    ADD COLUMN `ys_special_song` VARCHAR(255) AFTER `ys_testimonies_choruses`,
                    ADD COLUMN `ys_sermon_preacher` VARCHAR(255) AFTER `ys_special_song`,
                    ADD COLUMN `ys_sermon_title` VARCHAR(255) AFTER `ys_sermon_preacher`,
                    ADD COLUMN `ys_sermon_summary` TEXT AFTER `ys_sermon_title`,
                    ADD COLUMN `ys_invitation_song` VARCHAR(255) AFTER `ys_sermon_summary`,
                    ADD COLUMN `ys_benediction` VARCHAR(255) AFTER `ys_invitation_song`,
                    ADD COLUMN `ys_altar_prayer` VARCHAR(255) AFTER `ys_benediction`,
                    ADD COLUMN `yps_prelude` VARCHAR(255) AFTER `ys_altar_prayer`,
                    ADD COLUMN `yps_songs_after_prelude` TEXT AFTER `yps_prelude`,
                    ADD COLUMN `yps_congregational_songs` TEXT AFTER `yps_songs_after_prelude`,
                    ADD COLUMN `yps_opening_prayer` VARCHAR(255) AFTER `yps_congregational_songs`,
                    ADD COLUMN `yps_announcement` TEXT AFTER `yps_opening_prayer`,
                    ADD COLUMN `yps_songs_before_testimony` TEXT AFTER `yps_announcement`,
                    ADD COLUMN `yps_choruses` VARCHAR(255) AFTER `yps_songs_before_testimony`,
                    ADD COLUMN `yps_testimonies_count` VARCHAR(255) AFTER `yps_choruses`,
                    ADD COLUMN `yps_special_song` VARCHAR(255) AFTER `yps_testimonies_count`,
                    ADD COLUMN `yps_sermon_preacher` VARCHAR(255) AFTER `yps_special_song`,
                    ADD COLUMN `yps_sermon_title` VARCHAR(255) AFTER `yps_sermon_preacher`,
                    ADD COLUMN `yps_bible_references` TEXT AFTER `yps_sermon_title`,
                    ADD COLUMN `yps_notes` TEXT AFTER `yps_bible_references`,
                    ADD COLUMN `yps_invitation_closing_song` VARCHAR(255) AFTER `yps_notes`,
                    ADD COLUMN `yps_benediction` VARCHAR(255) AFTER `yps_invitation_closing_song`,
                    ADD COLUMN `yps_altar_prayer` VARCHAR(255) AFTER `yps_benediction`,
                    ADD COLUMN `att_morning_prayer_m` INT DEFAULT 0,
                    ADD COLUMN `att_morning_prayer_f` INT DEFAULT 0,
                    ADD COLUMN `att_morning_prayer_t` INT DEFAULT 0,
                    ADD COLUMN `att_evening_prayer_m` INT DEFAULT 0,
                    ADD COLUMN `att_evening_prayer_f` INT DEFAULT 0,
                    ADD COLUMN `att_evening_prayer_t` INT DEFAULT 0,
                    ADD COLUMN `att_sunday_school_m` INT DEFAULT 0,
                    ADD COLUMN `att_sunday_school_f` INT DEFAULT 0,
                    ADD COLUMN `att_sunday_school_t` INT DEFAULT 0,
                    ADD COLUMN `att_children_service_m` INT DEFAULT 0,
                    ADD COLUMN `att_children_service_f` INT DEFAULT 0,
                    ADD COLUMN `att_children_service_t` INT DEFAULT 0,
                    ADD COLUMN `att_devotional_m` INT DEFAULT 0,
                    ADD COLUMN `att_devotional_f` INT DEFAULT 0,
                    ADD COLUMN `att_devotional_t` INT DEFAULT 0,
                    ADD COLUMN `att_revival_m` INT DEFAULT 0,
                    ADD COLUMN `att_revival_f` INT DEFAULT 0,
                    ADD COLUMN `att_revival_t` INT DEFAULT 0,
                    ADD COLUMN `bless_salvation` INT DEFAULT 0,
                    ADD COLUMN `bless_sanctification` INT DEFAULT 0,
                    ADD COLUMN `bless_holy_ghost` INT DEFAULT 0,
                    ADD COLUMN `bless_re_anointing` INT DEFAULT 0,
                    ADD COLUMN `bless_healing` INT DEFAULT 0,
                    ADD COLUMN `bless_others` INT DEFAULT 0,
                    ADD COLUMN `bless_total` INT DEFAULT 0
                ");

                // Migrate existing V1 data to V2 fields
                if (in_array('preacher', $v1Cols)) {
                    $pdo->exec("UPDATE worship_reports SET ds_sermon_preacher = preacher WHERE ds_sermon_preacher IS NULL OR ds_sermon_preacher = ''");
                }
                if (in_array('sermon_topic', $v1Cols)) {
                    $pdo->exec("UPDATE worship_reports SET ds_sermon_title = sermon_topic WHERE ds_sermon_title IS NULL OR ds_sermon_title = ''");
                }
                if (in_array('attendance_total', $v1Cols)) {
                    $pdo->exec("UPDATE worship_reports SET att_devotional_t = attendance_total WHERE att_devotional_t = 0");
                }
            }
        } catch (Exception $e) { /* Ignore upgrade errors */ }

        // 1.5 Repair issues table if missing date_reported
        try {
            $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");
            }
            
            $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` ");
            }
        } catch (Exception $e) { /* Ignore */ }

        // 2. Explicitly recreate/restore the member_attendance view
        try {
            $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);
        } catch (Exception $e) { /* Ignore view errors */ }

        $pdo->exec("SET FOREIGN_KEY_CHECKS = 1");
        
        redirect('../index.php?page=settings&msg=Database restored and upgraded to V2 successfully.');
    } catch (Exception $e) {
        $pdo->exec("SET FOREIGN_KEY_CHECKS = 1"); 
        redirect('../index.php?page=settings&error=Restore failed: ' . urlencode($e->getMessage()));
    }
}
 else {
    redirect('../index.php?page=settings');
}
?>
