-- Techvai Billing — Account-based licensing (Adobe-style sign-in)
-- Adds accounts + account_devices alongside the existing `licenses` table
-- (that older key-based system keeps working for any customers already on
-- it — this is a new, additional system, not a replacement/migration).
--
-- Import this once: phpMyAdmin -> Import, or
--   mysql -u user -p dbname < schema_accounts.sql

CREATE TABLE IF NOT EXISTS accounts (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    email          VARCHAR(191) NOT NULL UNIQUE,
    password_hash  VARCHAR(255) NOT NULL,
    customer_name  VARCHAR(191) NOT NULL DEFAULT '',
    -- How many computers can be signed in to this account at the same time.
    -- Adobe-style default is 2.
    max_devices    INT NOT NULL DEFAULT 2,
    status         ENUM('active', 'revoked') NOT NULL DEFAULT 'active',
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per computer currently signed in to an account. When max_devices
-- is reached, signing in on a new computer requires removing one of these
-- rows first (the "choose a device to sign out" screen).
CREATE TABLE IF NOT EXISTS account_devices (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    account_id          INT NOT NULL,
    device_fingerprint  VARCHAR(191) NOT NULL,
    device_name         VARCHAR(191) NOT NULL DEFAULT 'Unknown Device',
    signed_in_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_account_device (account_id, device_fingerprint),
    CONSTRAINT fk_account_devices_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_accounts_status ON accounts(status);
CREATE INDEX idx_account_devices_account ON account_devices(account_id);