# MAVELA Management System - Database Migration and Data Cleanup
# For Namecheap Shared Hosting Deployment
# Purpose: add learner promotion workflow columns, clear learner data,
#          reset auto-increment so next admission starts at MAV26001
#
# IMPORTANT: Do NOT copy-paste this file's text from a chat window or
# markdown viewer into phpMyAdmin - rich text copy can silently corrupt
# comment dashes and break the SQL syntax (this is what caused the
# #1064 error). Instead use phpMyAdmin's Import tab and upload this
# .sql file directly, or run it via the mysql command line client.

DELIMITER $$

DROP PROCEDURE IF EXISTS mavela_add_column_if_missing $$
CREATE PROCEDURE mavela_add_column_if_missing(
  IN p_table VARCHAR(64),
  IN p_column VARCHAR(64),
  IN p_definition VARCHAR(255)
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND COLUMN_NAME = p_column
  ) THEN
    SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END $$

DROP PROCEDURE IF EXISTS mavela_add_index_if_missing $$
CREATE PROCEDURE mavela_add_index_if_missing(
  IN p_table VARCHAR(64),
  IN p_index VARCHAR(64),
  IN p_columns VARCHAR(255)
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND INDEX_NAME = p_index
  ) THEN
    SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD INDEX `', p_index, '` (', p_columns, ')');
    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END $$

DELIMITER ;

# PHASE 1: add promotion workflow columns to learner_promotions

CALL mavela_add_column_if_missing('learner_promotions', 'status', "ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending'");
CALL mavela_add_column_if_missing('learner_promotions', 'balance_before', 'DECIMAL(10,2) NOT NULL DEFAULT 0.00');
CALL mavela_add_column_if_missing('learner_promotions', 'balance_carried', 'DECIMAL(10,2) NOT NULL DEFAULT 0.00');
CALL mavela_add_column_if_missing('learner_promotions', 'created_by', 'INT(11) DEFAULT NULL');
CALL mavela_add_column_if_missing('learner_promotions', 'approved_by', 'INT(11) DEFAULT NULL');
CALL mavela_add_column_if_missing('learner_promotions', 'created_at', 'TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP');
CALL mavela_add_column_if_missing('learner_promotions', 'approved_at', 'DATETIME DEFAULT NULL');

CALL mavela_add_index_if_missing('learner_promotions', 'idx_status', '`status`');
CALL mavela_add_index_if_missing('learner_promotions', 'idx_learner_year', '`learner_id`, `promotion_year`');

DROP PROCEDURE IF EXISTS mavela_add_column_if_missing;
DROP PROCEDURE IF EXISTS mavela_add_index_if_missing;

# PHASE 2: clear all learner-related data for a fresh start

SET FOREIGN_KEY_CHECKS = 0;

DELETE FROM `learner_marks`;

DELETE FROM `learner_notes`;

DELETE FROM `learner_promotions`;

DELETE FROM `daily_attendance_register`;

DELETE FROM `elearning_submissions`;

DELETE FROM `elearning_posts`;

DELETE FROM `fee_payments`;

DELETE FROM `fee_invoices`;

DELETE FROM `parent_learner_links`;

DELETE FROM `learner_applications`;

DELETE FROM `admissions_pending`;

DELETE FROM `learners`;

DELETE FROM `library_access_logs`;

# Library resources are kept by default. Uncomment the next line only
# if you also want to remove uploaded library documents:
# DELETE FROM `library_resources`;

SET FOREIGN_KEY_CHECKS = 1;

# PHASE 3: reset auto-increment counters so next admission is MAV26001

ALTER TABLE `learners` AUTO_INCREMENT = 1;
ALTER TABLE `learner_marks` AUTO_INCREMENT = 1;
ALTER TABLE `learner_notes` AUTO_INCREMENT = 1;
ALTER TABLE `learner_promotions` AUTO_INCREMENT = 1;
ALTER TABLE `daily_attendance_register` AUTO_INCREMENT = 1;
ALTER TABLE `fee_payments` AUTO_INCREMENT = 1;
ALTER TABLE `fee_invoices` AUTO_INCREMENT = 1;
ALTER TABLE `parent_learner_links` AUTO_INCREMENT = 1;
ALTER TABLE `learner_applications` AUTO_INCREMENT = 1;
ALTER TABLE `admissions_pending` AUTO_INCREMENT = 1;
ALTER TABLE `elearning_posts` AUTO_INCREMENT = 1;
ALTER TABLE `elearning_submissions` AUTO_INCREMENT = 1;
ALTER TABLE `library_resources` AUTO_INCREMENT = 1;
ALTER TABLE `library_access_logs` AUTO_INCREMENT = 1;

# PHASE 4: verification - run these manually after the script completes
#
# SELECT COUNT(*) AS learner_count FROM learners;
# SHOW COLUMNS FROM learner_promotions;
# SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
#   WHERE TABLE_NAME = 'learners' AND TABLE_SCHEMA = DATABASE();
#
# All done. Your database now has the learner promotion workflow
# columns, all student/learner data cleared, and the next admission
# will be assigned registration number MAV26001.
