-- ----------------------------------------------------------
-- FIRE SERVICE REPORTING SYSTEM – FULL DATABASE SCHEMA
-- ----------------------------------------------------------
START TRANSACTION;

/* ==========================================================
   FIRE STATIONS TABLE
   ========================================================== */
CREATE TABLE stations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    district VARCHAR(150),
    station_name VARCHAR(200),
    address VARCHAR(255),
    phone VARCHAR(50),
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

/* ==========================================================
   FIRE VEHICLES TABLE (optional but useful)
   ========================================================== */
CREATE TABLE vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    station_id INT,
    vehicle_no VARCHAR(100),
    driver_name VARCHAR(150),
    status ENUM('available','on_call','repair') DEFAULT 'available',
    FOREIGN KEY (station_id) REFERENCES stations(id) ON DELETE CASCADE
);

/* ==========================================================
   MAIN FIRE CALL FORM TABLE — FULL FIELDS FROM USER
   ========================================================== */
CREATE TABLE fire_calls (
    id INT AUTO_INCREMENT PRIMARY KEY,

    fire_call_serial VARCHAR(50) NOT NULL,
    district VARCHAR(100) NOT NULL,
    fire_station VARCHAR(150) NOT NULL,
    area_type ENUM('Rural','Urban') NOT NULL,
    category_of_call VARCHAR(150) NOT NULL,
    property_type VARCHAR(150) NOT NULL,

    occurrence_time DATETIME NOT NULL,
    fire_spot_address TEXT NOT NULL,

    call_receive_mode VARCHAR(100) NOT NULL,
    messenger_details VARCHAR(255),

    time_call_received TIME NOT NULL,
    time_unit_left TIME NOT NULL,
    time_unit_reached TIME,
    distance_km DECIMAL(10,2),

    working_duration TIME,
    time_unit_arrived_back TIME,

    personnel_attended TEXT,
    vehicle_running_km DECIMAL(10,2),
    pump_operation_hours DECIMAL(10,2),

    human_lives_dead INT DEFAULT 0,
    human_lives_saved INT DEFAULT 0,
    cattle_lives_dead INT DEFAULT 0,
    cattle_lives_saved INT DEFAULT 0,

    affected_persons INT DEFAULT 0,
    affected_family INT DEFAULT 0,
    property_affected TEXT,

    cause_of_fire VARCHAR(255),
    properties_lost DECIMAL(15,2),
    properties_saved DECIMAL(15,2),

    building_type VARCHAR(255),
    fire_risk_under_rule9 ENUM('Yes','No') DEFAULT 'No',

    delivery_hose_used VARCHAR(150),
    reporting_officer VARCHAR(150) NOT NULL,
    brief_details TEXT,
    oic_observation TEXT,

    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

/* ==========================================================
   SPECIAL SERVICE CALL TABLE
   ========================================================== */
CREATE TABLE special_service_calls (
    id INT AUTO_INCREMENT PRIMARY KEY,

    district VARCHAR(100),
    fire_station VARCHAR(150),

    service_type VARCHAR(200) NOT NULL,
    request_time DATETIME NOT NULL,
    service_location TEXT NOT NULL,

    requester_name VARCHAR(200),
    requester_contact VARCHAR(100),

    personnel_attended TEXT,
    vehicle_running_km DECIMAL(10,2),

    service_details TEXT,
    property_saved DECIMAL(15,2),
    property_loss DECIMAL(15,2),

    reporting_officer VARCHAR(150),
    oic_observation TEXT,

    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

/* ==========================================================
   ATTENDANCE (optional)
   ========================================================== */
CREATE TABLE attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    station_id INT,
    check_in DATETIME,
    check_out DATETIME,
    status ENUM('present','absent','leave') DEFAULT 'present',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (station_id) REFERENCES stations(id) ON DELETE CASCADE
);

/* ==========================================================
   SYSTEM LOGS
   ========================================================== */
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(255),
    details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

/* ==========================================================
   SETTINGS TABLE
   ========================================================== */
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(150) UNIQUE,
    value TEXT
);

COMMIT;
