CREATE TABLE IF NOT EXISTS plans (
  id            VARCHAR(36)      NOT NULL PRIMARY KEY,
  name          VARCHAR(100)     NOT NULL,
  max_domains   INT              NOT NULL DEFAULT 1,
  max_accounts  INT              NOT NULL DEFAULT 1,
  max_emails    INT              NOT NULL DEFAULT 10,
  max_databases INT              NOT NULL DEFAULT 5,
  max_disk_gb   INT              NOT NULL DEFAULT 10,
  price_monthly DECIMAL(10,2)    NOT NULL DEFAULT 0.00,
  is_active     TINYINT(1)       NOT NULL DEFAULT 1,
  created_at    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS licenses (
  id                 VARCHAR(36)  NOT NULL PRIMARY KEY,
  plan_id            VARCHAR(36)  NOT NULL,
  owner_email        VARCHAR(255) NOT NULL,
  owner_name         VARCHAR(255) NOT NULL,
  server_ip          VARCHAR(45)  NULL,
  server_hostname    VARCHAR(255) NULL,
  server_fingerprint VARCHAR(512) NULL,
  status             VARCHAR(20)  NOT NULL DEFAULT 'active',
  issued_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at         DATETIME     NULL,
  last_verified      DATETIME     NULL,
  notes              TEXT         NULL,
  FOREIGN KEY (plan_id) REFERENCES plans(id) ON UPDATE CASCADE,
  INDEX idx_owner_email (owner_email),
  INDEX idx_status      (status),
  INDEX idx_server_ip   (server_ip)
);

CREATE TABLE IF NOT EXISTS license_tokens (
  id          VARCHAR(36)  NOT NULL PRIMARY KEY,
  license_id  VARCHAR(36)  NOT NULL,
  token_hash  VARCHAR(512) NOT NULL,
  issued_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at  DATETIME     NOT NULL,
  revoked     TINYINT(1)   NOT NULL DEFAULT 0,
  revoked_at  DATETIME     NULL,
  FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE,
  INDEX idx_license_id (license_id),
  INDEX idx_token_hash (token_hash)
);

CREATE TABLE IF NOT EXISTS validation_log (
  id                 BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
  license_id         VARCHAR(36)  NOT NULL,
  server_ip          VARCHAR(45)  NULL,
  server_fingerprint VARCHAR(512) NULL,
  result             VARCHAR(30)  NOT NULL,
  checked_at         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_license_id (license_id),
  INDEX idx_checked_at (checked_at)
);

CREATE TABLE IF NOT EXISTS admins (
  id            VARCHAR(36)  NOT NULL PRIMARY KEY,
  username      VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  email         VARCHAR(255) NOT NULL UNIQUE,
  role          VARCHAR(20)  NOT NULL DEFAULT 'admin',
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  last_login    DATETIME     NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT IGNORE INTO plans (id, name, max_domains, max_accounts, max_emails, max_databases, max_disk_gb, price_monthly) VALUES
  ('00000000-0000-0000-0000-000000000001', 'Free',   1,  1,  5,   2,   5,    0.00),
  ('00000000-0000-0000-0000-000000000002', 'Solo',   5,  5,  50,  10,  50,   9.99),
  ('00000000-0000-0000-0000-000000000003', 'Host',   50, 50, 500, 100, 250,  29.99),
  ('00000000-0000-0000-0000-000000000004', 'Agency', -1, -1, -1,  -1,  -1,   79.99);