-- SPCHS Version 1.0 complete installation file
-- Import this file directly in phpMyAdmin.
-- 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 tecgazec_hardware
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE tecgazec_hardware;

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;
USE tecgazec_hardware;

INSERT INTO unit_master
    (id, unit_name, description, display_order, is_required, status)
VALUES
    (1, 'Computer Lab', 'Computer Laboratory', 1, 'Yes', 'Active'),
    (2, 'Language Unit', 'Language Laboratory', 2, 'Yes', 'Active'),
    (3, 'Media Unit', 'Media Resource Unit', 3, 'Yes', 'Active'),
    (4, 'Office', 'School Office', 4, 'Yes', 'Active')
ON DUPLICATE KEY UPDATE
    description = VALUES(description),
    display_order = VALUES(display_order),
    is_required = VALUES(is_required),
    status = VALUES(status);
USE tecgazec_hardware;

INSERT INTO equipment_master
    (id, equipment_name, display_order, status)
VALUES
    (1, 'Desktop', 1, 'Active'),
    (2, 'Laptop', 2, 'Active'),
    (3, 'Tab', 3, 'Active'),
    (4, 'Smart Board', 4, 'Active'),
    (5, 'Smart TV', 5, 'Active'),
    (6, 'UPS', 6, 'Active'),
    (7, 'Telephone', 7, 'Active'),
    (8, 'Internet', 8, 'Active')
ON DUPLICATE KEY UPDATE
    display_order = VALUES(display_order),
    status = VALUES(status);
USE tecgazec_hardware;

-- Default mapping reconstructed from the final wizard architecture.
-- It can be changed later through Admin > Equipment Management.

INSERT INTO unit_equipment
    (unit_id, equipment_id, display_order, status)
VALUES
    (1, 1, 1, 'Active'),
    (1, 2, 2, 'Active'),
    (1, 3, 3, 'Active'),
    (1, 4, 4, 'Active'),
    (1, 5, 5, 'Active'),
    (1, 6, 6, 'Active'),
    (1, 8, 7, 'Active'),

    (2, 1, 1, 'Active'),
    (2, 2, 2, 'Active'),
    (2, 3, 3, 'Active'),
    (2, 4, 4, 'Active'),
    (2, 5, 5, 'Active'),
    (2, 6, 6, 'Active'),
    (2, 8, 7, 'Active'),

    (3, 1, 1, 'Active'),
    (3, 2, 2, 'Active'),
    (3, 3, 3, 'Active'),
    (3, 4, 4, 'Active'),
    (3, 5, 5, 'Active'),
    (3, 6, 6, 'Active'),
    (3, 8, 7, 'Active'),

    (4, 1, 1, 'Active'),
    (4, 2, 2, 'Active'),
    (4, 3, 3, 'Active'),
    (4, 4, 4, 'Active'),
    (4, 5, 5, 'Active'),
    (4, 6, 6, 'Active'),
    (4, 7, 7, 'Active'),
    (4, 8, 8, 'Active')
ON DUPLICATE KEY UPDATE
    display_order = VALUES(display_order),
    status = VALUES(status);
USE tecgazec_hardware;

-- Temporary administrator account
-- Email: admin@spchis.local
-- Password: Admin@12345
-- Change the email and password immediately after first login.

INSERT INTO users
    (id, fullname, email, phone, workplace, zone_name, password, role, status)
VALUES
    (
        1,
        'System Administrator',
        'admin@spchis.local',
        '',
        'Provincial Education Department',
        NULL,
        '$2y$12$V3Pz0iPLIEOZgicNntiPX.opB95l6SdxQxrILBo.jYdVg/j4zKz/q',
        'admin',
        'Active'
    )
ON DUPLICATE KEY UPDATE
    fullname = VALUES(fullname),
    role = 'admin',
    status = 'Active';
