-- license-php/database/schema.sql
-- Presensi Digital License System Database Schema (MySQL 8.x / MariaDB compatible)
-- Character set: utf8mb4

CREATE TABLE IF NOT EXISTS licenses (
    id VARCHAR(64) NOT NULL PRIMARY KEY,
    license_key VARCHAR(64) NOT NULL UNIQUE,
    license_key_hash VARCHAR(64) NOT NULL,
    product VARCHAR(64) NOT NULL DEFAULT 'presensi-smansa-pro',
    plan VARCHAR(32) NOT NULL DEFAULT 'PRO',
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(255) NULL,
    domain VARCHAR(255) NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'unused',
    license_type VARCHAR(32) NOT NULL DEFAULT 'Subscription',
    max_activation INT NOT NULL DEFAULT 1,
    activation_count INT NOT NULL DEFAULT 0,
    activated_at VARCHAR(64) NULL,
    expires_at VARCHAR(64) NULL,
    last_verified_at VARCHAR(64) NULL,
    created_at VARCHAR(64) NOT NULL,
    updated_at VARCHAR(64) NOT NULL,
    notes TEXT NULL,
    INDEX idx_licenses_hash (license_key_hash),
    INDEX idx_licenses_domain (domain),
    INDEX idx_licenses_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS license_audit_logs (
    id VARCHAR(64) NOT NULL PRIMARY KEY,
    license_id VARCHAR(64) NULL,
    action VARCHAR(64) NOT NULL,
    domain VARCHAR(255) NULL,
    ip_address VARCHAR(64) NULL,
    user_agent VARCHAR(512) NULL,
    metadata TEXT NULL,
    created_at VARCHAR(64) NOT NULL,
    INDEX idx_logs_lic_id (license_id),
    INDEX idx_logs_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
