-- SPCHS Version 1.0 - Final Database Structure
-- Compatible with MariaDB 10.6+ and MySQL 8+
-- Database: spchs

SET NAMES utf8mb4;
SET time_zone = '+05:30';
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS spchs
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE spchs;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    fullname VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL,
    phone VARCHAR(30) NOT NULL DEFAULT '',
    workplace VARCHAR(190) NOT NULL DEFAULT '',
    zone_name VARCHAR(100) DEFAULT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','zonal_director','user') NOT NULL DEFAULT 'user',
    status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    reset_token VARCHAR(64) DEFAULT NULL,
    reset_expiry DATETIME 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_users_email (email),
    UNIQUE KEY uq_users_reset_token (reset_token),
    KEY idx_users_role_status (role, status),
    KEY idx_users_zone (zone_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS schools (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    census_no VARCHAR(50) NOT NULL,
    district VARCHAR(100) NOT NULL,
    zone_name VARCHAR(100) NOT NULL,
    division_name VARCHAR(100) NOT NULL,
    school_name VARCHAR(255) NOT NULL,
    school_category VARCHAR(50) NOT NULL DEFAULT '',
    school_type VARCHAR(50) NOT NULL DEFAULT '',
    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_schools_census_no (census_no),
    KEY idx_schools_district (district),
    KEY idx_schools_zone (zone_name),
    KEY idx_schools_division (division_name),
    KEY idx_schools_name (school_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS equipment_master (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    equipment_name VARCHAR(100) NOT NULL,
    display_order INT NOT NULL DEFAULT 0,
    status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    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_equipment_name (equipment_name),
    KEY idx_equipment_status_order (status, display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unit_master (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    unit_name VARCHAR(100) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    display_order INT NOT NULL DEFAULT 1,
    is_required ENUM('Yes','No') NOT NULL DEFAULT 'Yes',
    status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    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_unit_name (unit_name),
    KEY idx_unit_status_required_order (status, is_required, display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unit_equipment (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    unit_id INT UNSIGNED NOT NULL,
    equipment_id INT UNSIGNED NOT NULL,
    display_order INT NOT NULL DEFAULT 1,
    status ENUM('Active','Inactive') NOT NULL DEFAULT 'Active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_unit_equipment (unit_id, equipment_id),
    KEY idx_unit_equipment_equipment (equipment_id),
    KEY idx_unit_equipment_status_order (unit_id, status, display_order),
    CONSTRAINT fk_unit_equipment_unit
        FOREIGN KEY (unit_id) REFERENCES unit_master(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_unit_equipment_equipment
        FOREIGN KEY (equipment_id) REFERENCES equipment_master(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS surveys (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    survey_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    entered_by VARCHAR(150) NOT NULL,
    census_no VARCHAR(50) NOT NULL,
    district VARCHAR(100) NOT NULL,
    zone_name VARCHAR(100) NOT NULL,
    division_name VARCHAR(100) NOT NULL,
    school_name VARCHAR(255) NOT NULL,
    school_category VARCHAR(50) NOT NULL DEFAULT '',
    school_type VARCHAR(50) NOT NULL DEFAULT '',
    principal_name VARCHAR(255) NOT NULL DEFAULT '',
    principal_phone VARCHAR(20) NOT NULL DEFAULT '',
    school_phone VARCHAR(20) NOT NULL DEFAULT '',
    it_teacher_name VARCHAR(255) NOT NULL DEFAULT '',
    it_teacher_phone VARCHAR(20) NOT NULL DEFAULT '',
    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_surveys_date (survey_date),
    KEY idx_surveys_census_date (census_no, survey_date),
    KEY idx_surveys_zone (zone_name),
    KEY idx_surveys_district (district),
    KEY idx_surveys_division (division_name),
    KEY idx_surveys_entered_by (entered_by),
    KEY idx_surveys_school_name (school_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS survey_equipment_details (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    survey_id INT UNSIGNED NOT NULL,
    section VARCHAR(100) NOT NULL,
    equipment_id INT UNSIGNED NOT NULL,
    working INT UNSIGNED NOT NULL DEFAULT 0,
    not_working INT UNSIGNED NOT NULL DEFAULT 0,
    repair INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_survey_section_equipment (survey_id, section, equipment_id),
    KEY idx_survey_equipment_id (equipment_id),
    KEY idx_survey_section (survey_id, section),
    CONSTRAINT fk_survey_equipment_survey
        FOREIGN KEY (survey_id) REFERENCES surveys(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_survey_equipment_equipment
        FOREIGN KEY (equipment_id) REFERENCES equipment_master(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS survey_other_units (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    survey_id INT UNSIGNED NOT NULL,
    unit_name VARCHAR(150) NOT NULL,
    details TEXT DEFAULT NULL,
    display_order INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_other_unit_survey (survey_id),
    CONSTRAINT fk_other_unit_survey
        FOREIGN KEY (survey_id) REFERENCES surveys(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS survey_other_equipment (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    other_unit_id INT UNSIGNED NOT NULL,
    equipment_name VARCHAR(150) NOT NULL,
    working INT UNSIGNED NOT NULL DEFAULT 0,
    not_working INT UNSIGNED NOT NULL DEFAULT 0,
    repair INT UNSIGNED NOT NULL DEFAULT 0,
    display_order INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_other_equipment_unit (other_unit_id),
    CONSTRAINT fk_other_equipment_unit
        FOREIGN KEY (other_unit_id) REFERENCES survey_other_units(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    log_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_name VARCHAR(100) NOT NULL,
    role VARCHAR(50) NOT NULL,
    module VARCHAR(100) NOT NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_activity_logs_time (log_time),
    KEY idx_activity_logs_user (user_name),
    KEY idx_activity_logs_role (role),
    KEY idx_activity_logs_module (module),
    KEY idx_activity_logs_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
