CREATE DATABASE IF NOT EXISTS omnicast_portal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE omnicast_portal;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('user','admin') NOT NULL DEFAULT 'user',
 status ENUM('active','suspended') NOT NULL DEFAULT 'active',
 email_verified_at DATETIME NULL,
 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,
 INDEX(role), INDEX(status)
) ENGINE=InnoDB;

CREATE TABLE plans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 slug VARCHAR(120) NOT NULL UNIQUE,
 description TEXT NULL,
 price_kobo BIGINT UNSIGNED NOT NULL,
 duration_days INT UNSIGNED NOT NULL,
 max_devices INT UNSIGNED NOT NULL DEFAULT 1,
 features_json JSON NULL,
 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;

CREATE TABLE devices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 device_id VARCHAR(128) NOT NULL UNIQUE,
 device_secret_hash CHAR(64) NOT NULL,
 device_name VARCHAR(160) NULL,
 user_id BIGINT UNSIGNED NULL,
 activation_code VARCHAR(32) NULL UNIQUE,
 activation_expires_at DATETIME NULL,
 status ENUM('pending','active','revoked') NOT NULL DEFAULT 'pending',
 last_seen_at DATETIME NULL,
 last_ip VARCHAR(64) NULL,
 app_version VARCHAR(40) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(user_id,status), INDEX(activation_expires_at)
) ENGINE=InnoDB;

CREATE TABLE subscriptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 plan_id BIGINT UNSIGNED NOT NULL,
 reference VARCHAR(100) NOT NULL UNIQUE,
 amount_kobo BIGINT UNSIGNED NOT NULL,
 status ENUM('pending','active','expired','cancelled','failed') NOT NULL DEFAULT 'pending',
 starts_at DATETIME NULL,
 ends_at DATETIME NULL,
 provider_payload JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(plan_id) REFERENCES plans(id) ON DELETE RESTRICT,
 INDEX(user_id,status,ends_at)
) ENGINE=InnoDB;

CREATE TABLE license_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 device_id BIGINT UNSIGNED NULL,
 user_id BIGINT UNSIGNED NULL,
 event_type VARCHAR(80) NOT NULL,
 details JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE SET NULL,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(event_type), INDEX(created_at)
) ENGINE=InnoDB;

CREATE TABLE payment_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 event_id VARCHAR(190) NULL UNIQUE,
 event_type VARCHAR(80) NOT NULL,
 reference VARCHAR(100) NULL,
 payload JSON NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 action VARCHAR(120) NOT NULL,
 details JSON NULL,
 ip_address VARCHAR(64) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(action), INDEX(created_at)
) ENGINE=InnoDB;

INSERT INTO plans (name,slug,description,price_kobo,duration_days,max_devices,features_json)
VALUES
('Free','free','Essential presentation tools',0,3650,1,'["bible","media_player","preview_screen","multiview","live_stage","projector"]'),
('Monthly','monthly','Full access for 30 days',1500000,30,1,'["bible","media_player","preview_screen","multiview","live_stage","projector","songs","graphics","lower_third","voice_detection","capture","streaming"]'),
('Yearly','yearly','Full access for 365 days',15000000,365,3,'["bible","media_player","preview_screen","multiview","live_stage","projector","songs","graphics","lower_third","voice_detection","capture","streaming","offline_bible","workspaces","hotkeys"]')
ON DUPLICATE KEY UPDATE name=VALUES(name);