-- ==========================================================
--  Migration: per-product license entitlements
--  Run as:  mysql -u root -p TradeSimulatorLicense < migration_license_products.sql
--
--  Purely additive — does NOT alter the existing `licenses` table in
--  any way. /wd/tradesimulator/main.py (tsvalid) never queries this new
--  table and is completely unaffected by this migration.
-- ==========================================================

USE TradeSimulatorLicense;

CREATE TABLE IF NOT EXISTS license_products (
   license_id   INT UNSIGNED  NOT NULL,
   -- Product identifiers used so far: 'tradesimulator', 'trend_degree'.
   -- Add more as needed (e.g. a future 'trade_manager') — no schema
   -- change required, just new rows.
   product      VARCHAR(32)   NOT NULL,
   created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (license_id, product),
   CONSTRAINT fk_license_products_license
       FOREIGN KEY (license_id) REFERENCES licenses(id)
       ON DELETE CASCADE
) ENGINE=InnoDB;

-- Backfill: every EXISTING license is entitled to tradesimulator only.
-- Per decision: do NOT grant trend_degree access automatically — any
-- existing customer who should also get TD access needs an explicit
-- row added (see the INSERT example at the bottom of this file).
INSERT INTO license_products (license_id, product)
SELECT id, 'tradesimulator' FROM licenses
ON DUPLICATE KEY UPDATE license_id = license_id;  -- no-op if re-run

-- ----------------------------------------------------------
--  Example: granting an existing or new license trend_degree access
--
--  INSERT INTO license_products (license_id, product)
--  VALUES (<license id>, 'trend_degree');
--
--  Find a license's id by its label or key_hash, e.g.:
--  SELECT id, label FROM licenses WHERE label LIKE '%customer name%';
-- ----------------------------------------------------------

-- Sanity check after running — should show 'tradesimulator' for every
-- existing license, and nothing for 'trend_degree' yet:
-- SELECT l.id, l.label, GROUP_CONCAT(lp.product) AS products
-- FROM licenses l LEFT JOIN license_products lp ON lp.license_id = l.id
-- GROUP BY l.id;
