-- MySQL schema for the Dakhl license server (PHP).
-- The app auto-creates these on first run; this file is for manual import
-- (phpMyAdmin) or reference. Safe to run repeatedly.

CREATE TABLE IF NOT EXISTS licenses (
  id                       VARCHAR(36)  NOT NULL PRIMARY KEY,
  license_key              VARCHAR(191) NOT NULL UNIQUE,
  customer_name            VARCHAR(191) NOT NULL,
  note                     TEXT NULL,
  plan                     VARCHAR(16)  NOT NULL DEFAULT 'perpetual',
  max_activations          INT          NOT NULL DEFAULT 1,
  status                   VARCHAR(16)  NOT NULL DEFAULT 'active',
  subscription_expires_at  VARCHAR(32)  NULL,
  allow_auto_migrate       INT          NOT NULL DEFAULT 1,
  created_at               VARCHAR(32)  NOT NULL,
  updated_at               VARCHAR(32)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activations (
  id                VARCHAR(36)  NOT NULL PRIMARY KEY,
  license_id        VARCHAR(36)  NOT NULL,
  device_hash       VARCHAR(64)  NOT NULL,
  fp_json           TEXT         NOT NULL,
  label             VARCHAR(191) NULL,
  status            VARCHAR(16)  NOT NULL DEFAULT 'active',
  app_version       VARCHAR(32)  NULL,
  first_seen_at     VARCHAR(32)  NOT NULL,
  last_seen_at      VARCHAR(32)  NOT NULL,
  last_ip           VARCHAR(64)  NULL,
  lease_expires_at  VARCHAR(32)  NULL,
  revoked_at        VARCHAR(32)  NULL,
  revoked_reason    VARCHAR(255) NULL,
  UNIQUE (license_id, device_hash),
  INDEX idx_activations_license (license_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_log (
  id             VARCHAR(36) NOT NULL PRIMARY KEY,
  license_id     VARCHAR(36) NULL,
  activation_id  VARCHAR(36) NULL,
  event          VARCHAR(64) NOT NULL,
  detail         TEXT NULL,
  ip             VARCHAR(64) NULL,
  created_at     VARCHAR(32) NOT NULL,
  INDEX idx_audit_license (license_id),
  INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;