SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS plans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(40) NOT NULL UNIQUE,
 name VARCHAR(100) NOT NULL, license_type ENUM('beta','lifetime','subscription') NOT NULL,
 default_max_devices TINYINT UNSIGNED NOT NULL DEFAULT 2, default_duration_days INT UNSIGNED NULL,
 features_json JSON NULL, is_active TINYINT(1) NOT NULL DEFAULT 1,
 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 licenses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, license_key VARCHAR(80) NOT NULL UNIQUE,
 plan_id BIGINT UNSIGNED NOT NULL, customer_reference VARCHAR(120) NULL,
 status ENUM('active','suspended','revoked') NOT NULL DEFAULT 'active', starts_at DATE NOT NULL,
 expires_at DATE NULL, max_devices TINYINT UNSIGNED NOT NULL DEFAULT 2, notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_license_plan FOREIGN KEY(plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS activations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, license_id BIGINT UNSIGNED NOT NULL,
 device_id VARCHAR(100) NOT NULL, app_version VARCHAR(40) NOT NULL,
 status ENUM('active','deactivated') NOT NULL DEFAULT 'active',
 activated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deactivated_at DATETIME NULL, UNIQUE KEY uq_license_device(license_id,device_id), KEY idx_activation_device(device_id),
 CONSTRAINT fk_activation_license FOREIGN KEY(license_id) REFERENCES licenses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, action VARCHAR(80) NOT NULL,
 license_id BIGINT UNSIGNED NULL, activation_id BIGINT UNSIGNED NULL, ip_hash CHAR(64) NULL,
 details_json JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_audit_created(created_at),
 CONSTRAINT fk_audit_license FOREIGN KEY(license_id) REFERENCES licenses(id) ON DELETE SET NULL,
 CONSTRAINT fk_audit_activation FOREIGN KEY(activation_id) REFERENCES activations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO plans(code,name,license_type,default_max_devices,default_duration_days,features_json) VALUES
('beta','Beta Tester','beta',2,90,JSON_OBJECT('all_features',true)),
('lifetime','Lifetime','lifetime',2,NULL,JSON_OBJECT('all_features',true)),
('annual','Langganan Tahunan','subscription',2,365,JSON_OBJECT('all_features',true));