-- ============================================================
-- SEASWT 2026 — Database Schema
-- Southwest Education Accountability & Students' Welfare Tour
-- NANS Southwest Zone D
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE DATABASE IF NOT EXISTS `seaswt2026`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `seaswt2026`;

-- ─────────────────────────────────────────────
-- 1. STATES
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `states` (
  `id`         TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`       VARCHAR(60)      NOT NULL,
  `slug`       VARCHAR(60)      NOT NULL,
  `created_at` TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_states_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────
-- 2. INSTITUTIONS
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `institutions` (
  `id`           SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `state_id`     TINYINT UNSIGNED  NOT NULL,
  `name`         VARCHAR(200)      NOT NULL,
  `abbreviation` VARCHAR(20)       NOT NULL DEFAULT '',
  `type`         ENUM('university','polytechnic','college') NOT NULL,
  `ownership`    ENUM('federal','state','private')          NOT NULL DEFAULT 'federal',
  `is_active`    TINYINT(1)        NOT NULL DEFAULT 1,
  `created_at`   TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_inst_state` (`state_id`),
  CONSTRAINT `fk_inst_state` FOREIGN KEY (`state_id`) REFERENCES `states` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────
-- 3. COMMITTEE MEMBERS
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `committee_members` (
  `id`           MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`         VARCHAR(150)       NOT NULL,
  `title`        VARCHAR(100)       NOT NULL DEFAULT '',  -- e.g. "Comr.", "Dr."
  `role`         ENUM(
                   'super_admin',
                   'state_chair',
                   'secretary_researcher',
                   'field_officer',
                   'media_team',
                   'advisory'
                 ) NOT NULL,
  `position_label` VARCHAR(150)     NOT NULL DEFAULT '', -- human-readable e.g. "Zonal Coordinator"
  `state_id`     TINYINT UNSIGNED   NULL DEFAULT NULL,
  `institution_id` SMALLINT UNSIGNED NULL DEFAULT NULL,
  `phone`        VARCHAR(20)        NOT NULL DEFAULT '',
  `email`        VARCHAR(150)       NOT NULL DEFAULT '',
  `access_code_hash` VARCHAR(255)   NOT NULL DEFAULT '',
  `code_prefix`  VARCHAR(30)        NOT NULL DEFAULT '', -- e.g. SEASWT-OSN
  `photo_url`    VARCHAR(255)       NOT NULL DEFAULT '',
  `bio`          TEXT               NULL,
  `is_active`    TINYINT(1)         NOT NULL DEFAULT 1,
  `failed_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `locked_until` DATETIME           NULL DEFAULT NULL,
  `last_login`   DATETIME           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 `fk_member_state`       (`state_id`),
  KEY `fk_member_institution` (`institution_id`),
  CONSTRAINT `fk_member_state`       FOREIGN KEY (`state_id`)       REFERENCES `states`       (`id`),
  CONSTRAINT `fk_member_institution` FOREIGN KEY (`institution_id`) REFERENCES `institutions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────
-- 4. ASSESSMENT CATEGORIES  (8 × 10 pts = 100)
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `assessment_categories` (
  `id`          TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(100)     NOT NULL,
  `description` TEXT             NULL,
  `max_points`  TINYINT UNSIGNED NOT NULL DEFAULT 10,
  `sort_order`  TINYINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────
-- 5. INSTITUTION SCORES
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `institution_scores` (
  `id`            INT UNSIGNED       NOT NULL AUTO_INCREMENT,
  `institution_id` SMALLINT UNSIGNED NOT NULL,
  `category_id`   TINYINT UNSIGNED   NOT NULL,
  `score`         TINYINT UNSIGNED   NOT NULL DEFAULT 0,
  `assessed_by`   MEDIUMINT UNSIGNED NOT NULL, -- committee_members.id
  `notes`         TEXT               NULL,
  `is_approved`   TINYINT(1)         NOT NULL DEFAULT 0,
  `approved_by`   MEDIUMINT UNSIGNED 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`),
  UNIQUE KEY `uq_score_inst_cat` (`institution_id`, `category_id`),
  KEY `fk_score_inst`     (`institution_id`),
  KEY `fk_score_cat`      (`category_id`),
  KEY `fk_score_assessor` (`assessed_by`),
  CONSTRAINT `fk_score_inst`     FOREIGN KEY (`institution_id`) REFERENCES `institutions`          (`id`),
  CONSTRAINT `fk_score_cat`      FOREIGN KEY (`category_id`)   REFERENCES `assessment_categories` (`id`),
  CONSTRAINT `fk_score_assessor` FOREIGN KEY (`assessed_by`)   REFERENCES `committee_members`     (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────
-- 6. PUBLIC SUBMISSIONS
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `public_submissions` (
  `id`                INT UNSIGNED       NOT NULL AUTO_INCREMENT,
  `reference_code`    VARCHAR(20)        NOT NULL,  -- e.g. RPT-20260001
  `institution_id`    SMALLINT UNSIGNED  NULL DEFAULT NULL,
  `state_id`          TINYINT UNSIGNED   NOT NULL,
  `category`          ENUM(
                        'hostel',
                        'water',
                        'electricity',
                        'security',
                        'health',
                        'funding',
                        'academic',
                        'other'
                      ) NOT NULL DEFAULT 'other',
  `description`       TEXT               NOT NULL,
  `photo_path`        VARCHAR(255)       NOT NULL DEFAULT '',
  `is_anonymous`      TINYINT(1)         NOT NULL DEFAULT 1,
  `submitter_name`    VARCHAR(150)       NOT NULL DEFAULT '',
  `submitter_contact` VARCHAR(150)       NOT NULL DEFAULT '',
  `ip_address`        VARCHAR(45)        NOT NULL DEFAULT '',
  `status`            ENUM('pending','reviewed','published','rejected') NOT NULL DEFAULT 'pending',
  `reviewer_notes`    TEXT               NULL,
  `reviewed_by`       MEDIUMINT UNSIGNED NULL DEFAULT NULL,
  `reviewed_at`       DATETIME           NULL DEFAULT NULL,
  `created_at`        TIMESTAMP          NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ref_code` (`reference_code`),
  KEY `fk_sub_inst`  (`institution_id`),
  KEY `fk_sub_state` (`state_id`),
  CONSTRAINT `fk_sub_inst`  FOREIGN KEY (`institution_id`) REFERENCES `institutions` (`id`),
  CONSTRAINT `fk_sub_state` FOREIGN KEY (`state_id`)       REFERENCES `states`       (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────
-- 7. FOI REQUESTS
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `foi_requests` (
  `id`               SMALLINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `agency`           VARCHAR(200)       NOT NULL,
  `state_id`         TINYINT UNSIGNED   NULL DEFAULT NULL,
  `institution_id`   SMALLINT UNSIGNED  NULL DEFAULT NULL,
  `subject`          VARCHAR(255)       NOT NULL,
  `date_sent`        DATE               NOT NULL,
  `deadline`         DATE               NULL DEFAULT NULL,
  `status`           ENUM(
                       'drafted',
                       'sent',
                       'acknowledged',
                       'responded',
                       'overdue',
                       'escalated',
                       'closed'
                     ) NOT NULL DEFAULT 'drafted',
  `response_summary` TEXT               NULL,
  `document_path`    VARCHAR(255)       NOT NULL DEFAULT '',
  `handled_by`       MEDIUMINT UNSIGNED NOT NULL,
  `notes`            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`),
  KEY `fk_foi_state`   (`state_id`),
  KEY `fk_foi_inst`    (`institution_id`),
  KEY `fk_foi_handler` (`handled_by`),
  CONSTRAINT `fk_foi_state`   FOREIGN KEY (`state_id`)       REFERENCES `states`           (`id`),
  CONSTRAINT `fk_foi_inst`    FOREIGN KEY (`institution_id`) REFERENCES `institutions`     (`id`),
  CONSTRAINT `fk_foi_handler` FOREIGN KEY (`handled_by`)     REFERENCES `committee_members`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────
-- 8. CAMPAIGN GRAPHICS  (6-part series)
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `campaign_graphics` (
  `id`             TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `graphic_number` TINYINT UNSIGNED NOT NULL,
  `title`          VARCHAR(150)     NOT NULL,
  `theme`          VARCHAR(100)     NOT NULL DEFAULT '',
  `hashtags`       VARCHAR(255)     NOT NULL DEFAULT '',
  `status`         ENUM('planned','in_production','published') NOT NULL DEFAULT 'planned',
  `publish_date`   DATE             NULL DEFAULT NULL,
  `asset_path`     VARCHAR(255)     NOT NULL DEFAULT '',
  `notes`          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_graphic_number` (`graphic_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────
-- 9. REPORTS
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `reports` (
  `id`           SMALLINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `title`        VARCHAR(255)       NOT NULL,
  `phase`        TINYINT UNSIGNED   NOT NULL DEFAULT 5,
  `description`  TEXT               NULL,
  `file_path`    VARCHAR(255)       NOT NULL DEFAULT '',
  `published_at` DATETIME           NULL DEFAULT NULL,
  `is_public`    TINYINT(1)         NOT NULL DEFAULT 0,
  `published_by` MEDIUMINT UNSIGNED 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 `fk_report_publisher` (`published_by`),
  CONSTRAINT `fk_report_publisher` FOREIGN KEY (`published_by`) REFERENCES `committee_members` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────
-- 10. ACTIVITY LOG
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `activity_log` (
  `id`        INT UNSIGNED       NOT NULL AUTO_INCREMENT,
  `member_id` MEDIUMINT UNSIGNED NULL DEFAULT NULL,  -- NULL = public/unauthenticated
  `action`    VARCHAR(255)       NOT NULL,
  `detail`    TEXT               NULL,
  `ip_address` VARCHAR(45)       NOT NULL DEFAULT '',
  `created_at` TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_log_member`  (`member_id`),
  KEY `idx_log_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────
-- 11. LOGIN ATTEMPTS  (rate-limiting)
-- ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `login_attempts` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(150) NOT NULL,  -- name/id entered
  `ip_address` VARCHAR(45)  NOT NULL,
  `attempted_at` TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_attempts_ip`         (`ip_address`),
  KEY `idx_attempts_identifier` (`identifier`),
  KEY `idx_attempts_time`       (`attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;