-- ============================================================
-- PMSS — UNIFIED PORTAL UPGRADE
-- Brings the live `purbomod_pmss` DB in sync with BOTH:
--   • Admin Panel  (project 34e08f95)
--   • Student Portal (project 1198146e)
--   • Teacher Portal (shares the same tables)
--
-- Safe to run on the dump dated 2026-05-19. Idempotent: every
-- statement uses IF EXISTS / IF NOT EXISTS / INSERT IGNORE so it
-- can be re-executed without error.
--
-- RUN IN phpMyAdmin AFTER taking a full backup.
-- ============================================================
START TRANSACTION;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- 1. CLEAN UP EMPTY STUB / DUPLICATE TABLES
-- ============================================================
DROP TABLE IF EXISTS `about_school`;
DROP TABLE IF EXISTS `attendance`;            -- real data lives in attendance_records
DROP TABLE IF EXISTS `dashboard`;
DROP TABLE IF EXISTS `dashboard_content`;
DROP TABLE IF EXISTS `school_result`;         -- aggregates live in public_result
DROP TABLE IF EXISTS `user_management`;
DROP TABLE IF EXISTS `pmss_payment_summary`;  -- recreated as VIEW below
DROP TABLE IF EXISTS `class_routin`;          -- typo'd empty stub; real table created below

-- ============================================================
-- 2. MERGE `admins` -> `admin_users`  (admin_users is superset)
-- ============================================================
INSERT IGNORE INTO `admin_users`
    (`id`, `full_name`, `username`, `password`, `role`, `user_image`, `created_at`)
SELECT  `id`, `full_name`, `username`, `password`, `role`, `user_image`, `created_at`
FROM    `admins`
WHERE   `username` NOT IN (SELECT `username` FROM `admin_users`);
DROP TABLE IF EXISTS `admins`;

-- ============================================================
-- 3. UNIFY PAYMENTS
--    `payments` is canonical. Copy any data from legacy tables
--    then drop them. Rename log table.
-- ============================================================
INSERT IGNORE INTO `payments`
    (`tran_id`, `invoice_id`, `student_id`, `student_name`,
     `amount`, `currency`, `method`, `gateway`, `status`,
     `note`, `created_at`, `updated_at`)
SELECT `tran_id`, NULL, CAST(`student_id` AS UNSIGNED), `student_name`,
       `amount`, `currency`, `method`, 'pmss', `status`,
       `notes`, `created_at`, IFNULL(`updated_at`, `created_at`)
FROM   `pmss_payments`
WHERE  EXISTS (SELECT 1 FROM information_schema.tables
               WHERE table_schema = DATABASE() AND table_name='pmss_payments');

DROP TABLE IF EXISTS `pmss_payments`;
DROP TABLE IF EXISTS `student_payments`;

-- Rename log table to clearer name (only if not yet renamed)
SET @r := (SELECT COUNT(*) FROM information_schema.tables
           WHERE table_schema=DATABASE() AND table_name='pmss_payment_logs');
SET @sql := IF(@r>0, 'RENAME TABLE `pmss_payment_logs` TO `payment_logs`', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

DROP VIEW IF EXISTS `payment_summary`;
CREATE VIEW `payment_summary` AS
SELECT  DATE(`created_at`)  AS `pay_date`,
        YEAR(`created_at`)  AS `session_year`,
        `method`, `status`,
        COUNT(*)            AS `total_count`,
        SUM(`amount`)       AS `total_amount`
FROM    `payments`
GROUP BY DATE(`created_at`), YEAR(`created_at`), `method`, `status`;

-- ============================================================
-- 4. MERGE `pay_notifications` -> `notifications`
-- ============================================================
INSERT IGNORE INTO `notifications`
    (`title`, `message`, `category`, `actor`, `link`, `is_read`, `created_at`)
SELECT  `title`, `message`, `type`, NULL,
        JSON_UNQUOTE(JSON_EXTRACT(`meta`, '$.link')), 0, `created_at`
FROM    `pay_notifications`
WHERE  EXISTS (SELECT 1 FROM information_schema.tables
               WHERE table_schema = DATABASE() AND table_name='pay_notifications');
DROP TABLE IF EXISTS `pay_notifications`;

-- ============================================================
-- 5. MERGE `internal_exam_results` -> `public_result`  (scope column)
-- ============================================================
ALTER TABLE `public_result`
    ADD COLUMN IF NOT EXISTS `class_name`       VARCHAR(50)  NULL AFTER `exam_name`,
    ADD COLUMN IF NOT EXISTS `publication_date` DATE         NULL,
    ADD COLUMN IF NOT EXISTS `download_url`     VARCHAR(255) NULL,
    ADD COLUMN IF NOT EXISTS `scope`            ENUM('public','internal') NOT NULL DEFAULT 'public';

INSERT INTO `public_result`
    (`exam_name`, `class_name`, `year`,
     `total_students`, `total_passed`, `total_failed`, `pass_rate`,
     `publication_date`, `download_url`, `scope`)
SELECT `exam_name`, `class_name`, `year`,
       `total_students`, `total_passed`, `total_failed`, `pass_rate`,
       `publication_date`, `download_url`, 'internal'
FROM   `internal_exam_results`
WHERE  EXISTS (SELECT 1 FROM information_schema.tables
               WHERE table_schema = DATABASE() AND table_name='internal_exam_results');
DROP TABLE IF EXISTS `internal_exam_results`;

-- ============================================================
-- 6. STUDENT PORTAL — AUTH COLUMNS on `students`
-- ============================================================
ALTER TABLE `students`
    ADD COLUMN IF NOT EXISTS `password`             VARCHAR(255) NULL AFTER `uid`,
    ADD COLUMN IF NOT EXISTS `token`                VARCHAR(128) NULL,
    ADD COLUMN IF NOT EXISTS `last_activity`        TIMESTAMP    NULL DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `must_change_password` TINYINT(1)   NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS `is_active`            TINYINT(1)   NOT NULL DEFAULT 1;

CREATE INDEX IF NOT EXISTS `idx_students_token` ON `students`(`token`);
CREATE INDEX IF NOT EXISTS `idx_students_uid`   ON `students`(`uid`);

-- ============================================================
-- 7. STUDENT PORTAL — NEW SHARED TABLES
--    These match the admin API endpoints in:
--      api/assignments, api/class-routine, api/exam-routine,
--      api/syllabus, api/chatrooms, api/leave-applications,
--      api/transport-routes, api/student-transport
-- ============================================================

-- --- 7a. Homework / Assignments ---------------------------------
CREATE TABLE IF NOT EXISTS `assignments` (
    `id`              INT(11) NOT NULL AUTO_INCREMENT,
    `title`           VARCHAR(255) NOT NULL,
    `description`     TEXT NULL,
    `subject_id`      INT(11) NULL,
    `subject_name`    VARCHAR(100) NULL,
    `teacher_id`      INT(11) NULL,
    `teacher_name`    VARCHAR(150) NULL,
    `class`           VARCHAR(20)  NULL,
    `section`         VARCHAR(20)  NULL,
    `group_name`      VARCHAR(50)  NULL,
    `attachment_url`  VARCHAR(500) NULL,
    `assigned_at`     DATE NULL,
    `due_date`        DATE NULL,
    `max_marks`       INT(11) NULL DEFAULT 100,
    `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_assign_class` (`class`, `section`),
    KEY `idx_assign_due`   (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `assignment_submissions` (
    `id`               INT(11) NOT NULL AUTO_INCREMENT,
    `assignment_id`    INT(11) NOT NULL,
    `student_id`       INT(11) NOT NULL,
    `file_url`         VARCHAR(500) NULL,
    `note`             TEXT NULL,
    `submitted_at`     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `marks`            DECIMAL(6,2) NULL,
    `max_marks`        DECIMAL(6,2) NULL,
    `feedback`         TEXT NULL,
    `graded_file_url`  VARCHAR(500) NULL,
    `graded_at`        TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_sub_assign_student` (`assignment_id`, `student_id`),
    KEY `idx_sub_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- 7b. Class & Exam Routines ---------------------------------
CREATE TABLE IF NOT EXISTS `class_routine` (
    `id`            INT(11) NOT NULL AUTO_INCREMENT,
    `class`         VARCHAR(20) NOT NULL,
    `section`       VARCHAR(20) NULL,
    `day`           ENUM('Sat','Sun','Mon','Tue','Wed','Thu','Fri') NOT NULL,
    `period`        INT(11) NOT NULL,
    `subject_id`    INT(11) NULL,
    `subject_name`  VARCHAR(100) NULL,
    `teacher_id`    INT(11) NULL,
    `teacher_name`  VARCHAR(150) NULL,
    `start_time`    TIME NULL,
    `end_time`      TIME NULL,
    `room`          VARCHAR(50) NULL,
    `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_class_period` (`class`,`section`,`day`,`period`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `exam_routine` (
    `id`            INT(11) NOT NULL AUTO_INCREMENT,
    `exam_name`     VARCHAR(150) NOT NULL,
    `class`         VARCHAR(20) NOT NULL,
    `section`       VARCHAR(20) NULL,
    `exam_date`     DATE NOT NULL,
    `subject_id`    INT(11) NULL,
    `subject_name`  VARCHAR(100) NULL,
    `start_time`    TIME NULL,
    `end_time`      TIME NULL,
    `room`          VARCHAR(50) NULL,
    `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_exam_class_date` (`class`,`exam_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- 7c. Syllabus ----------------------------------------------
CREATE TABLE IF NOT EXISTS `syllabus` (
    `id`           INT(11) NOT NULL AUTO_INCREMENT,
    `class`        VARCHAR(20) NULL,
    `section`      VARCHAR(20) NULL,
    `group_name`   VARCHAR(50) NULL,
    `subject_id`   INT(11) NULL,
    `subject_name` VARCHAR(100) NULL,
    `title`        VARCHAR(200) NOT NULL,
    `description`  TEXT NULL,
    `file_url`     VARCHAR(500) NULL,
    `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_syl_class` (`class`,`section`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- 7d. Student chatrooms + messages ---------------------------
CREATE TABLE IF NOT EXISTS `student_chatrooms` (
    `id`         INT(11) NOT NULL AUTO_INCREMENT,
    `name`       VARCHAR(150) NOT NULL,
    `class`      VARCHAR(20) NULL,
    `section`    VARCHAR(20) NULL,
    `group_name` VARCHAR(50) NULL,
    `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_room_class` (`class`,`section`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `chatroom_messages` (
    `id`           INT(11) NOT NULL AUTO_INCREMENT,
    `chatroom_id`  INT(11) NOT NULL,
    `sender_type`  ENUM('student','teacher','admin') NOT NULL,
    `sender_id`    INT(11) NOT NULL,
    `sender_name`  VARCHAR(150) NULL,
    `message`      TEXT NOT NULL,
    `attachment_url` VARCHAR(500) NULL,
    `created_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_msg_room_time` (`chatroom_id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- 7e. Leave applications ------------------------------------
CREATE TABLE IF NOT EXISTS `leave_applications` (
    `id`             INT(11) NOT NULL AUTO_INCREMENT,
    `student_id`     INT(11) NOT NULL,
    `from_date`      DATE NOT NULL,
    `to_date`        DATE NOT NULL,
    `reason`         TEXT NOT NULL,
    `attachment_url` VARCHAR(500) NULL,
    `status`         ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    `reviewed_by`    INT(11) NULL,
    `review_note`    TEXT NULL,
    `reviewed_at`    TIMESTAMP NULL DEFAULT NULL,
    `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_leave_student` (`student_id`),
    KEY `idx_leave_status`  (`status`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --- 7f. Transport ---------------------------------------------
CREATE TABLE IF NOT EXISTS `transport_routes` (
    `id`            INT(11) NOT NULL AUTO_INCREMENT,
    `route_name`    VARCHAR(150) NOT NULL,
    `driver_name`   VARCHAR(150) NULL,
    `driver_phone`  VARCHAR(30) NULL,
    `vehicle_no`    VARCHAR(50) NULL,
    `pickup_point`  VARCHAR(255) NULL,
    `pickup_time`   TIME NULL,
    `drop_time`     TIME NULL,
    `stops`         TEXT NULL,
    `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_route_name` (`route_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `student_transport` (
    `student_id` INT(11) NOT NULL,
    `route_id`   INT(11) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`student_id`),
    KEY `idx_st_route` (`route_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. SHARED CROSS-PORTAL INDEXES
-- ============================================================
ALTER TABLE `attendance_records`
    ADD INDEX IF NOT EXISTS `idx_att_user` (`user_type`,`user_id`,`date`),
    ADD INDEX IF NOT EXISTS `idx_att_date` (`date`);

ALTER TABLE `notifications`
    ADD INDEX IF NOT EXISTS `idx_notif_cat` (`category`,`is_read`,`created_at`);

ALTER TABLE `payments`
    ADD INDEX IF NOT EXISTS `idx_pay_student` (`student_id`,`created_at`),
    ADD INDEX IF NOT EXISTS `idx_pay_status`  (`status`);

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;

-- ============================================================
-- 9. POST-MIGRATION (run ONCE from PHP, NOT in SQL):
--    php api/migrations/seed_student_passwords.php
--    → bcrypt-seeds every student's password to their UID,
--      sets must_change_password=1.
-- ============================================================