-- Hospital Feedback Management System (HFMS)
-- Database Schema for ALMS Hospital, Kottakkal Malappuram

CREATE DATABASE IF NOT EXISTS almas_fms;
USE almas_fms;

-- departments table
CREATE TABLE IF NOT EXISTS departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- roles table
CREATE TABLE IF NOT EXISTS roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default roles
INSERT INTO roles (name, description) VALUES
('admin', 'System Administrator'),
('hod', 'Department Head'),
('management', 'Management User');

-- users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    role_id INT NOT NULL,
    department_id INT,
    phone VARCHAR(20),
    is_active TINYINT(1) DEFAULT 1,
    failed_login_attempts INT DEFAULT 0,
    locked_until DATETIME,
    must_change_password TINYINT(1) DEFAULT 1,
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- service_parameters table (configurable parameters for each feedback category)
CREATE TABLE IF NOT EXISTS service_parameters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category ENUM('OP', 'IP', 'OTHER') NOT NULL,
    parameter_name_en VARCHAR(255) NOT NULL,
    parameter_name_ml VARCHAR(255),
    display_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default service parameters
INSERT INTO service_parameters (category, parameter_name_en, parameter_name_ml, display_order) VALUES
-- OP Parameters
('OP', 'Registration/Waiting Time', 'രജിസ്ട്രേഷന്റെയും കാത്തിരിക്കലിന്റെയും സമയം', 1),
('OP', 'Doctor Consultation', 'ഡോക്ടറുടെ കമന്റേഷന്‍', 2),
('OP', 'Staff Behavior', 'ജീവനക്കാരുടെ പെരുമാറ്റം', 3),
('OP', 'Facility Cleanliness', 'സൗകര്യത്തിന്റെ വൃത്തികാമകരത', 4),
('OP', 'Pharmacy Service', 'ഫാര്‍മസി സേവനം', 5),
-- IP Parameters
('IP', 'Room Cleanliness', 'മുറിയുടെ വൃത്തികാമകരത', 1),
('IP', 'Nursing Care', 'നഴ്സിംഗ് കെയര്‍', 2),
('IP', 'Doctor Visits', 'ഡോക്ടറുടെ സന്ദര്‍ശനങ്ങള്‍', 3),
('IP', 'Food Quality', 'ഭക്ഷണത്തിന്റെ ഗുണനിലവാരം', 4),
('IP', 'Discharge Process', 'ഡിസ്ചാര്‍ജ് പ്രക്രിയ', 5),
-- OTHER Parameters
('OTHER', 'Overall Experience', 'മൊത്തത്തിലുള്ള അനുഭവം', 1),
('OTHER', 'Staff Helpfulness', 'ജീവനക്കാരുടെ സഹായസന്നദ്ധത', 2),
('OTHER', 'Infrastructure', 'അടിസ്ഥാന സൗകര്യങ്ങള്‍', 3),
('OTHER', 'Timeliness', 'സമയത്തിലുള്ള സേവനം', 4);

-- qr_codes table
CREATE TABLE IF NOT EXISTS qr_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT NOT NULL,
    category ENUM('OP', 'IP', 'OTHER') NOT NULL,
    location_name VARCHAR(255),
    unique_token VARCHAR(64) UNIQUE NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- feedback_sessions table
CREATE TABLE IF NOT EXISTS feedback_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reference_no VARCHAR(20) UNIQUE NOT NULL,
    qr_code_id INT,
    department_id INT NOT NULL,
    category ENUM('OP', 'IP', 'OTHER') NOT NULL,
    respondent_name VARCHAR(255),
    phone VARCHAR(20),
    age_group VARCHAR(20),
    gender ENUM('Male', 'Female', 'Other', 'Prefer not to say'),
    overall_rating TINYINT(1) NOT NULL,
    comments TEXT,
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    data_share_consent TINYINT(1) DEFAULT 0,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_anonymized TINYINT(1) DEFAULT 0,
    FOREIGN KEY (qr_code_id) REFERENCES qr_codes(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- feedback_responses table (individual parameter ratings)
CREATE TABLE IF NOT EXISTS feedback_responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    parameter_id INT NOT NULL,
    rating TINYINT(1) NOT NULL,
    FOREIGN KEY (session_id) REFERENCES feedback_sessions(id),
    FOREIGN KEY (parameter_id) REFERENCES service_parameters(id)
);

-- feedback_reviews table (HOD actions)
CREATE TABLE IF NOT EXISTS feedback_reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id INT NOT NULL,
    reviewed_by INT NOT NULL,
    is_reviewed TINYINT(1) DEFAULT 0,
    action_note TEXT,
    is_escalated TINYINT(1) DEFAULT 0,
    escalation_priority ENUM('Low', 'Medium', 'High') DEFAULT 'Low',
    escalation_note TEXT,
    escalated_at DATETIME,
    reviewed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES feedback_sessions(id),
    FOREIGN KEY (reviewed_by) REFERENCES users(id)
);

-- system_settings table
CREATE TABLE IF NOT EXISTS system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default settings
INSERT INTO system_settings (setting_key, setting_value) VALUES
('hospital_name', 'ALMS Hospital'),
('hospital_address', 'Kottakkal, Malappuram, Kerala'),
('logo_path', ''),
('smtp_host', ''),
('smtp_port', '587'),
('smtp_username', ''),
('smtp_password', ''),
('smtp_from_email', ''),
('smtp_from_name', 'ALMS Hospital'),
('sms_gateway_enabled', '0'),
('sms_gateway_api_key', ''),
('notification_frequency', 'instant'),
('default_language', 'en'),
('feedback_window_hours', '2'),
('session_timeout_minutes', '30'),
('min_password_length', '8');

-- activity_log table
CREATE TABLE IF NOT EXISTS activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Indexes
CREATE INDEX idx_feedback_department ON feedback_sessions(department_id, submitted_at);
CREATE INDEX idx_feedback_rating ON feedback_sessions(overall_rating);
CREATE INDEX idx_feedback_qr ON feedback_sessions(qr_code_id, submitted_at);
CREATE INDEX idx_feedback_reviewed ON feedback_sessions(submitted_at);
CREATE INDEX idx_qr_token ON qr_codes(unique_token);

-- Create default admin user (password: admin123#)
-- Password hash for 'admin123#' with bcrypt cost 10
INSERT INTO users (username, email, password_hash, full_name, role_id) 
VALUES ('admin', 'admin@almshospital.org', '$2y$10$DrA2LjyaeMfbUHycR5lOeeQGum2XSIJW8iWYmdqCnsiQ17i.VBeXO', 'System Administrator', 1);

-- Create sample departments
INSERT INTO departments (name, code, description) VALUES
('General Medicine', 'GENMED', 'General Medicine Department'),
('Surgery', 'SURG', 'Surgical Department'),
('Pediatrics', 'PED', 'Pediatrics Department'),
('Gynecology & Obstetrics', 'OBG', 'Gynecology and Obstetrics Department'),
('Orthopedics', 'ORTHO', 'Orthopedics Department'),
('Cardiology', 'CARDIO', 'Cardiology Department'),
('Emergency', 'EMERG', 'Emergency and Trauma'),
('Pharmacy', 'PHARM', 'Pharmacy Services'),
('Laboratory', 'LAB', 'Laboratory Services'),
('Radiology', 'RAD', 'Radiology and Imaging'),
('Casualty', 'CAS', 'Casualty Department'),
('OPD', 'OPD', 'Outpatient Department');