CREATE DATABASE IF NOT EXISTS emergency_tracker CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE emergency_tracker;

CREATE TABLE IF NOT EXISTS users (
    national_id CHAR(9) PRIMARY KEY,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    in_shelter ENUM('yes', 'no', 'no_alarm', 'safe_after_exit') DEFAULT NULL,
    last_updated DATETIME DEFAULT NULL,
    is_admin BOOLEAN NOT NULL DEFAULT FALSE,
    role ENUM('user', 'team_admin', 'department_admin', 'field_admin', 'global_admin') NOT NULL DEFAULT 'user',
    lock_status BOOLEAN NOT NULL DEFAULT FALSE,
    must_change_password BOOLEAN NOT NULL DEFAULT TRUE,
    password_changed_at DATETIME DEFAULT NULL,
    field VARCHAR(100) DEFAULT NULL,
    department VARCHAR(100) DEFAULT NULL,
    team VARCHAR(100) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS admin_actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id CHAR(9) NOT NULL,
    action_type ENUM(
        'reset_all',
        'reset_password',
        'reset_team',
        'reset_department',
        'reset_field',
        'role_change',
        'report_on_behalf'
    ) NOT NULL,
    target_user_id CHAR(9) DEFAULT NULL,
    target_role VARCHAR(50) DEFAULT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES users(national_id),
    FOREIGN KEY (target_user_id) REFERENCES users(national_id)
);

CREATE TABLE IF NOT EXISTS managed_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('field', 'department', 'team') NOT NULL,
    name VARCHAR(100) NOT NULL,
    parent_id INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_type_name (type, name),
    INDEX idx_managed_parent (parent_id),
    CONSTRAINT fk_managed_parent FOREIGN KEY (parent_id) REFERENCES managed_types(id) ON DELETE RESTRICT
);

-- Insert a default admin user (password: "admin123")
INSERT IGNORE INTO users (
    national_id,
    password,
    name,
    is_admin,
    role,
    must_change_password
) VALUES (
    '123456782',
    '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBdXzz.JrKIFHy',
    'Admin',
    TRUE,
    'global_admin',
    FALSE
);