-- 002 — App version + licensing + update channel
-- Idempotent: safe to re-apply. New tables only; never touches existing ones.

-- The single-row table that says "what version of the CMS is running here".
-- install/release.php bumps this on the core; core/update.php's installInto
-- seeds it to BASELINE on a fresh DB (handled in core/schema.php).
CREATE TABLE IF NOT EXISTS `sn_app_version` (
  `id` TINYINT UNSIGNED NOT NULL PRIMARY KEY,
  `version` VARCHAR(20) NOT NULL DEFAULT '0.0.0',
  `codename` VARCHAR(40) NOT NULL DEFAULT '',
  `released_at` DATETIME NULL,
  `notes` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Licenses issued by the core. One per host. Core admin manages via
-- controllers/license.php. Clients read their own row via the validate API.
CREATE TABLE IF NOT EXISTS `sn_licenses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `license_key` CHAR(37) NOT NULL,
  `host` VARCHAR(160) NOT NULL,
  `plan` ENUM('basic','pro','agency') NOT NULL DEFAULT 'basic',
  `status` ENUM('active','suspended','revoked') NOT NULL DEFAULT 'active',
  `features` JSON NULL,
  `issued_at` DATETIME NOT NULL,
  `expires_at` DATETIME NULL,
  `last_validated_at` DATETIME NULL,
  `notes` TEXT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_license_key` (`license_key`),
  UNIQUE KEY `uniq_host` (`host`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Append-only audit of every update the client applied (or attempted).
-- Read by controllers/update.php on the core to show per-client status.
CREATE TABLE IF NOT EXISTS `sn_update_log` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `host` VARCHAR(160) NOT NULL,
  `license_key` CHAR(37) NOT NULL,
  `version_from` VARCHAR(20) NOT NULL DEFAULT '',
  `version_to` VARCHAR(20) NOT NULL DEFAULT '',
  `status` ENUM('started','ok','failed','rolled_back') NOT NULL DEFAULT 'started',
  `started_at` DATETIME NOT NULL,
  `finished_at` DATETIME NULL,
  `log` TEXT NULL,
  `manifest` JSON NULL,
  PRIMARY KEY (`id`),
  KEY `idx_host` (`host`),
  KEY `idx_started` (`started_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed the version row if missing. installInto seeds BASELINE for fresh
-- DBs; this covers existing DBs that haven't run the migration yet.
INSERT IGNORE INTO `sn_app_version` (`id`,`version`,`codename`) VALUES (1,'1.0.0','ignis');