<?php
session_start();
error_reporting(E_ALL);
if(isset($_SESSION['username'])){
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <?php
  include 'includes/styles.php';
  global $school_name;
  ?>
    <title>Migrate to Flexible Result System | <?php echo $school_name; ?> </title>
    <?php
    $module_name = 'Mark';
    include "check-admin-privilege.php";
    ?>
<?php
    // Migration class
    class FlexibleSystemMigration {
    private $conn;
    private $log = [];

    public function __construct($conn) {
    $this->conn = $conn;
    }

    public function log($message) {
    $this->log[] = date('Y-m-d H:i:s') . " - " . $message;
    echo "<p>" . htmlspecialchars($message) . "</p>";
    flush();
    }

    public function migrate() {
    $this->log("Starting migration from old system to flexible assessment system...");

    try {
    // Step 1: Create assessment templates from existing mark_setting data
    $this->migrateMarkSettings();

    // Step 2: Migrate existing results data
    $this->migrateResults();

    // Step 3: Update position tables if needed
    $this->updatePositions();

    $this->log("Migration completed successfully!");

    } catch (Exception $e) {
    $this->log("Migration failed: " . $e->getMessage());
    throw $e;
    }
    }

    private function migrateMarkSettings() {
    $this->log("Migrating mark settings to assessment templates...");

    // Get all existing mark settings
    $sql = "SELECT * FROM mark_setting";
    $this->conn->query($sql);
    $mark_settings = $this->conn->fetchMultiple();

    if (empty($mark_settings)) {
    $this->log("No mark settings found. Creating default templates...");
    $this->createDefaultTemplates();
    return;
    }

    foreach ($mark_settings as $setting) {
    $template_name = "Standard Assessment - {$setting->class} - {$setting->term}";

    // Insert assessment template
    $sql = "INSERT INTO assessment_templates (template_name, class, term, description, total_marks, status)
    VALUES (:template_name, :class, :term, :description, 100, 'active')";
    $this->conn->query($sql);
    $this->conn->bind(':template_name', $template_name);
    $this->conn->bind(':class', $setting->class);
    $this->conn->bind(':term', $setting->term);
    $this->conn->bind(':description', "Migrated from old mark setting system");
    $this->conn->execute();

    $template_id = $this->conn->lastInserted();

    // Insert assessment components
    $components = [
    ['name' => '1st CA', 'key' => 'first_ca', 'marks' => $setting->first_ca, 'order' => 1],
    ['name' => '2nd CA', 'key' => 'second_ca', 'marks' => $setting->second_ca, 'order' => 2],
    ['name' => '3rd CA', 'key' => 'third_ca', 'marks' => $setting->third_ca, 'order' => 3],
    ['name' => 'Exam', 'key' => 'exam', 'marks' => $setting->exam, 'order' => 4]
    ];

    foreach ($components as $component) {
    if ($component['marks'] > 0) { // Only add components with marks > 0
    $sql = "INSERT INTO assessment_components
    (template_id, component_name, component_key, max_marks, display_order)
    VALUES (:template_id, :component_name, :component_key, :max_marks, :display_order)";
    $this->conn->query($sql);
    $this->conn->bind(':template_id', $template_id);
    $this->conn->bind(':component_name', $component['name']);
    $this->conn->bind(':component_key', $component['key']);
    $this->conn->bind(':max_marks', $component['marks']);
    $this->conn->bind(':display_order', $component['order']);
    $this->conn->execute();
    }
    }

    $this->log("Created template: $template_name");
    }

    $this->log("Mark settings migration completed.");
    }

    private function createDefaultTemplates() {
    // Create a default template for common classes and terms
    $default_classes = ['JSS 1', 'JSS 2', 'JSS 3', 'SS 1', 'SS 2', 'SS 3'];
    $default_terms = ['1st term', '2nd term', '3rd term'];

    foreach ($default_classes as $class) {
    foreach ($default_terms as $term) {
    $template_name = "Standard Assessment";

    $sql = "INSERT INTO assessment_templates (template_name, class, term, description, total_marks, status)
    VALUES (:template_name, :class, :term, :description, 100, 'active')";
    $this->conn->query($sql);
    $this->conn->bind(':template_name', $template_name);
    $this->conn->bind(':class', $class);
    $this->conn->bind(':term', $term);
    $this->conn->bind(':description', "Default template created during migration");
    $this->conn->execute();

    $template_id = $this->conn->lastInserted();

    // Default components
    $components = [
    ['name' => '1st CA', 'key' => 'first_ca', 'marks' => 10, 'order' => 1],
    ['name' => '2nd CA', 'key' => 'second_ca', 'marks' => 10, 'order' => 2],
    ['name' => '3rd CA', 'key' => 'third_ca', 'marks' => 10, 'order' => 3],
    ['name' => 'Exam', 'key' => 'exam', 'marks' => 70, 'order' => 4]
    ];

    foreach ($components as $component) {
    $sql = "INSERT INTO assessment_components
    (template_id, component_name, component_key, max_marks, display_order)
    VALUES (:template_id, :component_name, :component_key, :max_marks, :display_order)";
    $this->conn->query($sql);
    $this->conn->bind(':template_id', $template_id);
    $this->conn->bind(':component_name', $component['name']);
    $this->conn->bind(':component_key', $component['key']);
    $this->conn->bind(':max_marks', $component['marks']);
    $this->conn->bind(':display_order', $component['order']);
    $this->conn->execute();
    }
    }
    }

    $this->log("Created default templates for common classes and terms.");
    }

    private function migrateResults() {
    $this->log("Migrating existing results to new flexible system...");

    // Get all results from old system
    $sql = "SELECT * FROM results WHERE first_ca > 0 OR second_ca > 0 OR third_ca > 0 OR exam_score > 0";
    $this->conn->query($sql);
    $old_results = $this->conn->fetchMultiple();

    if (empty($old_results)) {
    $this->log("No existing results to migrate.");
    return;
    }

    $migrated_count = 0;
    $error_count = 0;

    foreach ($old_results as $result) {
    try {
    // Get template for this class and term
    $sql = "SELECT id FROM assessment_templates
    WHERE class = :class AND term = :term AND status = 'active'
    LIMIT 1";
    $this->conn->query($sql);
    $this->conn->bind(':class', $result->class);
    $this->conn->bind(':term', $result->exam);
    $template = $this->conn->fetchSingle();

    if (!$template) {
    $this->log("Warning: No template found for {$result->class} - {$result->exam}. Skipping result for {$result->name_of_student}.");
    $error_count++;
    continue;
    }

    $template_id = $template->id;

    // Get template components
    $sql = "SELECT * FROM assessment_components WHERE template_id = :template_id ORDER BY display_order";
    $this->conn->query($sql);
    $this->conn->bind(':template_id', $template_id);
    $components = $this->conn->fetchMultiple();

    // Calculate total score
    $total_score = floatval($result->first_ca) + floatval($result->second_ca) +
    floatval($result->third_ca) + floatval($result->exam_score);

    // Calculate percentage
    $percentage = ($total_score / 100) * 100;

    // Get grade
    $sql = "SELECT grade_name FROM grade WHERE :percentage BETWEEN mark_from AND mark_upto";
    $this->conn->query($sql);
    $this->conn->bind(':percentage', $percentage);
    $grade_result = $this->conn->fetchSingle();
    $grade = $grade_result ? $grade_result->grade_name : '';

    // Get student reg number
    $sql = "SELECT registerNO FROM student WHERE name = :name";
    $this->conn->query($sql);
    $this->conn->bind(':name', $result->name_of_student);
    $reg_no = $this->conn->fetchColumn();

    // Insert into student_results
    $sql = "INSERT INTO student_results
    (student_name, reg_no, class, section, subject, term, school_year,
    current_school_session, template_id, total_score, percentage, grade)
    VALUES (:student_name, :reg_no, :class, :section, :subject, :term,
    :school_year, :current_school_session, :template_id, :total_score,
    :percentage, :grade)";

    $this->conn->query($sql);
    $this->conn->bind(':student_name', $result->name_of_student);
    $this->conn->bind(':reg_no', $reg_no);
    $this->conn->bind(':class', $result->class);
    $this->conn->bind(':section', $result->section);
    $this->conn->bind(':subject', $result->subject);
    $this->conn->bind(':term', $result->exam);
    $this->conn->bind(':school_year', $result->school_year);
    $this->conn->bind(':current_school_session', $result->current_school_session);
    $this->conn->bind(':template_id', $template_id);
    $this->conn->bind(':total_score', $total_score);
    $this->conn->bind(':percentage', $percentage);
    $this->conn->bind(':grade', $grade);
    $this->conn->execute();

    $new_result_id = $this->conn->lastInserted();

    // Insert component scores
    $component_scores = [
    ['score' => floatval($result->first_ca), 'key' => 'first_ca'],
    ['score' => floatval($result->second_ca), 'key' => 'second_ca'],
    ['score' => floatval($result->third_ca), 'key' => 'third_ca'],
    ['score' => floatval($result->exam_score), 'key' => 'exam']
    ];

    foreach ($components as $component) {
    foreach ($component_scores as $score_data) {
    if ($component->component_key == $score_data['key']) {
    $sql = "INSERT INTO student_assessment_scores
    (result_id, component_id, score)
    VALUES (:result_id, :component_id, :score)";
    $this->conn->query($sql);
    $this->conn->bind(':result_id', $new_result_id);
    $this->conn->bind(':component_id', $component->id);
    $this->conn->bind(':score', $score_data['score']);
    $this->conn->execute();
    break;
    }
    }
    }

    $migrated_count++;

    } catch (Exception $e) {
    $this->log("Error migrating result for {$result->name_of_student}: " . $e->getMessage());
    $error_count++;
    }
    }

    $this->log("Results migration completed. Migrated: $migrated_count, Errors: $error_count");
    }

    private function updatePositions() {
    $this->log("Updating position calculations for new system...");

    // This would involve recalculating positions based on the new flexible system
    // For now, we'll keep existing position data and let the system update them as new marks are added

    $this->log("Position updates completed.");
    }

    public function getLogs() {
    return $this->log;
    }
    }

    // HTML Interface
    ?>


</head>
<body class="hold-transition sidebar-mini layout-fixed">
<div class="wrapper">

  <!-- Navbar -->
    <?php include 'includes/notifications.php'; ?>
  <!-- /.navbar -->

  <!-- Main Sidebar Container -->
<?php include "includes/sidebar.php";?>

  <!-- Content Wrapper. Contains page content -->
  <div class="content-wrapper">
    <!-- Content Header (Page header) -->
    <div class="content-header">
      <div class="container-fluid">
        <div class="row mb-2">
          <div class="col-sm-6">
            <h1 class="m-0">Migrate to flexible system</h1>
          </div><!-- /.col -->
          <div class="col-sm-6">
            <ol class="breadcrumb float-sm-right">
              <li class="breadcrumb-item"><a href="<?php echo $conn->base_url();?>">Home</a></li>
              <li class="breadcrumb-item active">Add to flexible system</li>
            </ol>
          </div><!-- /.col -->
        </div><!-- /.row -->
      </div><!-- /.container-fluid -->
    </div>
    <!-- /.content-header -->

    <!-- Main content -->
    <section class="content">
        <div class="container-fluid">

            <div class="alert alert-warning">
                <h5><i class="icon fas fa-exclamation-triangle"></i> Important!</h5>
                <p><strong>Backup your database before running this migration!</strong></p>
                <p>This script will create new tables and migrate your existing mark settings and results to the new flexible assessment system.</p>
                <ul>
                    <li>Existing data in the <code>mark_setting</code> and <code>results</code> tables will be preserved</li>
                    <li>New tables will be created: <code>assessment_templates</code>, <code>assessment_components</code>, <code>student_results</code>, <code>student_assessment_scores</code></li>
                    <li>You can continue using the old system alongside the new one during transition</li>
                    <li>It's advisable to run this migration before creating asset templates for the new flexible system</li>
                </ul>
            </div>

            <?php if (isset($_POST['run_migration'])): ?>
                <div class="card">
                    <div class="card-header">
                        <h3 class="card-title">Migration Progress</h3>
                    </div>
                    <div class="card-body">
                        <div class="migration-log" id="migrationLog">
                            <?php
                            try {
                                $migration = new FlexibleSystemMigration($conn);
                                $migration->migrate();
                                echo "<div class='alert alert-success mt-3'>";
                                echo "<h5><i class='fas fa-check-circle'></i> Migration Completed Successfully!</h5>";
                                echo "<p>Your system has been successfully migrated to the flexible assessment system.</p>";
                                echo "<p><a href='mark-setting' class='btn btn-primary'>Manage Assessment Templates</a> ";
                                echo "<a href='add-mark' class='btn btn-success'>Add Marks (New System)</a></p>";
                                echo "</div>";
                            } catch (Exception $e) {
                                echo "<div class='alert alert-danger mt-3'>";
                                echo "<h5><i class='fas fa-times-circle'></i> Migration Failed!</h5>";
                                echo "<p>Error: " . htmlspecialchars($e->getMessage()) . "</p>";
                                echo "<p>Please check your database and try again.</p>";
                                echo "</div>";
                            }
                            ?>
                        </div>
                    </div>
                </div>
            <?php else: ?>
                <div class="card">
                    <div class="card-header">
                        <h3 class="card-title">Pre-Migration Checklist</h3>
                    </div>
                    <div class="card-body">
                        <div class="checklist">
                            <div class="form-check">
                                <input class="form-check-input" type="checkbox" id="backup-check">
                                <label class="form-check-label" for="backup-check">
                                    I have backed up my database
                                </label>
                            </div>
                            <div class="form-check">
                                <input class="form-check-input" type="checkbox" id="understand-check">
                                <label class="form-check-label" for="understand-check">
                                    I understand this will create new database tables
                                </label>
                            </div>
                            <div class="form-check">
                                <input class="form-check-input" type="checkbox" id="time-check">
                                <label class="form-check-label" for="time-check">
                                    I have time to complete this migration now
                                </label>
                            </div>
                        </div>

                        <form method="post" style="margin-top: 20px;">
                            <button type="submit" name="run_migration" class="btn btn-danger btn-lg" id="migration-btn" disabled>
                                <i class="fas fa-play"></i> Start Migration
                            </button>
                        </form>
                    </div>
                </div>

                <div class="card">
                    <div class="card-header">
                        <h3 class="card-title">What This Migration Does</h3>
                    </div>
                    <div class="card-body">
                        <ol>
                            <li><strong>Creates New Database Tables:</strong>
                                <ul>
                                    <li><code>assessment_templates</code> - Stores flexible assessment configurations</li>
                                    <li><code>assessment_components</code> - Stores individual assessment components (CA, Exam, etc.)</li>
                                    <li><code>student_results</code> - New results table with template linking</li>
                                    <li><code>student_assessment_scores</code> - Individual component scores</li>
                                </ul>
                            </li>
                            <li><strong>Migrates Mark Settings:</strong> Converts existing mark_setting entries to assessment templates</li>
                            <li><strong>Migrates Student Results:</strong> Converts existing results to the new flexible format</li>
                            <li><strong>Preserves Original Data:</strong> Your old tables remain unchanged</li>
                        </ol>
                    </div>
                </div>
            <?php endif; ?>
        </div>
    </section>
    <!-- /.content -->
  </div>
  <!-- /.content-wrapper -->
  <?php include "includes/footer.php";?>

    <script>
        $(document).ready(function() {
            // Enable migration button only when all checkboxes are checked
            $('.form-check-input').change(function() {
                var allChecked = $('.form-check-input:checked').length === $('.form-check-input').length;
                $('#migration-btn').prop('disabled', !allChecked);

                if (allChecked) {
                    $('#migration-btn').removeClass('btn-secondary').addClass('btn-danger');
                } else {
                    $('#migration-btn').removeClass('btn-danger').addClass('btn-secondary');
                }
            });

            // Auto-scroll migration log
            setInterval(function() {
                var log = $('#migrationLog');
                if (log.length) {
                    log.scrollTop(log[0].scrollHeight);
                }
            }, 500);
        });
    </script>

    <script>
      $(document).ready(function(){
        $('.select2').select2();

      })

    </script>


    <?php
  }else{
      ?>
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, shrink-to-fit=no">
        <?php include 'includes/styles.php'; ?>
        <title>Login to continue | <?php echo $school_name; ?> </title>

        <!-- END PAGE LEVEL PLUGINS/CUSTOM STYLES -->

    </head>
    <body>
    <div class="container" style="margin-top: 20%;">
        <div class="row">
            <div class="col-lg-12 col-md-12">
                <div class="section-title text-center">
                    <h6> You need to login to continue as an admin to run migration </h6>
                    <div class="spinner-border text-info" role="status">
                        <span class="sr-only">Loading...</span>
                        <meta http-equiv='refresh' content='3; <?php echo $link; ?>'>
                    </div>
                    <p>Redirecting to the login  page...</p>
                </div>
            </div>
        </div>
    </div>

    <?php
    }

  ?>
