-- ============================================================
-- PMSS — schema cleanup & merge migration
-- Run this in phpMyAdmin AFTER taking a full DB backup.
-- Safe to re-run: all DDL is guarded with IF EXISTS / IF NOT EXISTS.
-- ============================================================
START TRANSACTION;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- 1) Drop empty stub tables (1-column placeholders, no rows)
-- ------------------------------------------------------------
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`;-- stale view stub; recreate as VIEW below

-- ------------------------------------------------------------
-- 2) Merge `admins` -> `admin_users`
--    admin_users is the superset (adds email/token/last_activity).
-- ------------------------------------------------------------
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.
--    pmss_payments + student_payments are empty in your dump.
--    If you ever populated them, copy first; here we just drop.
-- ------------------------------------------------------------
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`;

INSERT IGNORE INTO `payments`
    (`tran_id`, `invoice_id`, `student_id`,
     `amount`, `method`, `gateway`, `status`,
     `created_at`, `updated_at`)
SELECT IFNULL(`txn_id`, CONCAT('SP-', `id`)),
       `invoice_id`, `student_id`,
       `amount`, `method`, 'manual', `status`,
       `created_at`, `created_at`
FROM   `student_payments`;

DROP TABLE IF EXISTS `pmss_payments`;
DROP TABLE IF EXISTS `student_payments`;
-- payment audit log stays under its new clearer name
RENAME TABLE `pmss_payment_logs` TO `payment_logs`;

-- recreate the summary view (was a broken stub table before)
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`;
DROP TABLE IF EXISTS `pay_notifications`;

-- ------------------------------------------------------------
-- 5) Merge `internal_exam_results` -> `public_result`
--    public_result becomes the single per-exam aggregate table.
-- ------------------------------------------------------------
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`;
DROP TABLE IF EXISTS `internal_exam_results`;

-- ------------------------------------------------------------
-- 6) Helpful shared indexes (for cross-portal joins)
-- ------------------------------------------------------------
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;