-- XIBATA A12+ Black Screen — MySQL/MariaDB schema
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS admin_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT 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;

CREATE TABLE IF NOT EXISTS plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  code VARCHAR(40) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  duration_days INT UNSIGNED NOT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  session_limit INT UNSIGNED NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  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;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  email VARCHAR(190) NOT NULL UNIQUE,
  display_name VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active','disabled','suspended') NOT NULL DEFAULT 'active',
  plan_id INT UNSIGNED NULL,
  credits DECIMAL(12,2) NOT NULL DEFAULT 0,
  dhru_api_key VARCHAR(80) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_login_at DATETIME NULL,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_users_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS licenses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NOT NULL,
  license_key VARCHAR(64) NOT NULL UNIQUE,
  status ENUM('ATIVA','EXPIRADA','SUSPENSA','CANCELADA','PENDENTE') NOT NULL DEFAULT 'PENDENTE',
  started_at DATETIME NULL,
  expires_at DATETIME NULL,
  device_name VARCHAR(120) NULL,
  notes VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_lic_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_lic_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE RESTRICT,
  INDEX idx_lic_user_status (user_id, status),
  INDEX idx_lic_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS license_history (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  license_id INT UNSIGNED NOT NULL,
  action VARCHAR(40) NOT NULL,
  detail VARCHAR(255) NULL,
  admin_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_lh_lic FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sessions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id VARCHAR(16) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  client_name VARCHAR(120) NULL,
  technician_name VARCHAR(120) NULL,
  status ENUM('created','active','ended') NOT NULL DEFAULT 'created',
  started_at DATETIME NULL,
  ended_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sess_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_sess_user (user_id),
  INDEX idx_sess_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_tokens (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  last_used_at DATETIME NULL,
  user_agent VARCHAR(190) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tok_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_tok_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_type ENUM('admin','user','system') NOT NULL DEFAULT 'system',
  actor_id INT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  entity VARCHAR(40) NULL,
  entity_id INT UNSIGNED NULL,
  ip VARCHAR(45) NULL,
  detail TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(80) PRIMARY KEY,
  setting_value TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dhru_fusion_config (
  id TINYINT UNSIGNED PRIMARY KEY DEFAULT 1,
  api_url VARCHAR(255) NULL,
  api_user VARCHAR(120) NULL,
  api_key_encrypted TEXT NULL,
  status ENUM('disabled','configured','online','offline') NOT NULL DEFAULT 'disabled',
  last_check_at DATETIME NULL,
  last_message VARCHAR(255) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dhru_services (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_name VARCHAR(120) NOT NULL DEFAULT 'XIBATA',
  service_name VARCHAR(160) NOT NULL,
  slug VARCHAR(80) NOT NULL,
  service_type ENUM('IMEI','SERVER','FILE') NOT NULL DEFAULT 'IMEI',
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  description VARCHAR(255) NULL,
  time_text VARCHAR(40) NOT NULL DEFAULT '1-24 Hours',
  fulfillment ENUM('manual','license') NOT NULL DEFAULT 'manual',
  license_days INT UNSIGNED NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_dhru_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dhru_orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  service_id INT UNSIGNED NOT NULL,
  service_slug VARCHAR(80) NOT NULL,
  input_value VARCHAR(190) NOT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('pending','processing','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  result_code TEXT NULL,
  result_data LONGTEXT NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  INDEX idx_dhru_user (user_id),
  INDEX idx_dhru_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO plans (name, code, description, duration_days, price, status) VALUES
('30 dias', '30', 'Plano de 30 dias', 30, 0, 'active'),
('60 dias', '60', 'Plano de 60 dias', 60, 0, 'active'),
('90 dias', '90', 'Plano de 90 dias', 90, 0, 'active')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO dhru_fusion_config (id, status) VALUES (1, 'disabled')
ON DUPLICATE KEY UPDATE id = 1;

INSERT INTO settings (setting_key, setting_value) VALUES
('panel_name', 'XIBATA A12+'),
('client_enabled', '1'),
('client_min_version', '1.0.0'),
('client_latest_version', '1.1.0'),
('client_block_message', 'Painel em manutenção. Tente novamente em instantes.'),
('dhru_group_name', 'XIBATA Black Screen'),
('dhru_currency', 'USD')
ON DUPLICATE KEY UPDATE setting_key = setting_key;

INSERT INTO dhru_services (group_name, service_name, slug, service_type, price, description, time_text, fulfillment, license_days, sort_order) VALUES
('XIBATA Black Screen', 'Black Screen 30 dias', 'blackscreen-30', 'IMEI', 10, 'Ativa 30 dias de licença. Informe o usuário no IMEI.', 'Instant', 'license', 30, 1),
('XIBATA Black Screen', 'Black Screen 60 dias', 'blackscreen-60', 'IMEI', 18, 'Ativa 60 dias de licença. Informe o usuário no IMEI.', 'Instant', 'license', 60, 2),
('XIBATA Black Screen', 'Black Screen 90 dias', 'blackscreen-90', 'IMEI', 25, 'Ativa 90 dias de licença. Informe o usuário no IMEI.', 'Instant', 'license', 90, 3)
ON DUPLICATE KEY UPDATE service_name = VALUES(service_name);

SET FOREIGN_KEY_CHECKS = 1;