-- =====================================================
-- PMSS — Full Schema Sync (idempotent)
-- Run this in phpMyAdmin (SQL tab) on your MySQL database.
-- Safe to re-run: only adds missing tables / columns / indexes.
-- Brings MySQL in line with the current Lovable Cloud schema
-- so the one-way sync (Cloud -> MySQL) writes succeed.
-- =====================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

-- -----------------------------------------------------
-- Helper: add column only if missing
-- -----------------------------------------------------
DROP PROCEDURE IF EXISTS pmss_add_column_if_missing;
DELIMITER //
CREATE PROCEDURE pmss_add_column_if_missing(
  IN p_table VARCHAR(64),
  IN p_column VARCHAR(64),
  IN p_definition TEXT
)
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 @sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END IF;
END //
DELIMITER ;

DROP PROCEDURE IF EXISTS pmss_add_index_if_missing;
DELIMITER //
CREATE PROCEDURE pmss_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 @sql = CONCAT('ALTER TABLE `', p_table, '` ADD INDEX `', p_index, '` (', p_columns, ')');
    PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
  END IF;
END //
DELIMITER ;

-- =====================================================
-- 1) qr_code columns on people tables
-- =====================================================
CALL pmss_add_column_if_missing('students',     'qr_code', 'VARCHAR(255) NULL DEFAULT NULL');
CALL pmss_add_column_if_missing('teachers',     'qr_code', 'VARCHAR(255) NULL DEFAULT NULL');
CALL pmss_add_column_if_missing('school_staff', 'qr_code', 'VARCHAR(255) NULL DEFAULT NULL');
CALL pmss_add_index_if_missing('students',     'idx_students_qr_code',     '`qr_code`');
CALL pmss_add_index_if_missing('teachers',     'idx_teachers_qr_code',     '`qr_code`');
CALL pmss_add_index_if_missing('school_staff', 'idx_school_staff_qr_code', '`qr_code`');

-- =====================================================
-- 2) person_qr_codes (lookup table used by /qr/<token>)
-- =====================================================
CREATE TABLE IF NOT EXISTS `person_qr_codes` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `person_type` VARCHAR(32) NOT NULL,
  `person_id` BIGINT NOT NULL,
  `qr_token` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_person` (`person_type`, `person_id`),
  UNIQUE KEY `uniq_token`  (`qr_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 3) attendance_records (entry/exit log)
-- =====================================================
CREATE TABLE IF NOT EXISTS `attendance_records` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT NOT NULL,
  `user_type` VARCHAR(32) NOT NULL,
  `date` DATE NOT NULL,
  `entry_time` TIME NULL,
  `exit_time` TIME NULL,
  `status` VARCHAR(20) DEFAULT 'present',
  `device_id` VARCHAR(64) NULL,
  `marked_by` INT NULL,
  `remarks` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_attendance_user` (`user_type`, `user_id`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 4) signatures
-- =====================================================
CREATE TABLE IF NOT EXISTS `signatures` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `person_name` VARCHAR(255) NOT NULL,
  `designation` VARCHAR(255) NOT NULL,
  `signature_image` VARCHAR(500) NULL,
  `signature_type` VARCHAR(50) DEFAULT 'principal',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 5) marks
-- =====================================================
CREATE TABLE IF NOT EXISTS `marks` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `student_id` BIGINT NULL,
  `exam_id` BIGINT NULL,
  `subject_id` BIGINT NULL,
  `paper_type` VARCHAR(20) DEFAULT 'paper1',
  `written` DECIMAL(6,2) DEFAULT 0,
  `mcq` DECIMAL(6,2) DEFAULT 0,
  `practical` DECIMAL(6,2) DEFAULT 0,
  `ca` DECIMAL(6,2) DEFAULT 0,
  `total` DECIMAL(6,2) DEFAULT 0,
  `marks_obtained` DECIMAL(6,2) NULL,
  `grade` VARCHAR(10) NULL,
  `gpa` DECIMAL(4,2) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_marks_student_exam` (`student_id`, `exam_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 6) individual_result
-- =====================================================
CREATE TABLE IF NOT EXISTS `individual_result` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `exam_name` VARCHAR(255) NOT NULL,
  `class` VARCHAR(20) NOT NULL,
  `group_name` VARCHAR(50) NULL,
  `session_year` INT NOT NULL,
  `student_id` INT NOT NULL,
  `student_name` VARCHAR(255) NULL,
  `roll` INT NULL,
  `subjects` LONGTEXT NOT NULL,
  `total_marks` DECIMAL(8,2) DEFAULT 0,
  `gpa` DECIMAL(4,2) DEFAULT 0,
  `grade` VARCHAR(10) DEFAULT 'F',
  `result` VARCHAR(10) DEFAULT 'FAIL',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_indres_lookup` (`exam_name`, `class`, `session_year`, `student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 7) exam_results / internal_exam_results
-- =====================================================
CREATE TABLE IF NOT EXISTS `exam_results` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `result_type` VARCHAR(20) NOT NULL DEFAULT 'internal',
  `exam_name` VARCHAR(255) NOT NULL,
  `class_name` VARCHAR(50) NULL,
  `year` INT NOT NULL,
  `total_students` INT NOT NULL DEFAULT 0,
  `total_passed` INT NOT NULL DEFAULT 0,
  `total_failed` INT NOT NULL DEFAULT 0,
  `passed_boys` INT DEFAULT 0,
  `passed_girls` INT DEFAULT 0,
  `failed_boys` INT DEFAULT 0,
  `failed_girls` INT DEFAULT 0,
  `pass_rate` DECIMAL(5,2) DEFAULT 0,
  `gpa` DECIMAL(4,2) DEFAULT 0,
  `publication_date` DATE NULL,
  `download_url` VARCHAR(500) NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `internal_exam_results` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `exam_name` VARCHAR(255) NOT NULL,
  `class_name` VARCHAR(50) NOT NULL,
  `year` INT NOT NULL,
  `total_students` INT DEFAULT 0,
  `total_passed` INT DEFAULT 0,
  `total_failed` INT DEFAULT 0,
  `pass_rate` DECIMAL(5,2) DEFAULT 0,
  `publication_date` DATE NULL,
  `download_url` VARCHAR(500) NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 8) class_group_subjects
-- =====================================================
CREATE TABLE IF NOT EXISTS `class_group_subjects` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `class_id` INT NOT NULL,
  `group_name` VARCHAR(50) NULL,
  `subject_id` BIGINT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_cgs` (`class_id`, `group_name`, `subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 9) notifications
-- =====================================================
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `category` VARCHAR(50) NOT NULL DEFAULT 'system',
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NULL,
  `link` VARCHAR(500) NULL,
  `actor` VARCHAR(100) NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 10) activity_log / visitor_stats
-- =====================================================
CREATE TABLE IF NOT EXISTS `activity_log` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `admin_id` BIGINT NOT NULL,
  `action` VARCHAR(64) NOT NULL,
  `module` VARCHAR(64) NOT NULL,
  `item_id` BIGINT NULL,
  `details` TEXT NULL,
  `ip_address` VARCHAR(64) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_activity_admin` (`admin_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `visitor_stats` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `ip_address` VARCHAR(64) NOT NULL,
  `session_id` VARCHAR(128) NOT NULL,
  `visited_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_visitor_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 11) password_reset_requests / contact_messages
-- =====================================================
CREATE TABLE IF NOT EXISTS `password_reset_requests` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NULL,
  `phone` VARCHAR(30) NULL,
  `id_number` VARCHAR(64) NULL,
  `status` VARCHAR(20) DEFAULT 'pending',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `contact_messages` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `your_name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(30) NOT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 12) student_invoices
-- =====================================================
CREATE TABLE IF NOT EXISTS `student_invoices` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `student_id` BIGINT NOT NULL,
  `invoice_number` VARCHAR(64) NOT NULL,
  `invoice_date` DATE NOT NULL DEFAULT (CURRENT_DATE),
  `due_date` DATE NULL,
  `items` LONGTEXT NOT NULL,
  `subtotal` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `discount_type` VARCHAR(20) DEFAULT 'none',
  `discount_value` DECIMAL(10,2) DEFAULT 0,
  `total` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `status` VARCHAR(20) DEFAULT 'Unpaid',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_invoice_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 13) app_settings (single-row)
-- =====================================================
CREATE TABLE IF NOT EXISTS `app_settings` (
  `id` INT NOT NULL DEFAULT 1,
  `school_name` VARCHAR(255) DEFAULT 'PMSS',
  `school_logo` VARCHAR(500) NULL,
  `admin_email` VARCHAR(255) NULL,
  `theme_preference` VARCHAR(20) DEFAULT 'light',
  `default_session` INT NULL,
  `class_config` TEXT DEFAULT NULL,
  `notifications_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `app_settings` (`id`) VALUES (1);

-- =====================================================
-- Cleanup helpers
-- =====================================================
DROP PROCEDURE IF EXISTS pmss_add_column_if_missing;
DROP PROCEDURE IF EXISTS pmss_add_index_if_missing;

SET FOREIGN_KEY_CHECKS = 1;
SELECT 'PMSS schema sync complete' AS status;