-- ═══════════════════════════════════════════════════
-- QAFYS — 001 Initial Schema
-- Core tables: users, staff, students, parents
-- ═══════════════════════════════════════════════════

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

-- ────────────────────────────────────────
-- Users (all roles share this base table)
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id`            VARCHAR(20)   NOT NULL,
  `username`      VARCHAR(100)  NOT NULL,
  `password_hash` VARCHAR(255)  NOT NULL,
  `role`          ENUM('superadmin','admin','teacher','student','parent') NOT NULL,
  `first_name`    VARCHAR(100)  NOT NULL,
  `middle_name`   VARCHAR(100)  DEFAULT '',
  `last_name`     VARCHAR(100)  NOT NULL,
  `gender`        ENUM('Male','Female','—') DEFAULT '—',
  `status`        ENUM('active','inactive') DEFAULT 'active',
  `photo`         TEXT          DEFAULT NULL,
  `created_at`    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`),
  KEY `idx_role` (`role`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- Staff Details (superadmin, admin, teacher)
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `staff_details` (
  `user_id`         VARCHAR(20)  NOT NULL,
  `qafys_email`     VARCHAR(200) NOT NULL,
  `phone`           VARCHAR(30)  DEFAULT '',
  `address`         VARCHAR(500) DEFAULT '',
  `notes`           TEXT         DEFAULT NULL,
  `employment`      ENUM('full','part') DEFAULT 'full',
  `salary_type`     ENUM('monthly','hourly') DEFAULT 'monthly',
  `salary_amount`   DECIMAL(10,2) DEFAULT 0.00,
  `payment_method`  ENUM('direct','cash') DEFAULT 'direct',
  `dob`             DATE         DEFAULT NULL,
  `ssn_encrypted`   TEXT         DEFAULT NULL,
  `ssn_iv`          VARCHAR(64)  DEFAULT NULL,
  `ssn_tag`         VARCHAR(64)  DEFAULT NULL,
  `first_class_time` VARCHAR(5)  DEFAULT NULL,
  `clock_in_time`   VARCHAR(5)   DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `idx_qafys_email` (`qafys_email`),
  CONSTRAINT `fk_staff_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- Student Details
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `student_details` (
  `user_id`               VARCHAR(20)  NOT NULL,
  `grade_at_enrollment`   VARCHAR(20)  NOT NULL,
  `school`                VARCHAR(50)  DEFAULT 'Elementary',
  `date_enrolled`         DATE         NOT NULL,
  `osis`                  VARCHAR(20)  DEFAULT '',
  `home_address`          VARCHAR(500) DEFAULT '',
  `mother_name`           VARCHAR(200) DEFAULT '',
  `mother_phone`          VARCHAR(30)  DEFAULT '',
  `father_name`           VARCHAR(200) DEFAULT '',
  `father_phone`          VARCHAR(30)  DEFAULT '',
  `guardian_name`         VARCHAR(200) DEFAULT '',
  `student_phone`         VARCHAR(30)  DEFAULT '',
  `previous_school`       VARCHAR(200) DEFAULT '',
  `yearly_tuition`        DECIMAL(10,2) DEFAULT 0.00,
  `student_email`         VARCHAR(200) DEFAULT '',
  `student_password`      VARCHAR(255) DEFAULT '',
  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_student_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- Parent Details
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `parent_details` (
  `user_id`              VARCHAR(20)  NOT NULL,
  `email`                VARCHAR(200) NOT NULL,
  `parent1`              VARCHAR(200) DEFAULT '',
  `parent2`              VARCHAR(200) DEFAULT '',
  `guardian`             VARCHAR(200) DEFAULT '',
  `primary_address`      VARCHAR(500) DEFAULT '',
  `secondary_address`    VARCHAR(500) DEFAULT '',
  `phones`               VARCHAR(500) DEFAULT '',
  `emails`               VARCHAR(500) DEFAULT '',
  `emergency`            VARCHAR(500) DEFAULT '',
  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_parent_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- Parent ↔ Student Links (many-to-many)
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `parent_student_links` (
  `parent_id`  VARCHAR(20) NOT NULL,
  `student_id` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`parent_id`, `student_id`),
  CONSTRAINT `fk_psl_parent`  FOREIGN KEY (`parent_id`)  REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_psl_student` FOREIGN KEY (`student_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- Staff Additional Roles
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `user_additional_roles` (
  `user_id`         VARCHAR(20)  NOT NULL,
  `additional_role` VARCHAR(20)  NOT NULL,
  PRIMARY KEY (`user_id`, `additional_role`),
  CONSTRAINT `fk_addl_role_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- Teacher → Subjects
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `teacher_subjects` (
  `user_id` VARCHAR(20)  NOT NULL,
  `subject` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`user_id`, `subject`),
  CONSTRAINT `fk_ts_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- Teacher → Class Groups (grade|gender)
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `teacher_groups` (
  `user_id`    VARCHAR(20) NOT NULL,
  `group_key`  VARCHAR(50) NOT NULL,  -- e.g., "7|Male"
  PRIMARY KEY (`user_id`, `group_key`),
  CONSTRAINT `fk_tg_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- School Configuration
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `school_config` (
  `config_key`   VARCHAR(100) NOT NULL,
  `config_value` TEXT         NOT NULL,
  `updated_at`   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;