-- MI Business System Portal for MySQL 8 / MariaDB 10.6+.
-- Run once in sootna_mismaiel DB; utf8mb4 is mandatory.
CREATE TABLE IF NOT EXISTS portal_settings (
  setting_key varchar(60) PRIMARY KEY,
  setting_value varchar(255) NOT NULL,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO portal_settings(setting_key, setting_value) VALUES
 ('admin_path','admin'),('captcha_type','alnum'),('captcha_length','6');
CREATE TABLE IF NOT EXISTS portal_admins (
  id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username varchar(100) NOT NULL UNIQUE,
  password_hash varchar(255) NOT NULL,
  totp_nonce binary(24) NULL,
  totp_ciphertext varbinary(128) NULL,
  last_totp_step bigint NOT NULL DEFAULT -1,
  role enum('owner','operator') NOT NULL DEFAULT 'owner',
  is_active tinyint(1) NOT NULL DEFAULT 1,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS portal_attempts (
  id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ip_hash char(64) NOT NULL,
  action varchar(32) NOT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX ix_attempts(ip_hash,action,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS portal_cards (
  id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  card_serial varchar(45) NOT NULL UNIQUE,
  code_hash char(64) NOT NULL UNIQUE,
  plan_code varchar(60) NOT NULL,
  activation_mode enum('online_once','offline_manual') NOT NULL,
  status enum('available','redeemed','disabled') NOT NULL DEFAULT 'available',
  expires_at datetime NULL,
  redeemed_at datetime NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS portal_activation_pool (
  id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  license_public_id varchar(120) NOT NULL UNIQUE,
  company_id char(36) NOT NULL,
  encrypted_code blob NOT NULL,
  nonce binary(24) NOT NULL,
  plan_code varchar(60) NOT NULL,
  activation_mode enum('online_once') NOT NULL,
  status enum('available','assigned','disabled') NOT NULL DEFAULT 'available',
  assigned_card_id bigint UNSIGNED NULL UNIQUE,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (assigned_card_id) REFERENCES portal_cards(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS portal_requests (
  id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference_code varchar(40) NOT NULL UNIQUE,
  card_id bigint UNSIGNED NULL,
  full_name varchar(140) NOT NULL,
  email varchar(254) NOT NULL,
  phone varchar(25) NOT NULL,
  plan_code varchar(60) NOT NULL,
  activation_mode enum('online_once','offline_manual') NOT NULL,
  status enum('new','waiting_pool','waiting_offline_request','ready_for_offline_request','waiting_offline_signing','issued','rejected') NOT NULL DEFAULT 'new',
  authority_license_id varchar(120) NULL,
  authority_company_id char(36) NULL,
  offline_request_nonce binary(24) NULL,
  offline_request_encrypted MEDIUMBLOB NULL,
  offline_submitted_at datetime NULL,
  notes varchar(500) NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (card_id) REFERENCES portal_cards(id),
  UNIQUE KEY uq_request_card_once (card_id),
  INDEX ix_portal_requests(status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS portal_audit (
  id bigint UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor varchar(120) NOT NULL,
  event varchar(100) NOT NULL,
  target varchar(120) NULL,
  ip_hash char(64) NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;