-- KINET MikroTik Admin Module - Update v40 Sales + Teknisi + Centralized Access
-- Jalankan sekali setelah replace file v40.

CREATE TABLE IF NOT EXISTS role_module_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_code VARCHAR(30) NOT NULL,
    module_key VARCHAR(80) NOT NULL,
    can_view TINYINT(1) NOT NULL DEFAULT 0,
    can_create TINYINT(1) NOT NULL DEFAULT 0,
    can_edit TINYINT(1) NOT NULL DEFAULT 0,
    can_delete TINYINT(1) NOT NULL DEFAULT 0,
    can_action TINYINT(1) NOT NULL DEFAULT 0,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_role_module (role_code, module_key),
    INDEX idx_role_module_role (role_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_module_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    module_key VARCHAR(80) NOT NULL,
    can_view TINYINT(1) NOT NULL DEFAULT 0,
    can_create TINYINT(1) NOT NULL DEFAULT 0,
    can_edit TINYINT(1) NOT NULL DEFAULT 0,
    can_delete TINYINT(1) NOT NULL DEFAULT 0,
    can_action TINYINT(1) NOT NULL DEFAULT 0,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_module (user_id, module_key),
    INDEX idx_user_module_user (user_id),
    CONSTRAINT fk_user_module_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS acs_devices (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NULL,
    router_id INT NULL,
    device_name VARCHAR(160) NOT NULL,
    serial_number VARCHAR(160) NULL,
    mac_address VARCHAR(80) NULL,
    ip_address VARCHAR(80) NULL,
    ont_model VARCHAR(160) NULL,
    ssid VARCHAR(160) NULL,
    status ENUM('online','offline','maintenance','unknown') NOT NULL DEFAULT 'unknown',
    last_seen_at DATETIME NULL,
    note TEXT NULL,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_acs_customer (customer_id),
    INDEX idx_acs_router (router_id),
    INDEX idx_acs_status (status),
    CONSTRAINT fk_acs_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_acs_router FOREIGN KEY (router_id) REFERENCES routers(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_acs_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE psb_onboardings ADD COLUMN IF NOT EXISTS coverage_status ENUM('unchecked','covered','not_covered','need_survey') NOT NULL DEFAULT 'unchecked' AFTER longitude;
ALTER TABLE psb_onboardings ADD COLUMN IF NOT EXISTS nearest_odp_id INT NULL AFTER coverage_status;
ALTER TABLE psb_onboardings ADD COLUMN IF NOT EXISTS coverage_distance_m DECIMAL(12,2) NULL AFTER nearest_odp_id;
ALTER TABLE psb_onboardings ADD COLUMN IF NOT EXISTS sales_id INT NULL AFTER assigned_to;
ALTER TABLE psb_onboardings ADD INDEX IF NOT EXISTS idx_psb_sales_id (sales_id);

INSERT INTO settings (setting_key, setting_value) VALUES
('coverage_default_radius_m', '300'),
('sales_portal_enabled', '1'),
('teknisi_portal_enabled', '1'),
('central_access_enabled', '1')
ON DUPLICATE KEY UPDATE setting_key = setting_key;