CREATE DATABASE IF NOT EXISTS envato_licenses
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE envato_licenses;

CREATE TABLE IF NOT EXISTS Verification (
  id INT NOT NULL AUTO_INCREMENT,
  purchaseCode VARCHAR(255) NOT NULL,
  itemId VARCHAR(50) NOT NULL,
  ipAddress VARCHAR(45) NOT NULL,
  status VARCHAR(20) NOT NULL,
  reason TEXT NULL,
  buyerUsername VARCHAR(255) NULL,
  itemName VARCHAR(255) NULL,
  purchaseDate VARCHAR(50) NULL,
  supportedUntil VARCHAR(50) NULL,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  INDEX Verification_purchaseCode_idx (purchaseCode),
  INDEX Verification_itemId_idx (itemId),
  INDEX Verification_ipAddress_idx (ipAddress),
  INDEX Verification_status_idx (status),
  INDEX Verification_createdAt_idx (createdAt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS RateLimit (
  id INT NOT NULL AUTO_INCREMENT,
  ipAddress VARCHAR(45) NOT NULL,
  requestCount INT NOT NULL DEFAULT 1,
  windowStart DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  INDEX RateLimit_ipAddress_idx (ipAddress),
  INDEX RateLimit_windowStart_idx (windowStart)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS AdminUser (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  passwordHash VARCHAR(255) NOT NULL,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY AdminUser_username_key (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;