/********************************************************
* PROJECT: CENTRALIZED NYSC MANAGEMENT SYSTEM
*
* DATABASE: nmis_db
*
* ENGINE: MySQL 8+
*
* CHARACTER SET: utf8mb4
*
* COLLATION: utf8mb4_general_ci
*
**********************************************************/

-- ============================================================
-- TABLE: activity_members
-- ============================================================

CREATE TABLE activity_members (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    activity_id BIGINT UNSIGNED NOT NULL,

    corps_member_id BIGINT UNSIGNED NOT NULL,

    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(activity_id, corps_member_id),

    FOREIGN KEY(activity_id)
        REFERENCES camp_activities(id)
        ON DELETE CASCADE,

    FOREIGN KEY(corps_member_id)
        REFERENCES corps_members(id)
        ON DELETE CASCADE

);

-- ============================================================
-- TABLE: monthly_clearance
-- ============================================================

DROP TABLE IF EXISTS monthly_clearance;

CREATE TABLE monthly_clearance (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    corps_member_id BIGINT UNSIGNED NOT NULL,

    clearance_month INT NOT NULL,

    clearance_year YEAR NOT NULL,

    cds_attendance_verified ENUM(
        'Yes',
        'No'
    ) DEFAULT 'No',

    camp_activity_verified ENUM(
        'Yes',
        'No'
    ) DEFAULT 'No',

    biometric_verified ENUM(
        'Yes',
        'No'
    ) DEFAULT 'No',

    submitted_date DATETIME,

    approval_date DATETIME,

    approved_by BIGINT UNSIGNED,

    status ENUM(

        'Pending',

        'Approved',

        'Rejected'

    ) DEFAULT 'Pending',

    remarks TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE(corps_member_id, clearance_month, clearance_year),

    CONSTRAINT fk_clearance_member
        FOREIGN KEY (corps_member_id)
        REFERENCES corps_members(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_clearance_approver
        FOREIGN KEY (approved_by)
        REFERENCES users(id)
        ON DELETE SET NULL

);



-- ============================================================
-- TABLE: clearance_logs
-- ============================================================

DROP TABLE IF EXISTS clearance_logs;

CREATE TABLE clearance_logs (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    clearance_id BIGINT UNSIGNED NOT NULL,

    action VARCHAR(100),

    previous_status VARCHAR(50),

    new_status VARCHAR(50),

    remarks TEXT,

    action_by BIGINT UNSIGNED,

    action_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_clearance_log_clearance
        FOREIGN KEY (clearance_id)
        REFERENCES monthly_clearance(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_clearance_log_user
        FOREIGN KEY (action_by)
        REFERENCES users(id)
        ON DELETE SET NULL

);



-- ============================================================
-- TABLE: clearance_approvals
-- ============================================================

DROP TABLE IF EXISTS clearance_approvals;

CREATE TABLE clearance_approvals (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    clearance_id BIGINT UNSIGNED NOT NULL,

    approver_id BIGINT UNSIGNED NOT NULL,

    approval_level ENUM(

        'Camp Officer',

        'LGI',

        'Administrator'

    ) NOT NULL,

    decision ENUM(

        'Approved',

        'Rejected'

    ) NOT NULL,

    comments TEXT,

    approval_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_approval_clearance
        FOREIGN KEY (clearance_id)
        REFERENCES monthly_clearance(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_approval_user
        FOREIGN KEY (approver_id)
        REFERENCES users(id)
        ON DELETE CASCADE

);



-- ============================================================
-- TABLE: qr_attendance_sessions
-- ============================================================

DROP TABLE IF EXISTS qr_attendance_sessions;

CREATE TABLE qr_attendance_sessions (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    cds_group_id BIGINT UNSIGNED NOT NULL,

    meeting_title VARCHAR(200),

    session_token VARCHAR(255) UNIQUE NOT NULL,

    session_date DATE NOT NULL,

    meeting_start DATETIME NOT NULL,

    meeting_end DATETIME NOT NULL,

    valid_from DATETIME,

    valid_until DATETIME,

    status ENUM(
        'Pending',
        'Active',
        'Expired',
        'Closed'
    ) DEFAULT 'Pending',

    allow_late_entry ENUM(
        'Yes',
        'No'
    ) DEFAULT 'Yes',

    late_after_minutes INT DEFAULT 30,

    generated_by BIGINT UNSIGNED,

    attendance_mode ENUM(
        'QR',
        'Manual',
        'Both'
    ) DEFAULT 'QR',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX(cds_group_id),

    CONSTRAINT fk_qr_group
        FOREIGN KEY (cds_group_id)
        REFERENCES cds_groups(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_qr_generator
        FOREIGN KEY (generated_by)
        REFERENCES users(id)
        ON DELETE SET NULL
);

-- ============================================================
-- TABLE: qr_scan_logs
-- ============================================================

DROP TABLE IF EXISTS qr_scan_logs;

CREATE TABLE qr_scan_logs(

id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

attendance_session_id BIGINT UNSIGNED,

corps_member_id BIGINT UNSIGNED,

scan_time DATETIME,

scan_status ENUM(

'Success',

'Already Marked',

'Expired QR',

'Outside Time',

'Invalid QR',

'Wrong CDS Group'

),

ip_address VARCHAR(100),

device_info VARCHAR(255),

gps_latitude DECIMAL(10,8),

gps_longitude DECIMAL(11,8),

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_scan_session
FOREIGN KEY(attendance_session_id)
REFERENCES qr_attendance_sessions(id)
ON DELETE CASCADE,

CONSTRAINT fk_scan_member
FOREIGN KEY(corps_member_id)
REFERENCES corps_members(id)
ON DELETE CASCADE

);



-- ============================================================
-- TABLE: announcements
-- ============================================================

DROP TABLE IF EXISTS announcements;

CREATE TABLE announcements (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(255) NOT NULL,

    announcement TEXT NOT NULL,

    target_role ENUM(

        'All',

        'Administrator',

        'Camp Officer',

        'LGI',

        'Corps Member'

    ) DEFAULT 'All',

    publish_date DATE,

    expiry_date DATE,

    created_by BIGINT UNSIGNED,

    status ENUM(

        'Published',

        'Draft',

        'Archived'

    ) DEFAULT 'Published',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_announcement_creator
        FOREIGN KEY (created_by)
        REFERENCES users(id)
        ON DELETE SET NULL

);

-- ============================================================
-- TABLE: password_reset_tokens
-- ============================================================

DROP TABLE IF EXISTS password_reset_tokens;

CREATE TABLE password_reset_tokens (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    email VARCHAR(150) NOT NULL,

    token VARCHAR(255) NOT NULL,

    expires_at DATETIME NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX(email)

);



-- ============================================================
-- TABLE: login_history
-- ============================================================

DROP TABLE IF EXISTS login_history;

CREATE TABLE login_history (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL,

    login_time DATETIME,

    logout_time DATETIME,

    ip_address VARCHAR(100),

    browser TEXT,

    operating_system VARCHAR(100),

    login_status ENUM(
        'Success',
        'Failed'
    ) DEFAULT 'Success',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_login_history_user
        FOREIGN KEY(user_id)
        REFERENCES users(id)
        ON DELETE CASCADE

);



-- ============================================================
-- TABLE: corps_postings
-- ============================================================

DROP TABLE IF EXISTS corps_postings;

CREATE TABLE corps_postings (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    corps_member_id BIGINT UNSIGNED NOT NULL,

    state_id INT UNSIGNED,

    lga_id BIGINT UNSIGNED,

    ppa_name VARCHAR(255) NOT NULL,

    ppa_address TEXT,

    supervisor_name VARCHAR(150),

    supervisor_phone VARCHAR(20),

    supervisor_email VARCHAR(150),

    posting_date DATE,

    resumption_date DATE,

    status ENUM(
        'Posted',
        'Rejected',
        'Relocated',
        'Completed'
    ) DEFAULT 'Posted',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_posting_member
        FOREIGN KEY(corps_member_id)
        REFERENCES corps_members(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_posting_state
        FOREIGN KEY(state_id)
        REFERENCES states(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_posting_lga
        FOREIGN KEY(lga_id)
        REFERENCES local_governments(id)
        ON DELETE SET NULL

);



-- ============================================================
-- TABLE: allowances
-- ============================================================

DROP TABLE IF EXISTS allowances;

CREATE TABLE allowances (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    corps_member_id BIGINT UNSIGNED NOT NULL,

    allowance_month INT,

    allowance_year YEAR,

    amount DECIMAL(12,2) DEFAULT 0.00,

    payment_date DATE,

    payment_reference VARCHAR(120),

    payment_status ENUM(

        'Pending',

        'Paid',

        'Failed'

    ) DEFAULT 'Pending',

    remarks TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_allowance_member
        FOREIGN KEY(corps_member_id)
        REFERENCES corps_members(id)
        ON DELETE CASCADE

);


-- ============================================================
-- TABLE: Events
-- ============================================================

CREATE TABLE events (

    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    organizer_id BIGINT,
    target_role VARCHAR(50) DEFAULT 'all',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- TABLE: cds_executives
-- ============================================================

DROP TABLE IF EXISTS cds_executives;

CREATE TABLE cds_executives(

id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

cds_group_id BIGINT UNSIGNED,

corps_member_id BIGINT UNSIGNED,

position VARCHAR(150),

can_mark_attendance ENUM(
'Yes',
'No'
)
DEFAULT 'Yes',

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

UNIQUE(cds_group_id,corps_member_id),

CONSTRAINT fk_exec_group
FOREIGN KEY(cds_group_id)
REFERENCES cds_groups(id)
ON DELETE CASCADE,

CONSTRAINT fk_exec_member
FOREIGN KEY(corps_member_id)
REFERENCES corps_members(id)
ON DELETE CASCADE

);


-- ============================================================
-- TABLE: attendance_settings
-- ============================================================

DROP TABLE IF EXISTS attendance_settings;

CREATE TABLE attendance_settings(

id INT AUTO_INCREMENT PRIMARY KEY,

attendance_radius INT DEFAULT 100,

attendance_opens_before INT DEFAULT 30,

attendance_closes_after INT DEFAULT 60,

allow_multiple_scan ENUM(
'Yes',
'No'
)
DEFAULT 'No',

require_location ENUM(
'Yes',
'No'
)
DEFAULT 'No',

require_qr ENUM(
'Yes',
'No'
)
DEFAULT 'Yes',

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP

);

INSERT INTO attendance_settings(

attendance_radius,

attendance_opens_before,

attendance_closes_after,

allow_multiple_scan,

require_location,

require_qr

)

VALUES(

100,

30,

60,

'No',

'No',

'Yes'

);

-- ============================================================
-- TABLE: attendance_excuses
-- ============================================================

DROP TABLE IF EXISTS attendance_excuses;

CREATE TABLE attendance_excuses(

id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

attendance_id BIGINT UNSIGNED,

corps_member_id BIGINT UNSIGNED,

reason TEXT,

attachment VARCHAR(255),

status ENUM(

'Pending',

'Approved',

'Rejected'

)

DEFAULT 'Pending',

reviewed_by BIGINT UNSIGNED,

reviewed_at DATETIME,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_excuse_attendance
FOREIGN KEY(attendance_id)
REFERENCES cds_attendance(id)
ON DELETE CASCADE,

CONSTRAINT fk_excuse_member
FOREIGN KEY(corps_member_id)
REFERENCES corps_members(id)
ON DELETE CASCADE,

CONSTRAINT fk_excuse_user
FOREIGN KEY(reviewed_by)
REFERENCES users(id)
ON DELETE SET NULL

);


-- ============================================================
-- TABLE: attendance_logs
-- ============================================================

DROP TABLE IF EXISTS attendance_logs;

CREATE TABLE attendance_logs(

id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

attendance_id BIGINT UNSIGNED,

action VARCHAR(150),

performed_by BIGINT UNSIGNED,

remarks TEXT,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

CONSTRAINT fk_att_log
FOREIGN KEY(attendance_id)
REFERENCES cds_attendance(id)
ON DELETE CASCADE,

CONSTRAINT fk_att_user
FOREIGN KEY(performed_by)
REFERENCES users(id)
ON DELETE SET NULL

);


-- ============================================================
-- TABLE: leave_applications
-- ============================================================

DROP TABLE IF EXISTS leave_applications;

CREATE TABLE leave_applications (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    corps_member_id BIGINT UNSIGNED NOT NULL,

    leave_type ENUM(

        'Medical',

        'Official',

        'Compassionate',

        'Emergency'

    ) DEFAULT 'Official',

    start_date DATE,

    end_date DATE,

    reason TEXT,

    supporting_document VARCHAR(255),

    approved_by BIGINT UNSIGNED,

    approval_date DATETIME,

    status ENUM(

        'Pending',

        'Approved',

        'Rejected'

    ) DEFAULT 'Pending',

    remarks TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_leave_member
        FOREIGN KEY(corps_member_id)
        REFERENCES corps_members(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_leave_user
        FOREIGN KEY(approved_by)
        REFERENCES users(id)
        ON DELETE SET NULL

);



-- ============================================================
-- TABLE: relocation_requests
-- ============================================================

DROP TABLE IF EXISTS relocation_requests;

CREATE TABLE relocation_requests (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    corps_member_id BIGINT UNSIGNED NOT NULL,

    current_state_id INT UNSIGNED,

    requested_state_id INT UNSIGNED,

    reason TEXT,

    evidence VARCHAR(255),

    requested_date DATE,

    approved_date DATE,

    approved_by BIGINT UNSIGNED,

    status ENUM(

        'Pending',

        'Approved',

        'Rejected'

    ) DEFAULT 'Pending',

    remarks TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_relocation_member
        FOREIGN KEY(corps_member_id)
        REFERENCES corps_members(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_current_state
        FOREIGN KEY(current_state_id)
        REFERENCES states(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_requested_state
        FOREIGN KEY(requested_state_id)
        REFERENCES states(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_relocation_user
        FOREIGN KEY(approved_by)
        REFERENCES users(id)
        ON DELETE SET NULL

);



-- ============================================================
-- TABLE: reports
-- ============================================================

DROP TABLE IF EXISTS reports;

CREATE TABLE reports (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    report_name VARCHAR(255),

    report_type ENUM(

        'Attendance',

        'Clearance',

        'Camp',

        'CDS',

        'Posting',

        'Allowance',

        'General'

    ) DEFAULT 'General',

    report_file VARCHAR(255),

    generated_by BIGINT UNSIGNED,

    generated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_report_user
        FOREIGN KEY(generated_by)
        REFERENCES users(id)
        ON DELETE SET NULL

);

-- ============================================================
-- ALTER THE CDS ATTENDANCE
-- ============================================================
ALTER TABLE cds_attendance

ADD COLUMN attendance_session_id BIGINT UNSIGNED NULL
AFTER corps_member_id,

ADD COLUMN gps_latitude DECIMAL(10,8) NULL
AFTER attendance_time,

ADD COLUMN gps_longitude DECIMAL(11,8) NULL
AFTER gps_latitude,

ADD COLUMN device_info VARCHAR(255) NULL
AFTER gps_longitude;

ALTER TABLE cds_attendance

ADD CONSTRAINT fk_attendance_session
FOREIGN KEY(attendance_session_id)
REFERENCES qr_attendance_sessions(id)
ON DELETE SET NULL;



-- ============================================================
-- ADDITIONAL PERFORMANCE INDEXES
-- ============================================================

CREATE INDEX idx_users_email
ON users(email);

CREATE INDEX idx_users_role
ON users(role_id);

CREATE INDEX idx_members_statecode
ON corps_members(state_code);

CREATE INDEX idx_members_status
ON corps_members(status);

CREATE INDEX idx_members_batch
ON corps_members(batch);

CREATE INDEX idx_clearance_status
ON monthly_clearance(status);

CREATE INDEX idx_clearance_month
ON monthly_clearance(clearance_month);

CREATE INDEX idx_cds_date
ON cds_attendance(attendance_date);

CREATE INDEX idx_allowance_month
ON allowances(allowance_month);

CREATE INDEX idx_posting_status
ON corps_postings(status);

CREATE INDEX idx_notification_read
ON notifications(is_read);

CREATE INDEX idx_session_date
ON qr_attendance_sessions(session_date);

CREATE INDEX idx_session_status
ON qr_attendance_sessions(status);

CREATE INDEX idx_scan_status
ON qr_scan_logs(scan_status);

CREATE INDEX idx_scan_member
ON qr_scan_logs(corps_member_id);

CREATE INDEX idx_attendance_session
ON cds_attendance(attendance_session_id);



-- ============================================================
-- SAMPLE SYSTEM ANNOUNCEMENT
-- ============================================================

INSERT INTO announcements
(
title,
announcement,
target_role,
publish_date,
status
)
VALUES
(
'Welcome to NMIS',
'Welcome to the Centralized NYSC Management Information System. Please complete your profile after logging in.',
'All',
CURDATE(),
'Published'
);



-- ============================================================
-- SAMPLE NOTIFICATION
-- ============================================================

INSERT INTO notifications
(
user_id,
title,
message,
notification_type
)
VALUES
(
1,
'Welcome',
'Welcome to CNMIS Administrator Dashboard.',
'Success'
);

-- ============================================================
-- ALTER THE CDS GROUPS
-- ============================================================

ALTER TABLE cds_groups

ADD COLUMN attendance_type ENUM(
'QR',
'Manual',
'Both'
)
DEFAULT 'QR'

AFTER description;



-- ============================================================
-- FINALIZE DATABASE
-- ============================================================

SET FOREIGN_KEY_CHECKS = 1;

COMMIT;