-- KitCikgu License Center 0.9
-- Jalankan selepas backup database. Migrasi ini tidak memadam lesen, aktivasi atau key pair.
SET NAMES utf8mb4;
START TRANSACTION;

CREATE TABLE IF NOT EXISTS products (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(60) NOT NULL UNIQUE,
 name VARCHAR(140) NOT NULL,
 description TEXT NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 latest_version VARCHAR(40) NULL,
 max_devices_default TINYINT UNSIGNED NOT NULL DEFAULT 2,
 license_duration_days INT UNSIGNED NULL DEFAULT 90,
 installer_id BIGINT UNSIGNED NULL,
 branding_mode VARCHAR(40) NOT NULL DEFAULT 'beta',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS installers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 product_id BIGINT UNSIGNED NOT NULL,
 version VARCHAR(40) NOT NULL,
 original_name VARCHAR(255) NOT NULL,
 stored_name VARCHAR(255) NOT NULL UNIQUE,
 sha256 CHAR(64) NOT NULL,
 file_size BIGINT UNSIGNED NOT NULL,
 release_notes TEXT NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 0,
 uploaded_by VARCHAR(120) NULL,
 uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_installer_product FOREIGN KEY(product_id) REFERENCES products(id),
 KEY idx_installer_active(product_id,is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS branding_settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 mode VARCHAR(40) NOT NULL UNIQUE,
 display_name VARCHAR(140) NOT NULL,
 tagline VARCHAR(255) NULL,
 logo_path VARCHAR(255) NOT NULL,
 email_logo_path VARCHAR(255) NOT NULL,
 primary_color CHAR(7) NOT NULL DEFAULT '#102a56',
 is_active TINYINT(1) NOT NULL DEFAULT 0,
 updated_by VARCHAR(120) NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(100) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 display_name VARCHAR(140) NULL,
 status ENUM('active','disabled') NOT NULL DEFAULT 'active',
 last_login_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rate_limits (
 rate_key CHAR(64) PRIMARY KEY,
 action VARCHAR(60) NOT NULL,
 attempts INT UNSIGNED NOT NULL DEFAULT 1,
 window_started_at DATETIME NOT NULL,
 blocked_until DATETIME NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_rate_action(action), KEY idx_rate_blocked(blocked_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO products(code,name,description,status,latest_version,max_devices_default,license_duration_days,branding_mode)
VALUES('rekodguru-offline','RekodGuru Offline','Aplikasi rekod pentaksiran guru yang berfungsi secara offline.','active','0.8.0-beta',2,90,'beta')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO branding_settings(mode,display_name,tagline,logo_path,email_logo_path,primary_color,is_active)
VALUES
('beta','RekodGuru Offline (Beta)','Ujian Dalaman MRSM','assets/branding/beta/logo.png','assets/branding/beta/email-logo.png','#102a56',1),
('production','KitCikgu License Center','Lesen produk KitCikgu','assets/branding/production/logo.png','assets/branding/production/email-logo.png','#155eef',0)
ON DUPLICATE KEY UPDATE mode=VALUES(mode);

INSERT INTO system_settings(setting_key,setting_value,updated_by)
VALUES
('license_portal_mode','OPEN','migration'),
('active_branding','beta','migration'),
('admin_session_timeout_minutes','30','migration'),
('max_installer_size_mb','250','migration'),
('support_email','lesen@kitcikgu.com','migration')
ON DUPLICATE KEY UPDATE setting_key=VALUES(setting_key);

-- Tambah product_id hanya jika belum wujud.
SET @sql=IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='licenses' AND COLUMN_NAME='product_id')=0,
 'ALTER TABLE licenses ADD COLUMN product_id BIGINT UNSIGNED NULL AFTER id, ADD KEY idx_license_product(product_id)', 'SELECT 1');
PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

SET @sql=IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='license_requests' AND COLUMN_NAME='product_id')=0,
 'ALTER TABLE license_requests ADD COLUMN product_id BIGINT UNSIGNED NULL AFTER id, ADD KEY idx_request_product(product_id)', 'SELECT 1');
PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

UPDATE licenses SET product_id=(SELECT id FROM products WHERE code='rekodguru-offline' LIMIT 1) WHERE product_id IS NULL;
UPDATE license_requests r JOIN licenses l ON l.id=r.license_id SET r.product_id=l.product_id WHERE r.product_id IS NULL;

-- Benarkan satu email memiliki satu lesen bagi setiap produk.
SET @emailUnique=(SELECT INDEX_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='license_requests' AND COLUMN_NAME='email' AND NON_UNIQUE=0 AND INDEX_NAME<>'PRIMARY' LIMIT 1);
SET @sql=IF(@emailUnique IS NULL,'SELECT 1',CONCAT('ALTER TABLE license_requests DROP INDEX `',REPLACE(@emailUnique,'`','``'),'`'));
PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;
SET @sql=IF((SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='license_requests' AND INDEX_NAME='uq_request_email_product')=0,
 'ALTER TABLE license_requests ADD UNIQUE KEY uq_request_email_product(email,product_id)', 'SELECT 1');
PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

-- Audit tambahan; medan lama dikekalkan untuk compatibility.
SET @sql=IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='audit_logs' AND COLUMN_NAME='admin_id')=0,
 'ALTER TABLE audit_logs ADD COLUMN admin_id BIGINT UNSIGNED NULL, ADD COLUMN entity_type VARCHAR(60) NULL, ADD COLUMN entity_id BIGINT UNSIGNED NULL, ADD COLUMN ip_address VARCHAR(45) NULL, ADD COLUMN user_agent VARCHAR(500) NULL', 'SELECT 1');
PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

COMMIT;