-- ============================================
-- Smart KPIs for HR v2 - Database Schema
-- تجمع جدة الصحي الثاني
-- ============================================

CREATE DATABASE IF NOT EXISTS smartkpi_hr CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE smartkpi_hr;

-- ============================================
-- جدول المستشفيات / الجهات
-- ============================================
DROP TABLE IF EXISTS activity_log;
DROP TABLE IF EXISTS user_hospital_permissions;
DROP TABLE IF EXISTS kpi_staff_turnover;
DROP TABLE IF EXISTS kpi_ms7;
DROP TABLE IF EXISTS kpi_annual_vacation;
DROP TABLE IF EXISTS kpi_medical_insurance;
DROP TABLE IF EXISTS kpi_dpc;
DROP TABLE IF EXISTS kpi_sick_leave;
DROP TABLE IF EXISTS kpi_bls;
DROP TABLE IF EXISTS kpi_professional_classification;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS hospitals;

CREATE TABLE hospitals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    name_en VARCHAR(200),
    short_name VARCHAR(80),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO hospitals (id, code, name_ar, name_en, short_name) VALUES
(1, 'KAH',   'مجمع الملك عبدالله الطبي', 'King Abdullah Medical Complex', 'مجمع الملك عبدالله'),
(2, 'KAI',   'مراكز مراقبة مطار الملك عبدالعزيز الدولي', 'King Abdulaziz Airport Health Centers', 'مراكز المطار'),
(3, 'IRADA', 'مجمع إرادة والصحة النفسية', 'Irada & Mental Health Complex', 'مجمع إرادة'),
(4, 'AZIZ',  'مستشفى العزيزية للأطفال', 'Al-Aziziyah Children Hospital', 'مستشفى العزيزية'),
(5, 'EYES',  'مستشفى العيون بجدة', 'Eye Hospital Jeddah', 'مستشفى العيون'),
(6, 'KFH',   'مستشفى الملك فهد العام', 'King Fahad General Hospital', 'مستشفى الملك فهد'),
(7, 'RABIGH','مستشفى رابغ العام', 'Rabigh General Hospital', 'مستشفى رابغ'),
(8, 'HQ',    'المقر الرئيسي', 'Headquarters', 'المقر الرئيسي'),
(9, 'WCHH',  'مستشفى الولادة والأطفال التخصصي', 'Specialized Women & Children Hospital', 'مستشفى الولادة');

-- ============================================
-- جدول المستخدمين
-- ============================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(200) NOT NULL,
    email VARCHAR(200),
    gender ENUM('male','female') DEFAULT 'male',
    role ENUM('super_admin','admin','viewer','data_entry') DEFAULT 'viewer',
    hospital_id INT NULL,
    is_active TINYINT(1) DEFAULT 1,
    last_login DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE SET NULL
);

-- Default admin (password: Admin@12345)
INSERT INTO users (username, password_hash, full_name, email, role, hospital_id) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'مدير النظام', 'admin@health.sa', 'super_admin', NULL);

-- ============================================
-- صلاحيات المستخدمين على الجهات
-- ============================================
CREATE TABLE user_hospital_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    hospital_id INT NOT NULL,
    can_view TINYINT(1) DEFAULT 1,
    can_edit TINYINT(1) DEFAULT 0,
    can_export TINYINT(1) DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_hospital (user_id, hospital_id)
);

-- ============================================
-- مؤشر التصنيف المهني (P.C) - بيانات حقيقية من الاكسل
-- ============================================
CREATE TABLE kpi_professional_classification (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hospital_id INT NOT NULL,
    report_date DATE NOT NULL,
    total_staff INT DEFAULT 0,
    expired_classification INT DEFAULT 0,
    valid_classification INT DEFAULT 0,
    achievement_rate DECIMAL(10,6) DEFAULT 0,
    notes TEXT,
    entered_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id),
    FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE SET NULL
);

-- بيانات حقيقية من ملفات الاكسل (ورقة P.C)
INSERT INTO kpi_professional_classification (hospital_id, report_date, total_staff, expired_classification, valid_classification, achievement_rate) VALUES
(6, '2025-11-01', 4021, 4,  4017, 0.999005),
(1, '2025-11-01', 4139, 27, 4112, 0.993477),
(5, '2025-11-01', 448,  1,  447,  0.997768),
(4, '2025-11-01', 512,  55, 457,  0.892578),
(7, '2025-11-01', 653,  1,  652,  0.998469),
(3, '2025-11-01', 653,  4,  649,  0.993874),
(2, '2025-11-01', 471,  0,  471,  1.000000);

-- ============================================
-- مؤشر شهادات BLS - بيانات حقيقية
-- ============================================
CREATE TABLE kpi_bls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hospital_id INT NOT NULL,
    report_date DATE NOT NULL,
    total_staff INT DEFAULT 0,
    expired_certificates INT DEFAULT 0,
    valid_certificates INT DEFAULT 0,
    achievement_rate DECIMAL(10,6) DEFAULT 0,
    notes TEXT,
    entered_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id),
    FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE SET NULL
);

-- بيانات حقيقية من الاكسل - ورقة BLS
INSERT INTO kpi_bls (hospital_id, report_date, total_staff, expired_certificates, valid_certificates, achievement_rate) VALUES
(6, '2025-11-01', 4021, 0,   4021, 1.000000),
(1, '2025-11-01', 4113, 239, 3874, 0.941892),
(5, '2025-11-01', 448,  9,   439,  0.979911),
(4, '2025-11-01', 513,  41,  472,  0.920078),
(7, '2025-11-01', 653,  1,   652,  0.998469),
(3, '2025-11-01', 653,  23,  630,  0.964778),
(2, '2025-11-01', 471,  0,   471,  1.000000);

-- ============================================
-- مؤشر الإجازات المرضية > 30 يوم - بيانات حقيقية
-- ============================================
CREATE TABLE kpi_sick_leave (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hospital_id INT NOT NULL,
    report_date DATE NOT NULL,
    staff_type ENUM('doctors','nursing','pharmacists','other') NOT NULL,
    submitted_to_medical_board INT DEFAULT 0,
    approved_by_medical_board INT DEFAULT 0,
    rejected_by_medical_board INT DEFAULT 0,
    referred_to_compliance INT DEFAULT 0,
    total INT DEFAULT 0,
    notes TEXT,
    entered_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id),
    FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE SET NULL
);

-- بيانات حقيقية من ورقة "Sick Leaves > 30 days"
-- مجمع الملك عبدالله الطبي
INSERT INTO kpi_sick_leave (hospital_id,report_date,staff_type,submitted_to_medical_board,approved_by_medical_board,rejected_by_medical_board,referred_to_compliance,total) VALUES
(1,'2025-11-01','doctors',    3, 2, 0, 0, 5),
(1,'2025-11-01','nursing',   14, 3, 0, 0,17),
(1,'2025-11-01','pharmacists', 0, 1, 0, 0, 1),
(1,'2025-11-01','other',      9, 4, 0, 0,13);
-- مستشفى الملك فهد
INSERT INTO kpi_sick_leave (hospital_id,report_date,staff_type,submitted_to_medical_board,approved_by_medical_board,rejected_by_medical_board,referred_to_compliance,total) VALUES
(6,'2025-11-01','doctors',    9,  2, 0, 0,11),
(6,'2025-11-01','nursing',   16,  9, 0, 0,25),
(6,'2025-11-01','pharmacists', 0, 1, 0, 0, 1),
(6,'2025-11-01','other',     20,  7, 0, 0,27);
-- مستشفى العيون
INSERT INTO kpi_sick_leave (hospital_id,report_date,staff_type,submitted_to_medical_board,approved_by_medical_board,rejected_by_medical_board,referred_to_compliance,total) VALUES
(5,'2025-11-01','doctors',    0, 0, 0, 0, 0),
(5,'2025-11-01','nursing',    0, 4, 0, 0, 4),
(5,'2025-11-01','pharmacists',0, 0, 0, 0, 0),
(5,'2025-11-01','other',      0, 2, 0, 0, 2);
-- مستشفى رابغ
INSERT INTO kpi_sick_leave (hospital_id,report_date,staff_type,submitted_to_medical_board,approved_by_medical_board,rejected_by_medical_board,referred_to_compliance,total) VALUES
(7,'2025-11-01','doctors',    0, 0, 0, 0, 0),
(7,'2025-11-01','nursing',    1, 0, 0, 0, 1),
(7,'2025-11-01','pharmacists',1, 0, 0, 0, 1),
(7,'2025-11-01','other',      1, 0, 0, 0, 1);
-- مراكز المطار
INSERT INTO kpi_sick_leave (hospital_id,report_date,staff_type,submitted_to_medical_board,approved_by_medical_board,rejected_by_medical_board,referred_to_compliance,total) VALUES
(2,'2025-11-01','doctors',    0, 0, 0, 0, 0),
(2,'2025-11-01','nursing',    0, 3, 0, 0, 3),
(2,'2025-11-01','pharmacists',0, 0, 0, 0, 0),
(2,'2025-11-01','other',      0, 5, 0, 0, 5);
-- مجمع إرادة
INSERT INTO kpi_sick_leave (hospital_id,report_date,staff_type,submitted_to_medical_board,approved_by_medical_board,rejected_by_medical_board,referred_to_compliance,total) VALUES
(3,'2025-11-01','doctors',    0, 0, 0, 0, 0),
(3,'2025-11-01','nursing',    0, 1, 0, 0, 1),
(3,'2025-11-01','pharmacists',0, 0, 0, 0, 0),
(3,'2025-11-01','other',      0, 0, 0, 0, 0);
-- مستشفى العزيزية
INSERT INTO kpi_sick_leave (hospital_id,report_date,staff_type,submitted_to_medical_board,approved_by_medical_board,rejected_by_medical_board,referred_to_compliance,total) VALUES
(4,'2025-11-01','doctors',    0, 0, 0, 0, 0),
(4,'2025-11-01','nursing',    0, 0, 0, 0, 0),
(4,'2025-11-01','pharmacists',0, 0, 0, 0, 0),
(4,'2025-11-01','other',      0, 0, 0, 0, 0);

-- ============================================
-- مؤشر التصنيف المختلف (D.P.C)
-- ============================================
CREATE TABLE kpi_dpc (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hospital_id INT NOT NULL,
    report_date DATE NOT NULL,
    compliance_management INT DEFAULT 0,
    modified INT DEFAULT 0,
    not_modified INT DEFAULT 0,
    appointment_scheduled INT DEFAULT 0,
    total INT DEFAULT 0,
    percentage DECIMAL(10,6) DEFAULT 0,
    notes TEXT,
    entered_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id),
    FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE SET NULL
);

-- بيانات حقيقية من ورقة D.P.C
INSERT INTO kpi_dpc (hospital_id,report_date,compliance_management,modified,not_modified,appointment_scheduled,total,percentage) VALUES
(6,  '2025-11-01', 2,  0, 0, 0, 2,  0.032787),
(1,  '2025-11-01', 29, 7, 0, 0, 36, 0.590164),
(4,  '2025-11-01', 2,  0, 0, 0, 2,  0.032787),
(7,  '2025-11-01', 4,  0, 0, 0, 4,  0.065574),
(5,  '2025-11-01', 3,  0, 0, 1, 4,  0.065574),
(3,  '2025-11-01', 0,  0,10, 0,10,  0.163934);

-- ============================================
-- مؤشر التأمين ضد الأخطاء الطبية
-- ============================================
CREATE TABLE kpi_medical_insurance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hospital_id INT NOT NULL,
    report_date DATE NOT NULL,
    staff_type ENUM('doctors','nursing','pharmacists','other') NOT NULL,
    insured INT DEFAULT 0,
    not_insured INT DEFAULT 0,
    expired INT DEFAULT 0,
    total INT DEFAULT 0,
    achievement_rate DECIMAL(10,6) DEFAULT 0,
    notes TEXT,
    entered_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id),
    FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE SET NULL
);

-- بيانات حقيقية من ورقة Medical Insurance - مجمع الملك عبدالله
INSERT INTO kpi_medical_insurance (hospital_id,report_date,staff_type,insured,not_insured,expired) VALUES
(1,'2025-11-01','doctors',    0, 0, 0),
(1,'2025-11-01','nursing',    1, 0, 0),
(1,'2025-11-01','pharmacists',2, 0, 0),
(1,'2025-11-01','other',      3, 0, 0);
-- مستشفى الملك فهد
INSERT INTO kpi_medical_insurance (hospital_id,report_date,staff_type,insured,not_insured,expired) VALUES
(6,'2025-11-01','doctors',    0, 0, 0),
(6,'2025-11-01','nursing',    0, 1, 0),
(6,'2025-11-01','pharmacists',0, 0, 0),
(6,'2025-11-01','other',      0, 0, 0);

-- Update totals
UPDATE kpi_medical_insurance SET total = insured + not_insured + expired,
  achievement_rate = CASE WHEN (insured+not_insured+expired)>0 THEN insured/(insured+not_insured+expired) ELSE 0 END;

-- ============================================
-- مؤشر الإجازات السنوية - بيانات حقيقية
-- ============================================
CREATE TABLE kpi_annual_vacation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hospital_id INT NOT NULL,
    report_year INT NOT NULL,
    report_month INT NOT NULL,
    self_operated_staff INT DEFAULT 0,
    civil_service_staff INT DEFAULT 0,
    opening_balance DECIMAL(15,2) DEFAULT 0,
    current_balance DECIMAL(15,2) DEFAULT 0,
    target_balance DECIMAL(15,2) DEFAULT 0,
    gap DECIMAL(15,2) DEFAULT 0,
    monthly_target DECIMAL(15,2) DEFAULT 0,
    monthly_actual DECIMAL(15,2) DEFAULT 0,
    achievement_rate DECIMAL(10,6) DEFAULT 0,
    notes TEXT,
    entered_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id),
    FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE SET NULL
);

-- بيانات حقيقية من ورقة Annual Vacation
-- المقر الرئيسي
INSERT INTO kpi_annual_vacation (hospital_id,report_year,report_month,self_operated_staff,civil_service_staff,opening_balance,current_balance,target_balance,gap,monthly_target,monthly_actual,achievement_rate) VALUES
(8,2025,8,  88,167, 50403.10, 42999, 22950, 20049, 1670.75, 2927, 2.919847),
(8,2025,9,  88,167, 50403.10, 42999, 22950, 20049, 1670.75,  759, 0.757145),
(8,2025,10, 88,167, 50403.10, 42999, 22950, 20049, 1670.75,  700, 0.698289),
(8,2025,11, 88,167, 50403.10, 42999, 22950, 20049, 1670.75,  852, 0.849918),
(8,2025,12, 88,167, 50403.10, 42999, 22950, 20049, 1670.75,  512, 0.510749),
(8,2026,1,  88,167, 50403.10, 42999, 22950, 20049, 1670.75,  366, 0.365105);
-- مستشفى الملك فهد
INSERT INTO kpi_annual_vacation (hospital_id,report_year,report_month,self_operated_staff,civil_service_staff,opening_balance,current_balance,target_balance,gap,monthly_target,monthly_actual,achievement_rate) VALUES
(6,2025,8,  318,976,301597.30,245559.30,116460,129099.30,10758.30, 3469,0.537416),
(6,2025,9,  318,976,301597.30,245559.30,116460,129099.30,10758.30, 3203,0.496207),
(6,2025,10, 318,976,301597.30,245559.30,116460,129099.30,10758.30, 1866,0.289080),
(6,2025,11, 318,976,301597.30,245559.30,116460,129099.30,10758.30, 6698,1.037651),
(6,2025,12, 318,976,301597.30,245559.30,116460,129099.30,10758.30,27628,4.280116),
(6,2026,1,  318,976,301597.30,245559.30,116460,129099.30,10758.30, 3400,0.526726);
-- مستشفى العزيزية
INSERT INTO kpi_annual_vacation (hospital_id,report_year,report_month,self_operated_staff,civil_service_staff,opening_balance,current_balance,target_balance,gap,monthly_target,monthly_actual,achievement_rate) VALUES
(4,2025,8,  18,209, 61571.20, 46408.70, 20430, 25978.70, 2164.89, 3438,2.646784),
(4,2025,9,  18,209, 61571.20, 46408.70, 20430, 25978.70, 2164.89,  888,0.683637),
(4,2025,10, 18,209, 61571.20, 46408.70, 20430, 25978.70, 2164.89, 1963,1.511238),
(4,2025,11, 18,209, 61571.20, 46408.70, 20430, 25978.70, 2164.89, 1334,1.026995),
(4,2025,12, 18,209, 61571.20, 46408.70, 20430, 25978.70, 2164.89, 3387,2.607521),
(4,2026,1,  18,209, 61571.20, 46408.70, 20430, 25978.70, 2164.89,  109,0.083915);
-- مستشفى العيون
INSERT INTO kpi_annual_vacation (hospital_id,report_year,report_month,self_operated_staff,civil_service_staff,opening_balance,current_balance,target_balance,gap,monthly_target,monthly_actual,achievement_rate) VALUES
(5,2025,8,  30,210, 66549.90, 31698.50, 21600, 10098.50,  841.54,  604,1.196217),
(5,2025,9,  30,210, 66549.90, 31698.50, 21600, 10098.50,  841.54,  545,1.079368),
(5,2025,10, 30,210, 66549.90, 31698.50, 21600, 10098.50,  841.54, 1128,2.233995),
(5,2025,11, 30,210, 66549.90, 31698.50, 21600, 10098.50,  841.54,  654,1.295242),
(5,2025,12, 30,210, 66549.90, 31698.50, 21600, 10098.50,  841.54, 2345,4.644254),
(5,2026,1,  30,210, 66549.90, 31698.50, 21600, 10098.50,  841.54,  228,0.451552);

-- ============================================
-- مؤشر MS.7 - امتيازات الأطباء
-- ============================================
CREATE TABLE kpi_ms7 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hospital_id INT NOT NULL,
    report_date DATE NOT NULL,
    total_doctors INT DEFAULT 0,
    granted_privileges INT DEFAULT 0,
    not_granted_privileges INT DEFAULT 0,
    achievement_rate DECIMAL(10,6) DEFAULT 0,
    notes TEXT,
    entered_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id),
    FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE SET NULL
);

-- بيانات حقيقية من ورقة MS.7
INSERT INTO kpi_ms7 (hospital_id, report_date, total_doctors, granted_privileges, not_granted_privileges, achievement_rate) VALUES
(6,'2025-11-01', 855, 855, 0,   1.000000),
(1,'2025-11-01', 734, 727, 7,   0.990463),
(1,'2025-11-01', 185, 185, 0,   1.000000), -- مستشفى الولادة والأطفال
(4,'2025-11-01',  72,  72, 0,   1.000000),
(7,'2025-11-01', 141, 141, 0,   1.000000),
(5,'2025-11-01',  71,  71, 0,   1.000000);

-- ============================================
-- معدل دوران الموظفين
-- ============================================
CREATE TABLE kpi_staff_turnover (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hospital_id INT NOT NULL,
    report_year INT NOT NULL,
    report_month INT NOT NULL,
    total_staff INT DEFAULT 0,
    resignations INT DEFAULT 0,
    terminations INT DEFAULT 0,
    transfers_out INT DEFAULT 0,
    new_hires INT DEFAULT 0,
    turnover_rate DECIMAL(10,6) DEFAULT 0,
    notes TEXT,
    entered_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (hospital_id) REFERENCES hospitals(id),
    FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE SET NULL
);

-- بيانات نموذجية لمعدل الدوران
INSERT INTO kpi_staff_turnover (hospital_id,report_year,report_month,total_staff,resignations,terminations,transfers_out,new_hires,turnover_rate) VALUES
(6,2026,1,1294,  8,2,3,12,0.01004),
(1,2026,1,4139, 15,3,5,20,0.00556),
(4,2026,1, 512,  3,1,1, 5,0.00977),
(5,2026,1, 448,  2,0,1, 3,0.00670),
(7,2026,1, 653,  4,1,2, 6,0.01071),
(3,2026,1, 653,  3,0,1, 4,0.00613),
(2,2026,1, 471,  2,0,1, 3,0.00636);

-- ============================================
-- سجل النشاطات
-- ============================================
CREATE TABLE activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(100),
    record_id INT,
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Add gender column to existing installations
ALTER TABLE users ADD COLUMN IF NOT EXISTS gender ENUM('male','female') DEFAULT 'male' AFTER email;