CREATE TABLE IF NOT EXISTS licenses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 key_id CHAR(8) CHARACTER SET ascii COLLATE ascii_bin NOT NULL UNIQUE,
 secret_hash VARCHAR(255) NOT NULL,
 customer_name VARCHAR(160) NOT NULL,
 status ENUM('active','suspended','revoked') NOT NULL DEFAULT 'active',
 max_devices SMALLINT UNSIGNED NOT NULL DEFAULT 1,
 expires_at DATETIME NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(status, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS license_devices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 license_id BIGINT UNSIGNED NOT NULL,
 device_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
 activation_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
 activated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 last_seen_at DATETIME NULL,
 revoked_at DATETIME NULL,
 UNIQUE KEY license_device(license_id, device_hash),
 FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS license_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 license_id BIGINT UNSIGNED NULL,
 event_type VARCHAR(40) NOT NULL,
 ip_hash CHAR(64) NOT NULL,
 occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX (ip_hash,occurred_at),
 FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS license_admins (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;