-- ============================================================
-- License Management System - Database Schema
-- PHP 7.3 / MySQL 5.7+ compatible
-- ============================================================

CREATE TABLE IF NOT EXISTS `admin_users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(60) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `clients` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) DEFAULT NULL,
  `domain` VARCHAR(190) NOT NULL COMMENT 'e.g. panel.example.com (no scheme, no www)',
  `license_key` VARCHAR(64) NOT NULL,
  `secret_key` VARCHAR(128) NOT NULL COMMENT 'HMAC shared secret, only this client + server know it',
  `plan_type` ENUM('lifetime','rental') NOT NULL DEFAULT 'rental',
  `status` ENUM('active','paused','suspended','expired','revoked') NOT NULL DEFAULT 'active',
  `rental_expires_at` DATETIME DEFAULT NULL,
  `grace_hours` INT UNSIGNED NOT NULL DEFAULT 24 COMMENT 'fail-open window if client cannot reach server',
  `check_interval_seconds` INT UNSIGNED NOT NULL DEFAULT 120,
  `broadcast_message` VARCHAR(255) DEFAULT NULL COMMENT 'optional banner shown inside the panel even when active',
  `last_seen_at` DATETIME DEFAULT NULL,
  `last_seen_ip` VARCHAR(64) DEFAULT NULL,
  `last_nonce` VARCHAR(64) DEFAULT NULL,
  `notes` TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_license_key` (`license_key`),
  KEY `idx_domain` (`domain`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `license_logs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` INT UNSIGNED NOT NULL,
  `action` VARCHAR(60) NOT NULL COMMENT 'e.g. created, paused, resumed, suspended, revoked, extended, edited',
  `status_before` VARCHAR(20) DEFAULT NULL,
  `status_after` VARCHAR(20) DEFAULT NULL,
  `actor` VARCHAR(100) DEFAULT NULL COMMENT 'admin username who performed the action, or "system"',
  `message` VARCHAR(255) DEFAULT NULL,
  `ip` VARCHAR(64) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_client` (`client_id`),
  CONSTRAINT `fk_logs_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `check_ins` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` INT UNSIGNED NOT NULL,
  `domain_seen` VARCHAR(190) DEFAULT NULL,
  `ip` VARCHAR(64) DEFAULT NULL,
  `status_returned` VARCHAR(20) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_client_time` (`client_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
  ('default_grace_hours', '24'),
  ('default_check_interval_seconds', '120')
ON DUPLICATE KEY UPDATE setting_key = setting_key;