-- ==========================================================
--  Trade Simulator License Validation — MariaDB Schema
--  Run as:  mysql -u root -p < schema.sql
-- ==========================================================

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

USE licensedb;

-- ----------------------------------------------------------
--  licenses — one row per license key sold
-- ----------------------------------------------------------
CREATE TABLE IF NOT EXISTS licenses (
   id               INT UNSIGNED     NOT NULL AUTO_INCREMENT,

   -- The license key itself (store hashed with SHA-256 so a
   -- DB breach doesn't expose customer keys directly).
   -- The raw key is never stored.
   key_hash         CHAR(64)         NOT NULL UNIQUE,

   -- Human-readable label (customer name / order ID etc.)
   -- Optional but useful for support
   label            VARCHAR(120)     DEFAULT NULL,

   -- Currently registered MT5 account number (NULL = not yet activated)
   registered_account  BIGINT       DEFAULT NULL,

   -- When the current account was registered (used for cooldown)
   account_registered_at  DATETIME  DEFAULT NULL,

   -- Expiry date — NULL means never expires
   expires_at       DATETIME        DEFAULT NULL,

   -- Admin kill-switch
   is_active        TINYINT(1)      NOT NULL DEFAULT 1,

   -- Audit timestamps
   created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                    ON UPDATE CURRENT_TIMESTAMP,

   PRIMARY KEY (id),
   INDEX idx_key_hash (key_hash),
   INDEX idx_account  (registered_account)
) ENGINE=InnoDB;


-- ----------------------------------------------------------
--  audit_log — every validation attempt, win or lose
-- ----------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
   id               BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,

   -- SHA-256 of the submitted key (so we can correlate without
   -- storing the raw key in logs)
   key_hash         CHAR(64)         NOT NULL,

   -- MT5 account number submitted with the request
   account          BIGINT           NOT NULL,

   -- EA build string sent with the request
   build            VARCHAR(32)      DEFAULT NULL,

   -- Client IP address
   ip               VARCHAR(45)      NOT NULL,   -- IPv6-safe

   -- Epoch timestamp sent by the EA
   client_ts        BIGINT           DEFAULT NULL,

   -- Result code returned to the client
   result           VARCHAR(24)      NOT NULL,

   -- Extra notes (e.g. "HMAC_FAIL", "TS_SKEW:412", "COOLDOWN:3d")
   note             VARCHAR(200)     DEFAULT NULL,

   -- Server-side timestamp
   logged_at        DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,

   PRIMARY KEY (id),
   INDEX idx_key_hash  (key_hash),
   INDEX idx_account   (account),
   INDEX idx_logged_at (logged_at),
   INDEX idx_ip        (ip)
) ENGINE=InnoDB;


-- ----------------------------------------------------------
--  Example: inserting a license key manually
--
--  1. Generate a random key however you like, e.g.:
--       python3 -c "import secrets; print(secrets.token_urlsafe(24))"
--     example output:  Xk9mP2rQvLnT8wYdJcFs6A
--
--  2. Hash it for storage:
--       python3 -c "import hashlib; print(hashlib.sha256(b'YOUR_KEY_HERE').hexdigest())"
--
--  3. Insert the row:
--
--  INSERT INTO licenses (key_hash, label, expires_at)
--  VALUES (
--     'paste_sha256_hex_here',
--     'Customer Name / Order #1234',
--     '2027-01-01 00:00:00'    -- or NULL for no expiry
--  );
--
-- ----------------------------------------------------------
