-- 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');

-- 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);

-- 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)
);

-- 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 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');
