67 lines
2.1 KiB
SQL
67 lines
2.1 KiB
SQL
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
|
|
);
|