-- Jalankan setelah database/schema.sql dan database/seed.sql.
-- Tujuan file ini:
-- 1. Menyimpan persetujuan/kebijakan tracking karyawan.
-- 2. Menyimpan daftar aplikasi/domain yang dilarang untuk perangkat perusahaan.
-- 3. Menyimpan laporan kepatuhan dari MDM, proxy, firewall, atau input admin resmi.
--
-- Catatan penting:
-- Aplikasi web/browser tidak bisa membaca aplikasi lain yang sedang dibuka di HP,
-- tidak bisa mengambil ID hardware permanen, dan tidak bisa memblokir kamera/sosmed.
-- Pembatasan aplikasi/kamera harus memakai Android Enterprise MDM, iOS Supervised MDM,
-- firewall/proxy kantor, atau perangkat perusahaan yang sudah diberi persetujuan tertulis.

SET NAMES utf8mb4;
SET time_zone = '+07:00';

CREATE TABLE IF NOT EXISTS 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') NOT NULL DEFAULT 'qr_tracking',
 consent_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 ip_address VARCHAR(45) NULL,
 user_agent VARCHAR(500) NULL,
 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 KEY employee_policy_version_unique(employee_id, policy_code, policy_version),
 INDEX(policy_code, consent_at),
 INDEX(revoked_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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') NOT NULL DEFAULT 'warning',
 applies_to_division_id BIGINT UNSIGNED NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 notes VARCHAR(255) NULL,
 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 KEY policy_rule_unique(rule_type, rule_key),
 INDEX(rule_type, is_active),
 INDEX(severity)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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') NOT NULL DEFAULT 'unknown',
 app_package VARCHAR(190) NULL,
 app_name VARCHAR(150) NULL,
 url_host VARCHAR(190) NULL,
 url_path VARCHAR(500) NULL,
 window_title VARCHAR(255) NULL,
 event_detail JSON NULL,
 ip_address VARCHAR(45) NULL,
 user_agent VARCHAR(500) NULL,
 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 IF NOT EXISTS device_restriction_profiles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL UNIQUE,
 description VARCHAR(255) NULL,
 mdm_provider VARCHAR(80) NULL,
 mdm_profile_id VARCHAR(120) NULL,
 camera_allowed TINYINT(1) NOT NULL DEFAULT 1,
 social_media_allowed TINYINT(1) NOT NULL DEFAULT 0,
 youtube_allowed TINYINT(1) NOT NULL DEFAULT 0,
 require_location_enabled TINYINT(1) NOT NULL DEFAULT 1,
 settings JSON NULL,
 is_active TINYINT(1) NOT NULL 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 IF NOT EXISTS 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;

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 standar perangkat kerja: lokasi wajib aktif, sosmed dan kamera mengikuti kebijakan perusahaan.', NULL, 0, 0, 0, 1,
 JSON_OBJECT('tracking_interval_seconds', 15, 'allowed_sources', JSON_ARRAY('mdm','proxy','firewall','admin_manual')), 1)
ON DUPLICATE KEY UPDATE name = VALUES(name);

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')
ON DUPLICATE KEY UPDATE
 rule_label = VALUES(rule_label),
 severity = VALUES(severity),
 notes = VALUES(notes),
 is_active = 1;

INSERT INTO permissions(code,name,module) VALUES
('device_policy.view','Lihat kebijakan perangkat','device_policy'),
('device_policy.manage','Kelola kebijakan perangkat','device_policy')
ON DUPLICATE KEY UPDATE name = VALUES(name), module = VALUES(module);

INSERT IGNORE INTO role_permissions(role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.code IN ('device_policy.view','device_policy.manage')
WHERE r.name IN ('Super Admin','Admin','IT');

INSERT INTO system_settings(setting_key,setting_value,is_public) VALUES
('device_policy_enabled', 'true', 0),
('device_policy_notice', JSON_QUOTE('Pemantauan aplikasi hanya boleh dari MDM/proxy/firewall resmi atau input admin, bukan penyadapan browser.'), 0)
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);