-- Create Database
CREATE DATABASE IF NOT EXISTS grievance_system;
USE grievance_system;

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Admin Table
CREATE TABLE IF NOT EXISTS admins (
    admin_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    auth_level TINYINT NOT NULL COMMENT '1=Provincial, 2=Zonal, 3=Subject Clerk',
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Grievances Table
CREATE TABLE IF NOT EXISTS grievances (
    grievance_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    service_category ENUM('Principal', 'Teacher', 'Non Academic') NOT NULL,
    school VARCHAR(150) NOT NULL,
    nature_of_grief VARCHAR(100) NOT NULL,
    grief_description TEXT NOT NULL,
    period_of_distress INT NOT NULL COMMENT 'In months',
    zonal VARCHAR(50) NOT NULL,
    is_special_category BOOLEAN DEFAULT FALSE,
    status ENUM('Pending', 'Processing', 'Sent to Zonal', 'Completed') DEFAULT 'Pending',
    action_taken TEXT,
    document_submission_date DATETIME,
    assigned_to_zonal BOOLEAN DEFAULT FALSE,
    zonal_action TEXT,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    completed_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- Required Documents Table
CREATE TABLE IF NOT EXISTS required_documents (
    doc_req_id INT PRIMARY KEY AUTO_INCREMENT,
    grievance_id INT NOT NULL,
    medical_report BOOLEAN DEFAULT FALSE,
    educational_certificates BOOLEAN DEFAULT FALSE,
    professional_certificates BOOLEAN DEFAULT FALSE,
    request_letter BOOLEAN DEFAULT FALSE,
    other_documents BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (grievance_id) REFERENCES grievances(grievance_id) ON DELETE CASCADE
);

-- Uploaded Documents Table
CREATE TABLE IF NOT EXISTS uploaded_documents (
    upload_id INT PRIMARY KEY AUTO_INCREMENT,
    grievance_id INT NOT NULL,
    document_type VARCHAR(50) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (grievance_id) REFERENCES grievances(grievance_id) ON DELETE CASCADE
);

-- Feedback Table
CREATE TABLE IF NOT EXISTS feedback (
    feedback_id INT PRIMARY KEY AUTO_INCREMENT,
    grievance_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    comments TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (grievance_id) REFERENCES grievances(grievance_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- Weekly Submission Counter Table
CREATE TABLE IF NOT EXISTS weekly_submissions (
    week_id INT PRIMARY KEY AUTO_INCREMENT,
    week_start DATE NOT NULL,
    week_end DATE NOT NULL,
    principal_count INT DEFAULT 0,
    teacher_count INT DEFAULT 0,
    non_academic_count INT DEFAULT 0,
    total_count INT DEFAULT 0,
    UNIQUE KEY unique_week (week_start, week_end)
);

-- Yearly Submission Counter Table
CREATE TABLE IF NOT EXISTS yearly_submissions (
    user_id INT NOT NULL,
    year INT NOT NULL,
    submission_count INT DEFAULT 0,
    PRIMARY KEY (user_id, year),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- Insert Default Admin (Password: admin123)
INSERT INTO admins (username, password, full_name, auth_level, email) 
VALUES ('admin_provincial', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Provincial Admin', 1, 'provincial@admin.com');

INSERT INTO admins (username, password, full_name, auth_level, email) 
VALUES ('admin_zonal', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Zonal Admin', 2, 'zonal@admin.com');

INSERT INTO admins (username, password, full_name, auth_level, email) 
VALUES ('clerk_subject', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Subject Clerk', 3, 'clerk@admin.com');