-- Phase 12: commercial licensing, installation binding, feature gates and validation audit.
CREATE TABLE IF NOT EXISTS software_licenses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL,
    license_id VARCHAR(100) NOT NULL,
    customer_name VARCHAR(200) NOT NULL,
    payload_json LONGTEXT NOT NULL,
    signature_base64 TEXT NOT NULL,
    status ENUM('ACTIVE','GRACE','EXPIRED','REVOKED','INVALID') NOT NULL DEFAULT 'ACTIVE',
    issued_at DATETIME NOT NULL,
    not_before DATETIME NULL,
    expires_at DATETIME NOT NULL,
    grace_until DATETIME NULL,
    installed_by BIGINT UNSIGNED NULL,
    installed_at DATETIME NOT NULL,
    last_validated_at DATETIME NULL,
    last_validation_code VARCHAR(80) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_software_license_public (public_id),
    UNIQUE KEY uq_software_license_id (license_id),
    INDEX idx_software_license_status (status,expires_at),
    CONSTRAINT fk_software_license_installed_by FOREIGN KEY (installed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS license_installations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    installation_uuid CHAR(36) NOT NULL,
    fingerprint_hash CHAR(64) NOT NULL,
    domain VARCHAR(255) NOT NULL,
    hostname VARCHAR(255) NOT NULL,
    database_name VARCHAR(160) NOT NULL,
    first_seen_at DATETIME NOT NULL,
    last_seen_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_license_installation_uuid (installation_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS license_validation_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    software_license_id BIGINT UNSIGNED NULL,
    installation_id BIGINT UNSIGNED NULL,
    result_code VARCHAR(80) NOT NULL,
    result_message VARCHAR(500) NULL,
    domain VARCHAR(255) NULL,
    fingerprint_hash CHAR(64) NULL,
    request_id VARCHAR(80) NULL,
    ip_address VARCHAR(64) NULL,
    validated_at DATETIME NOT NULL,
    INDEX idx_license_validation_time (validated_at),
    INDEX idx_license_validation_result (result_code,validated_at),
    CONSTRAINT fk_license_validation_license FOREIGN KEY (software_license_id) REFERENCES software_licenses(id) ON DELETE SET NULL,
    CONSTRAINT fk_license_validation_installation FOREIGN KEY (installation_id) REFERENCES license_installations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS license_feature_usage (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    feature_code VARCHAR(80) NOT NULL,
    usage_date DATE NOT NULL,
    request_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    last_used_at DATETIME NOT NULL,
    UNIQUE KEY uq_license_feature_usage (feature_code,usage_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO permissions (code,name) VALUES
('license.view','View software license and deployment identity'),
('license.manage','Install and manage software license')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code IN ('SUPER_ADMIN','HOTEL_ADMIN','IT_ADMINISTRATOR') AND p.code IN ('license.view','license.manage');

INSERT IGNORE INTO role_permissions (role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.code='READ_ONLY_MANAGER' AND p.code='license.view';