-- PacForge Platform Kernel V1.0.0 / V1.1.0 Core Infrastructure
-- Non-destructive and safe to rerun.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS pf_schema_migrations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    component VARCHAR(50) NOT NULL,
    migration VARCHAR(150) NOT NULL,
    version VARCHAR(30) NOT NULL,
    checksum VARCHAR(64) NULL,
    applied_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pf_schema_migrations_component_migration (component, migration)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pf_workspaces (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    workspace_code VARCHAR(50) NOT NULL,
    workspace_name VARCHAR(150) NOT NULL,
    workspace_version VARCHAR(30) NULL,
    minimum_kernel_version VARCHAR(30) NULL,
    base_path VARCHAR(255) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pf_workspaces_code (workspace_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pf_system_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    occurred_at DATETIME NOT NULL,
    level VARCHAR(20) NOT NULL,
    request_id VARCHAR(64) NULL,
    workspace_code VARCHAR(50) NULL,
    platform_solution_id BIGINT UNSIGNED NULL,
    category VARCHAR(100) NULL,
    message TEXT NOT NULL,
    context_json LONGTEXT NULL,
    PRIMARY KEY (id),
    KEY idx_pf_system_logs_occurred_at (occurred_at),
    KEY idx_pf_system_logs_request_id (request_id),
    KEY idx_pf_system_logs_workspace_code (workspace_code),
    KEY idx_pf_system_logs_platform_solution_id (platform_solution_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO pf_workspaces (
    workspace_code,
    workspace_name,
    workspace_version,
    minimum_kernel_version,
    base_path,
    active,
    created_at,
    updated_at
)
VALUES (
    'BUSINESS',
    'PacForge Business Studio',
    '2.0.1',
    '1.1.0',
    '/business',
    1,
    NOW(),
    NOW()
)
ON DUPLICATE KEY UPDATE
    workspace_name = VALUES(workspace_name),
    workspace_version = VALUES(workspace_version),
    minimum_kernel_version = VALUES(minimum_kernel_version),
    base_path = VALUES(base_path),
    active = VALUES(active),
    updated_at = NOW();

INSERT INTO pf_schema_migrations (
    component,
    migration,
    version,
    checksum,
    applied_at
)
VALUES (
    'kernel',
    '001_kernel_core',
    '1.1.0',
    NULL,
    NOW()
)
ON DUPLICATE KEY UPDATE
    version = VALUES(version);