CREATE DATABASE IF NOT EXISTS olasteve_licenses CHARACTER SET utf8mb4;
USE olasteve_licenses;

-- Products you sell — HoneySub today, room for more later
CREATE TABLE IF NOT EXISTS products (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  slug         VARCHAR(50) NOT NULL UNIQUE,
  price        DECIMAL(12,2) DEFAULT NULL,
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- A purchase creates this row IMMEDIATELY with just the buyer's email —
-- license_key stays NULL until they actually claim it during installation.
-- This is the core of the "automatic" flow: nothing here requires you to
-- manually generate or send anything.
CREATE TABLE IF NOT EXISTS license_keys (
  id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id        INT UNSIGNED NOT NULL,
  license_key       VARCHAR(100) DEFAULT NULL UNIQUE,
  buyer_name        VARCHAR(150) DEFAULT NULL,
  buyer_email       VARCHAR(150) NOT NULL,
  order_reference   VARCHAR(100) DEFAULT NULL,
  status            ENUM('pending','active','revoked') NOT NULL DEFAULT 'pending',
  bound_domain      VARCHAR(255) DEFAULT NULL,
  purchased_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  claimed_at        DATETIME DEFAULT NULL,
  last_verified_at  DATETIME DEFAULT NULL,
  notes             TEXT DEFAULT NULL,
  KEY buyer_email_idx (buyer_email),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE IF NOT EXISTS license_admins (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email    VARCHAR(150) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS license_activity_log (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  license_key  VARCHAR(100) DEFAULT NULL,
  buyer_email  VARCHAR(150) DEFAULT NULL,
  event        VARCHAR(50) NOT NULL,
  domain       VARCHAR(255) DEFAULT NULL,
  ip_address   VARCHAR(45) DEFAULT NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT IGNORE INTO products (name, slug, is_active) VALUES ('HoneySub', 'honeysub', 1);

SELECT 'License server schema ready' AS result;