-- RIETS full install SQL. Pilih database rahmatku_mapsrka1 di phpMyAdmin sebelum import file ini.
-- Pilih database tujuan di phpMyAdmin sebelum mengimpor file ini.
SET NAMES utf8mb4;
SET time_zone = '+07:00';

CREATE TABLE roles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) UNIQUE NOT NULL,
 description VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE permissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(100) UNIQUE NOT NULL,
 name VARCHAR(100) NOT NULL, module VARCHAR(60) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE role_permissions (
 role_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(role_id,permission_id), FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,
 FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, role_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(120) NOT NULL, email VARCHAR(190) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL,
 is_active TINYINT(1) DEFAULT 1, last_login_at DATETIME NULL, password_changed_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 deleted_at DATETIME NULL, FOREIGN KEY(role_id) REFERENCES roles(id), INDEX(is_active)
) ENGINE=InnoDB;
CREATE TABLE refresh_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) UNIQUE NOT NULL,
 device_name VARCHAR(100), expires_at DATETIME NOT NULL, revoked_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX(expires_at)
) ENGINE=InnoDB;
CREATE TABLE login_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, email VARCHAR(190), success TINYINT(1),
 ip_address VARCHAR(45), user_agent VARCHAR(500), logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX(logged_at)
) ENGINE=InnoDB;
CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, action VARCHAR(40) NOT NULL,
 entity_type VARCHAR(100) NOT NULL, entity_id BIGINT UNSIGNED NULL, old_values JSON NULL, new_values JSON NULL,
 ip_address VARCHAR(45), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX(entity_type,entity_id), INDEX(created_at)
) ENGINE=InnoDB;

CREATE TABLE divisions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(30) UNIQUE NOT NULL, name VARCHAR(100) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL
) ENGINE=InnoDB;
CREATE TABLE positions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL
) ENGINE=InnoDB;
CREATE TABLE employees (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, division_id BIGINT UNSIGNED NULL, position_id BIGINT UNSIGNED NULL,
 nik VARCHAR(50) UNIQUE NOT NULL, name VARCHAR(120) NOT NULL, phone VARCHAR(30), email VARCHAR(190), avatar VARCHAR(255),
 device_imei VARCHAR(100), tracking_pin_hash VARCHAR(255) NULL, status ENUM('active','inactive','leave') DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(division_id) REFERENCES divisions(id) ON DELETE SET NULL,
 FOREIGN KEY(position_id) REFERENCES positions(id) ON DELETE SET NULL, INDEX(status)
) ENGINE=InnoDB;
CREATE TABLE buildings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(30) UNIQUE NOT NULL, name VARCHAR(120) NOT NULL,
 address TEXT, area_m2 DECIMAL(12,2), land_area_m2 DECIMAL(12,2), building_area_m2 DECIMAL(12,2), total_floors INT UNSIGNED DEFAULT 1,
 latitude DECIMAL(10,7), longitude DECIMAL(10,7), photo VARCHAR(255), gis_file VARCHAR(255), status ENUM('active','inactive') DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL
) ENGINE=InnoDB;
CREATE TABLE floors (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, building_id BIGINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, level_no INT NOT NULL,
 plan_image VARCHAR(255), width_px INT, height_px INT, length_m DECIMAL(10,2), width_m DECIMAL(10,2), scale_px_per_meter DECIMAL(10,4) DEFAULT 10,
 origin_lat DECIMAL(10,7), origin_lng DECIMAL(10,7),
 status ENUM('active','inactive') DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL,
 FOREIGN KEY(building_id) REFERENCES buildings(id), UNIQUE(building_id,level_no)
) ENGINE=InnoDB;
CREATE TABLE rooms (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, floor_id BIGINT UNSIGNED NOT NULL, code VARCHAR(30) NOT NULL, name VARCHAR(100) NOT NULL,
 x DECIMAL(10,2) NOT NULL, y DECIMAL(10,2) NOT NULL, width DECIMAL(10,2) NOT NULL, height DECIMAL(10,2) NOT NULL,
 polygon JSON NULL, color CHAR(7) DEFAULT '#B9D8F2', is_restricted TINYINT(1) DEFAULT 0, status ENUM('active','inactive') DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL,
 FOREIGN KEY(floor_id) REFERENCES floors(id), UNIQUE(floor_id,code), INDEX(floor_id)
) ENGINE=InnoDB;
CREATE TABLE beacons (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, floor_id BIGINT UNSIGNED NOT NULL, code VARCHAR(50) UNIQUE NOT NULL,
 uuid CHAR(36) NOT NULL, major INT UNSIGNED NOT NULL, minor INT UNSIGNED NOT NULL,
 x DECIMAL(10,2) NOT NULL, y DECIMAL(10,2) NOT NULL, tx_power SMALLINT DEFAULT -59, radius_m DECIMAL(8,2) DEFAULT 10,
 battery_percent TINYINT UNSIGNED NULL, status ENUM('active','inactive','maintenance') DEFAULT 'active', last_seen_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL,
 FOREIGN KEY(floor_id) REFERENCES floors(id), UNIQUE(uuid,major,minor), INDEX(floor_id,status)
) ENGINE=InnoDB;
CREATE TABLE beacon_calibrations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, beacon_id BIGINT UNSIGNED NOT NULL, distance_m DECIMAL(8,2), measured_rssi SMALLINT,
 calibrated_tx_power SMALLINT, notes VARCHAR(255), created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(beacon_id) REFERENCES beacons(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE devices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, device_uuid VARCHAR(100) UNIQUE NOT NULL,
 platform VARCHAR(30), app_version VARCHAR(30), push_token VARCHAR(255), battery_percent TINYINT UNSIGNED,
 network_type VARCHAR(30), is_tracking TINYINT(1) DEFAULT 1, last_seen_at DATETIME,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE, INDEX(last_seen_at)
) ENGINE=InnoDB;
CREATE TABLE qr_areas (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, room_id BIGINT UNSIGNED NULL, building_id BIGINT UNSIGNED NULL,
 code VARCHAR(80) UNIQUE NOT NULL, name VARCHAR(120) NOT NULL, latitude DECIMAL(10,7) NOT NULL,
 longitude DECIMAL(10,7) NOT NULL, radius_m DECIMAL(8,2) DEFAULT 100, status ENUM('active','inactive') DEFAULT 'active',
 scan_count BIGINT UNSIGNED DEFAULT 0, created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL,
 FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE SET NULL, FOREIGN KEY(building_id) REFERENCES buildings(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX(status), INDEX(latitude,longitude)
) ENGINE=InnoDB;
CREATE TABLE tracking_sessions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, qr_area_id BIGINT UNSIGNED NULL,
 device_id BIGINT UNSIGNED NULL, public_id CHAR(36) UNIQUE NOT NULL, token_hash CHAR(64) UNIQUE NOT NULL,
 source VARCHAR(30) DEFAULT 'gps', status ENUM('active','stopped','expired') DEFAULT 'active', consent_at DATETIME NULL,
 ip_address VARCHAR(45), user_agent VARCHAR(500), started_at DATETIME NOT NULL, last_ping_at DATETIME NULL,
 ended_at DATETIME NULL, stop_reason VARCHAR(100), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(employee_id) REFERENCES employees(id), FOREIGN KEY(qr_area_id) REFERENCES qr_areas(id) ON DELETE SET NULL,
 FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE SET NULL, INDEX(employee_id,status), INDEX(last_ping_at)
) ENGINE=InnoDB;
CREATE TABLE uwb_anchors (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, floor_id BIGINT UNSIGNED NOT NULL, code VARCHAR(50) UNIQUE NOT NULL,
 hardware_uid VARCHAR(100) UNIQUE, x DECIMAL(10,2), y DECIMAL(10,2), z DECIMAL(10,2) DEFAULT 0,
 status ENUM('active','inactive','maintenance') DEFAULT 'active', last_seen_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 deleted_at DATETIME NULL, FOREIGN KEY(floor_id) REFERENCES floors(id), INDEX(floor_id,status)
) ENGINE=InnoDB;
CREATE TABLE current_locations (
 employee_id BIGINT UNSIGNED PRIMARY KEY, tracking_session_id BIGINT UNSIGNED NULL, source VARCHAR(30) NOT NULL DEFAULT 'gps',
 floor_id BIGINT UNSIGNED NULL, room_id BIGINT UNSIGNED NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL,
 altitude_m DECIMAL(9,2) NULL, x DECIMAL(10,2) NULL, y DECIMAL(10,2) NULL, accuracy_m DECIMAL(8,2),
 heading_deg DECIMAL(6,2) NULL, nearest_beacon_id BIGINT UNSIGNED NULL, battery_percent TINYINT UNSIGNED,
 network_type VARCHAR(30), speed_mps DECIMAL(8,2) DEFAULT 0, last_seen_at DATETIME NOT NULL,
 FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE, FOREIGN KEY(tracking_session_id) REFERENCES tracking_sessions(id) ON DELETE SET NULL,
 FOREIGN KEY(floor_id) REFERENCES floors(id) ON DELETE SET NULL,
 FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE SET NULL, FOREIGN KEY(nearest_beacon_id) REFERENCES beacons(id) ON DELETE SET NULL,
 INDEX(floor_id,last_seen_at), INDEX(room_id,last_seen_at)
) ENGINE=InnoDB;
CREATE TABLE location_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, tracking_session_id BIGINT UNSIGNED NULL,
 source VARCHAR(30) NOT NULL DEFAULT 'gps', floor_id BIGINT UNSIGNED NULL,
 room_id BIGINT UNSIGNED NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, altitude_m DECIMAL(9,2) NULL,
 x DECIMAL(10,2) NULL, y DECIMAL(10,2) NULL, accuracy_m DECIMAL(8,2), heading_deg DECIMAL(6,2) NULL,
 nearest_beacon_id BIGINT UNSIGNED NULL, rssi SMALLINT, battery_percent TINYINT UNSIGNED, network_type VARCHAR(30),
 speed_mps DECIMAL(8,2) DEFAULT 0, client_event_id VARCHAR(80) NULL, recorded_at DATETIME(3) NOT NULL,
 received_at TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3), metadata JSON NULL,
 FOREIGN KEY(employee_id) REFERENCES employees(id), FOREIGN KEY(tracking_session_id) REFERENCES tracking_sessions(id) ON DELETE SET NULL,
 FOREIGN KEY(floor_id) REFERENCES floors(id) ON DELETE SET NULL,
 FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE SET NULL, FOREIGN KEY(nearest_beacon_id) REFERENCES beacons(id) ON DELETE SET NULL,
 UNIQUE(employee_id,client_event_id), INDEX(employee_id,recorded_at), INDEX(source,recorded_at),
 INDEX(floor_id,recorded_at), INDEX(room_id,recorded_at), INDEX(latitude,longitude)
) ENGINE=InnoDB;
CREATE TABLE gps_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, location_history_id BIGINT UNSIGNED NOT NULL,
 latitude DECIMAL(10,7) NOT NULL, longitude DECIMAL(10,7) NOT NULL, altitude_m DECIMAL(9,2),
 accuracy_m DECIMAL(8,2), heading_deg DECIMAL(6,2), speed_mps DECIMAL(8,2), provider VARCHAR(30) DEFAULT 'browser',
 FOREIGN KEY(location_history_id) REFERENCES location_history(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE uwb_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, location_history_id BIGINT UNSIGNED NOT NULL, tag_uid VARCHAR(100),
 x DECIMAL(10,2), y DECIMAL(10,2), z DECIMAL(10,2), quality DECIMAL(6,3),
 FOREIGN KEY(location_history_id) REFERENCES location_history(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE rfid_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NULL, tag_uid VARCHAR(100), reader_uid VARCHAR(100),
 event_type ENUM('seen','enter','exit') DEFAULT 'seen', recorded_at DATETIME(3) NOT NULL,
 FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE SET NULL, INDEX(tag_uid,recorded_at)
) ENGINE=InnoDB;
CREATE TABLE raw_beacon_scans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, beacon_id BIGINT UNSIGNED NULL,
 uuid CHAR(36), major INT UNSIGNED, minor INT UNSIGNED, rssi SMALLINT NOT NULL, distance_m DECIMAL(8,3), scanned_at DATETIME(3),
 FOREIGN KEY(employee_id) REFERENCES employees(id), FOREIGN KEY(beacon_id) REFERENCES beacons(id) ON DELETE SET NULL,
 INDEX(employee_id,scanned_at)
) ENGINE=InnoDB;

CREATE TABLE work_schedules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL DEFAULT 'Reguler', day_of_week TINYINT NOT NULL,
 start_time TIME NOT NULL, end_time TIME NOT NULL, is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;
CREATE TABLE employee_schedules (
 employee_id BIGINT UNSIGNED NOT NULL, schedule_id BIGINT UNSIGNED NOT NULL, effective_from DATE NOT NULL, effective_to DATE NULL,
 PRIMARY KEY(employee_id,schedule_id,effective_from), FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE,
 FOREIGN KEY(schedule_id) REFERENCES work_schedules(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE attendance (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, work_date DATE NOT NULL,
 check_in DATETIME NULL, check_out DATETIME NULL, status ENUM('present','late','leave','absent') DEFAULT 'present',
 FOREIGN KEY(employee_id) REFERENCES employees(id), UNIQUE(employee_id,work_date)
) ENGINE=InnoDB;
CREATE TABLE room_visits (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL, room_id BIGINT UNSIGNED NOT NULL,
 entered_at DATETIME NOT NULL, exited_at DATETIME NULL, duration_seconds INT UNSIGNED DEFAULT 0,
 FOREIGN KEY(employee_id) REFERENCES employees(id), FOREIGN KEY(room_id) REFERENCES rooms(id),
 INDEX(employee_id,entered_at), INDEX(room_id,entered_at)
) ENGINE=InnoDB;
CREATE TABLE geofence_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, room_id BIGINT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL,
 action ENUM('notify','deny','log') DEFAULT 'notify', applies_to_division_id BIGINT UNSIGNED NULL, is_active TINYINT(1) DEFAULT 1,
 FOREIGN KEY(room_id) REFERENCES rooms(id), FOREIGN KEY(applies_to_division_id) REFERENCES divisions(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE employee_policy_consents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NOT NULL,
 policy_code VARCHAR(80) NOT NULL, policy_version VARCHAR(30) NOT NULL DEFAULT '1.0',
 consent_text TEXT NOT NULL, consent_method ENUM('qr_tracking','written','mdm_enrollment','admin_import') DEFAULT 'qr_tracking',
 consent_at DATETIME NOT NULL, revoked_at DATETIME NULL, ip_address VARCHAR(45), user_agent VARCHAR(500),
 created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE(employee_id,policy_code,policy_version), INDEX(policy_code,consent_at), INDEX(revoked_at)
) ENGINE=InnoDB;
CREATE TABLE device_policy_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 rule_type ENUM('blocked_domain','blocked_app','blocked_category','camera_restriction','location_required') NOT NULL,
 rule_key VARCHAR(190) NOT NULL, rule_label VARCHAR(150) NOT NULL,
 severity ENUM('info','warning','critical') DEFAULT 'warning', applies_to_division_id BIGINT UNSIGNED NULL,
 is_active TINYINT(1) DEFAULT 1, notes VARCHAR(255), created_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(applies_to_division_id) REFERENCES divisions(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 UNIQUE(rule_type,rule_key), INDEX(rule_type,is_active), INDEX(severity)
) ENGINE=InnoDB;
CREATE TABLE device_policy_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NULL, device_id BIGINT UNSIGNED NULL,
 rule_id BIGINT UNSIGNED NULL, event_source ENUM('mdm','proxy','firewall','browser_self_report','admin_manual') NOT NULL,
 event_type ENUM('blocked','allowed','warning','camera_attempt','location_disabled','unknown') DEFAULT 'unknown',
 app_package VARCHAR(190), app_name VARCHAR(150), url_host VARCHAR(190), url_path VARCHAR(500), window_title VARCHAR(255),
 event_detail JSON, ip_address VARCHAR(45), user_agent VARCHAR(500), recorded_at DATETIME NOT NULL,
 received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE SET NULL, FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE SET NULL,
 FOREIGN KEY(rule_id) REFERENCES device_policy_rules(id) ON DELETE SET NULL,
 INDEX(employee_id,recorded_at), INDEX(device_id,recorded_at), INDEX(rule_id,recorded_at),
 INDEX(event_source,event_type), INDEX(url_host), INDEX(app_package)
) ENGINE=InnoDB;
CREATE TABLE device_restriction_profiles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL UNIQUE, description VARCHAR(255),
 mdm_provider VARCHAR(80), mdm_profile_id VARCHAR(120), camera_allowed TINYINT(1) DEFAULT 1,
 social_media_allowed TINYINT(1) DEFAULT 0, youtube_allowed TINYINT(1) DEFAULT 0,
 require_location_enabled TINYINT(1) DEFAULT 1, settings JSON, is_active TINYINT(1) DEFAULT 1,
 created_by BIGINT UNSIGNED NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX(is_active)
) ENGINE=InnoDB;
CREATE TABLE employee_restriction_profiles (
 employee_id BIGINT UNSIGNED NOT NULL, profile_id BIGINT UNSIGNED NOT NULL,
 assigned_by BIGINT UNSIGNED NULL, assigned_at DATETIME NOT NULL, revoked_at DATETIME NULL,
 PRIMARY KEY(employee_id,profile_id,assigned_at), FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE,
 FOREIGN KEY(profile_id) REFERENCES device_restriction_profiles(id) ON DELETE CASCADE,
 FOREIGN KEY(assigned_by) REFERENCES users(id) ON DELETE SET NULL, INDEX(profile_id,revoked_at)
) ENGINE=InnoDB;
CREATE TABLE notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, employee_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL,
 type VARCHAR(40) NOT NULL, title VARCHAR(150) NOT NULL, message TEXT NOT NULL, severity ENUM('info','warning','critical') DEFAULT 'info',
 read_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE SET NULL, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(created_at,severity)
) ENGINE=InnoDB;
CREATE TABLE notification_channels (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), type ENUM('email','whatsapp','push','webhook'),
 config JSON, is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;
CREATE TABLE notification_deliveries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, notification_id BIGINT UNSIGNED NOT NULL, channel_id BIGINT UNSIGNED NOT NULL,
 status ENUM('pending','sent','failed') DEFAULT 'pending', attempts TINYINT DEFAULT 0, sent_at DATETIME NULL, response TEXT,
 FOREIGN KEY(notification_id) REFERENCES notifications(id) ON DELETE CASCADE, FOREIGN KEY(channel_id) REFERENCES notification_channels(id)
) ENGINE=InnoDB;

CREATE TABLE floor_uploads (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, floor_id BIGINT UNSIGNED NOT NULL, file_path VARCHAR(255), original_name VARCHAR(255),
 mime_type VARCHAR(100), file_size BIGINT, uploaded_by BIGINT UNSIGNED, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(floor_id) REFERENCES floors(id) ON DELETE CASCADE, FOREIGN KEY(uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE gis_uploads (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, building_id BIGINT UNSIGNED NOT NULL, file_path VARCHAR(255), format VARCHAR(30),
 metadata JSON, uploaded_by BIGINT UNSIGNED, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(building_id) REFERENCES buildings(id) ON DELETE CASCADE, FOREIGN KEY(uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE report_exports (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, report_type VARCHAR(60), format VARCHAR(10),
 filters JSON, file_path VARCHAR(255), status ENUM('queued','processing','ready','failed') DEFAULT 'queued',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_at DATETIME NULL, FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE system_settings (
 setting_key VARCHAR(100) PRIMARY KEY, setting_value JSON NOT NULL, is_public TINYINT(1) DEFAULT 0,
 updated_by BIGINT UNSIGNED NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE api_clients (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), client_id VARCHAR(80) UNIQUE, secret_hash VARCHAR(255),
 scopes JSON, is_active TINYINT(1) DEFAULT 1, last_used_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE webhooks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), endpoint_url VARCHAR(500), secret VARCHAR(255),
 events JSON, is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE webhook_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, webhook_id BIGINT UNSIGNED, event VARCHAR(100), payload JSON,
 http_status SMALLINT, response TEXT, attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(webhook_id) REFERENCES webhooks(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE backup_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, file_path VARCHAR(255), file_size BIGINT, status ENUM('running','success','failed'),
 checksum VARCHAR(128), message TEXT, started_at DATETIME, completed_at DATETIME
) ENGINE=InnoDB;
CREATE TABLE jobs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, queue VARCHAR(50), payload JSON, attempts TINYINT DEFAULT 0,
 available_at DATETIME, reserved_at DATETIME NULL, completed_at DATETIME NULL, failed_at DATETIME NULL, INDEX(queue,available_at)
) ENGINE=InnoDB;
CREATE TABLE failed_jobs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, job_id BIGINT UNSIGNED NULL, payload JSON, exception TEXT,
 failed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE data_retention_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, table_name VARCHAR(100), rows_deleted BIGINT UNSIGNED,
 cutoff_date DATETIME, executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- Impor setelah schema.sql pada database RIETS yang sedang dipilih.
INSERT INTO roles(name,description) VALUES
('Super Admin','Akses penuh'),('Admin','Operasional sistem'),('HRD','SDM dan laporan'),('Manager','Dashboard dan laporan'),
('Supervisor','Monitoring tim'),('Karyawan','Aplikasi perangkat'),('IT','Infrastruktur dan perangkat');

INSERT INTO permissions(code,name,module) VALUES
('dashboard.view','Lihat dashboard','dashboard'),('master.manage','Kelola master data','master'),
('tracking.view','Lihat tracking','tracking'),('tracking.manage','Kelola tracking','tracking'),
('report.view','Lihat laporan','report'),('report.export','Export laporan','report'),
('settings.manage','Kelola pengaturan','settings'),('audit.view','Lihat audit','audit'),
('device_policy.view','Lihat kebijakan perangkat','device_policy'),
('device_policy.manage','Kelola kebijakan perangkat','device_policy');
INSERT INTO role_permissions SELECT 1,id FROM permissions;
INSERT INTO role_permissions SELECT 2,id FROM permissions WHERE code NOT IN ('audit.view');
INSERT INTO role_permissions SELECT 3,id FROM permissions WHERE code IN ('dashboard.view','master.manage','tracking.view','report.view','report.export');
INSERT INTO role_permissions SELECT 4,id FROM permissions WHERE code IN ('dashboard.view','tracking.view','report.view','report.export');
INSERT INTO role_permissions SELECT 5,id FROM permissions WHERE code IN ('dashboard.view','tracking.view','report.view');
INSERT INTO role_permissions SELECT 7,id FROM permissions WHERE code IN ('dashboard.view','master.manage','tracking.view','settings.manage','audit.view');
INSERT INTO role_permissions SELECT 7,id FROM permissions WHERE code IN ('device_policy.view','device_policy.manage');

-- Password: Admin@12345 (ubah segera setelah login)
INSERT INTO users(role_id,name,email,password_hash,is_active,password_changed_at)
VALUES(1,'Administrator RIETS','admin@riets.local','$argon2id$v=19$m=65536,t=4,p=1$U2J5T1Y3a0trWWRaVUtZNQ$VSBStDDcD6Xmt7lP+KQvNNaTk10v2eF+dA+Bh1KbXgo',1,NOW());

INSERT INTO divisions(code,name) VALUES('IT','Information Technology'),('HRD','Human Resources'),('OPS','Operasional');
INSERT INTO positions(name) VALUES('Manager'),('Supervisor'),('Staff');
INSERT INTO users(role_id,name,email,password_hash,is_active,password_changed_at)
VALUES(6,'Samsul','samsul@riets.local','$argon2id$v=19$m=65536,t=4,p=1$U2J5T1Y3a0trWWRaVUtZNQ$VSBStDDcD6Xmt7lP+KQvNNaTk10v2eF+dA+Bh1KbXgo',1,NOW());
INSERT INTO employees(user_id,division_id,position_id,nik,name,phone,email,status)
VALUES(2,3,3,'RKA-0001','Samsul','080000000000','samsul@riets.local','active');
INSERT INTO buildings(code,name,address,area_m2,latitude,longitude) VALUES('GD-A','Gedung Produksi','Kompleks RKA',2500,-6.2000000,106.8166667);
INSERT INTO floors(building_id,name,level_no,width_px,height_px,scale_px_per_meter) VALUES(1,'Lantai 1',1,1200,800,10);
INSERT INTO rooms(floor_id,code,name,x,y,width,height,is_restricted) VALUES
(1,'GDG','Gudang',50,50,300,250,0),(1,'QC','Quality Control',400,50,280,250,0),(1,'PANEL','Panel Listrik',750,50,220,200,1);
INSERT INTO qr_areas(room_id,building_id,code,name,latitude,longitude,radius_m,status,created_by)
VALUES(1,1,'RKA-GD-A-GUDANG','Area Masuk Gudang',-6.2000000,106.8166667,150,'active',1);
INSERT INTO beacons(floor_id,code,uuid,major,minor,x,y,tx_power,radius_m,status) VALUES
(1,'BCN-A01','fda50693-a4e2-4fb1-afcf-c6eb07647825',1,1,80,80,-59,15,'active'),
(1,'BCN-A02','fda50693-a4e2-4fb1-afcf-c6eb07647825',1,2,620,80,-59,15,'active'),
(1,'BCN-A03','fda50693-a4e2-4fb1-afcf-c6eb07647825',1,3,450,500,-59,15,'active');
INSERT INTO work_schedules(name,day_of_week,start_time,end_time) VALUES
('Reguler',1,'08:00:00','17:00:00'),('Reguler',2,'08:00:00','17:00:00'),('Reguler',3,'08:00:00','17:00:00'),
('Reguler',4,'08:00:00','17:00:00'),('Reguler',5,'08:00:00','17:00:00');
INSERT INTO system_settings(setting_key,setting_value,is_public) VALUES
('tracking_interval_seconds','1',1),('offline_after_seconds','30',1),('gps_max_accuracy_m','100',1),
('enforce_work_schedule','true',1),
('location_engines','{"gps":true,"ble":true,"uwb":false,"rfid":false}',1),('company_name','"RKA"',1),
('device_policy_enabled','true',0),
('device_policy_notice','"Pemantauan aplikasi hanya dari MDM/proxy/firewall resmi atau input admin, bukan penyadapan browser."',0);

INSERT INTO device_restriction_profiles
(name,description,mdm_provider,camera_allowed,social_media_allowed,youtube_allowed,require_location_enabled,settings,is_active)
VALUES
('Profil Operasional RKA','Profil perangkat kerja: lokasi wajib aktif, sosmed dan kamera mengikuti kebijakan perusahaan.',NULL,0,0,0,1,
 '{"tracking_interval_seconds":1,"allowed_sources":["mdm","proxy","firewall","admin_manual"]}',1);

INSERT INTO device_policy_rules(rule_type,rule_key,rule_label,severity,notes) VALUES
('blocked_domain','instagram.com','Instagram','warning','Blokir melalui DNS/proxy/firewall atau MDM browser policy'),
('blocked_domain','facebook.com','Facebook','warning','Blokir melalui DNS/proxy/firewall atau MDM browser policy'),
('blocked_domain','youtube.com','YouTube','warning','Blokir melalui DNS/proxy/firewall atau MDM browser policy'),
('blocked_domain','tiktok.com','TikTok','warning','Blokir melalui DNS/proxy/firewall atau MDM browser policy'),
('blocked_app','com.instagram.android','Instagram Android','warning','Butuh Android Enterprise MDM untuk enforcement'),
('blocked_app','com.facebook.katana','Facebook Android','warning','Butuh Android Enterprise MDM untuk enforcement'),
('blocked_app','com.google.android.youtube','YouTube Android','warning','Butuh Android Enterprise MDM untuk enforcement'),
('blocked_app','com.zhiliaoapp.musically','TikTok Android','warning','Butuh Android Enterprise MDM untuk enforcement'),
('camera_restriction','camera','Pembatasan kamera','critical','Butuh MDM pada perangkat perusahaan/supervised');