-- NAC Graduation Photo Album System — database schema
-- MySQL 5.7+ / MariaDB 10.3+ (what cPanel provides)

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------------
-- Staff who use the system
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(64)  NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name     VARCHAR(191) NOT NULL,
  role          ENUM('admin','filler') NOT NULL DEFAULT 'filler',
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Departments / programs. A program is the combination of
-- name + level (Certificate, Level IV, B.A …) + mode (Regular, Extension …),
-- because the same subject is taught at different levels and times.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS programs (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(191) NOT NULL,
  level      VARCHAR(64)  NOT NULL,
  mode       VARCHAR(64)  NOT NULL,
  is_active  TINYINT(1)   NOT NULL DEFAULT 1,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_program (name, level, mode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Graduating students and their chosen photos.
--
-- photo1_no / photo2_no are the alphanumeric numbers written on the studio's
-- prints. Each number identifies exactly one photo of exactly one student, so
-- both columns are unique and the application also checks across the two
-- columns before saving.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS students (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  student_id VARCHAR(64)  NOT NULL,             -- the college ID, e.g. MMR/029/16
  full_name  VARCHAR(191) NOT NULL,
  sex        ENUM('M','F') NULL,
  program_id INT NULL,
  photo1_no  VARCHAR(64)  NULL,
  photo2_no  VARCHAR(64)  NULL,
  ft_number  VARCHAR(64)  NULL,                 -- photo payment transaction (FT) number
  remark     VARCHAR(255) NULL,
  filled_at  DATETIME     NULL,                 -- when the photo details were entered
  filled_by  INT          NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_students_student_id (student_id),
  UNIQUE KEY uq_students_photo1 (photo1_no),
  UNIQUE KEY uq_students_photo2 (photo2_no),
  KEY ix_students_program (program_id),
  KEY ix_students_full_name (full_name),
  CONSTRAINT fk_students_program FOREIGN KEY (program_id) REFERENCES programs (id) ON DELETE SET NULL,
  CONSTRAINT fk_students_filled_by FOREIGN KEY (filled_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Editable labels and preferences (photo slot names, college name …)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
  name  VARCHAR(64) PRIMARY KEY,
  value TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Who changed what. Student records can be corrected, so every change is kept.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS activity_log (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT NULL,
  user_name  VARCHAR(191) NULL,
  action     VARCHAR(64)  NOT NULL,   -- created | updated | photos_filled | imported | deleted
  student_id INT NULL,
  detail     TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_log_student (student_id),
  KEY ix_log_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;