CREATE TABLE licenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_name VARCHAR(255) NOT NULL,
    license_key VARCHAR(255) NOT NULL UNIQUE,
    primary_domain VARCHAR(255) NOT NULL,
    status ENUM('active','suspended','expired') DEFAULT 'active',
    allowed_domains INT DEFAULT 1,
    expires_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE license_domains (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_id INT NOT NULL,
    domain_name VARCHAR(255) NOT NULL,
    first_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_license_domain (license_id, domain_name),
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE
);

CREATE TABLE usage_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_id INT NULL,
    domain_name VARCHAR(255) NOT NULL,
    ip_address VARCHAR(64) NULL,
    event_type VARCHAR(100) NOT NULL,
    details TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE SET NULL
);

INSERT INTO licenses (client_name, license_key, primary_domain, status, allowed_domains, expires_at)
VALUES ('Elyon College', 'ELYON-2026-001', 'elyoncollege.com', 'active', 1, '2027-04-12 23:59:59');