-- GoScribe CMS Rebuild
-- Schema: v1.0.0-foundation
-- Target: MariaDB 10.4+ / MySQL 8+
-- Principle: Database-first, no JSON runtime files

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS gs_options (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    option_group VARCHAR(100) NOT NULL DEFAULT 'general',
    option_name VARCHAR(191) NOT NULL,
    option_value LONGTEXT NULL,
    option_type VARCHAR(50) NOT NULL DEFAULT 'string',
    is_autoload TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    description VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_option_name (option_name),
    KEY idx_options_group (option_group),
    KEY idx_options_autoload (is_autoload, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_login VARCHAR(100) NOT NULL,
    user_email VARCHAR(191) NOT NULL,
    user_pass_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(150) NOT NULL,
    first_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    user_nicename VARCHAR(191) NULL,
    phone VARCHAR(30) NULL,
    avatar_media_id BIGINT UNSIGNED NULL,
    locale VARCHAR(20) NOT NULL DEFAULT 'id_ID',
    timezone_name VARCHAR(100) NOT NULL DEFAULT 'Asia/Jakarta',
    status ENUM('pending','active','suspended','blocked','deleted') NOT NULL DEFAULT 'active',
    email_verified_at DATETIME NULL,
    last_login_at DATETIME NULL,
    last_login_ip VARCHAR(45) NULL,
    require_password_reset TINYINT(1) NOT NULL DEFAULT 0,
    password_changed_at DATETIME NULL,
    invited_at DATETIME NULL,
    activated_at DATETIME NULL,
    suspended_at DATETIME NULL,
    status_reason VARCHAR(255) NULL,
    remember_token_hash VARCHAR(255) NULL,
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_users_login (user_login),
    UNIQUE KEY uniq_users_email (user_email),
    KEY idx_users_status (status),
    KEY idx_users_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_user_meta (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(191) NOT NULL,
    meta_value LONGTEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_user_meta (user_id, meta_key),
    KEY idx_user_meta_key (meta_key),
    CONSTRAINT fk_user_meta_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE IF NOT EXISTS gs_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_key VARCHAR(100) NOT NULL,
    role_name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    priority SMALLINT UNSIGNED NOT NULL DEFAULT 100,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_role_key (role_key),
    KEY idx_roles_status (status),
    KEY idx_roles_priority (priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;




CREATE TABLE IF NOT EXISTS gs_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    permission_key VARCHAR(150) NOT NULL,
    permission_name VARCHAR(150) NOT NULL,
    module_key VARCHAR(100) NOT NULL DEFAULT 'core',
    description TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_permission_key (permission_key),
    KEY idx_permissions_module (module_key),
    KEY idx_permissions_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_role_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    grant_type ENUM('allow','deny') NOT NULL DEFAULT 'allow',
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_role_permission (role_id, permission_id),
    KEY idx_role_permissions_permission (permission_id),
    CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES gs_roles(id) ON DELETE CASCADE,
    CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES gs_permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_user_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_user_role (user_id, role_id),
    KEY idx_user_roles_role (role_id),
    KEY idx_user_roles_primary (user_id, is_primary),
    CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES gs_roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_user_invitations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(191) NOT NULL,
    user_login VARCHAR(100) NULL,
    display_name VARCHAR(150) NULL,
    role_id BIGINT UNSIGNED NULL,
    invitation_token_hash VARCHAR(255) NOT NULL,
    expires_at DATETIME NOT NULL,
    accepted_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    status ENUM('pending','sent','accepted','expired','cancelled') NOT NULL DEFAULT 'pending',
    message_text TEXT NULL,
    invited_by BIGINT UNSIGNED NULL,
    accepted_user_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_user_invitations_email_status (email, status),
    KEY idx_user_invitations_expires_at (expires_at),
    KEY idx_user_invitations_role_id (role_id),
    KEY idx_user_invitations_invited_by (invited_by),
    CONSTRAINT fk_user_invitations_role FOREIGN KEY (role_id) REFERENCES gs_roles(id) ON DELETE SET NULL,
    CONSTRAINT fk_user_invitations_invited_by FOREIGN KEY (invited_by) REFERENCES gs_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_user_invitations_accepted_user FOREIGN KEY (accepted_user_id) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_user_password_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    change_type ENUM('create','reset','forced_reset','self_change','admin_change','rehash') NOT NULL DEFAULT 'admin_change',
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_user_password_history_user_changed_at (user_id, changed_at DESC),
    CONSTRAINT fk_user_password_history_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_user_status_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    old_status ENUM('pending','active','suspended','blocked','deleted') NULL,
    new_status ENUM('pending','active','suspended','blocked','deleted') NOT NULL,
    reason VARCHAR(255) NULL,
    context_text LONGTEXT NULL,
    changed_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_user_status_logs_user_created_at (user_id, created_at DESC),
    KEY idx_user_status_logs_new_status_created_at (new_status, created_at DESC),
    CONSTRAINT fk_user_status_logs_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_status_logs_changed_by FOREIGN KEY (changed_by) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS gs_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    session_token_hash VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    last_activity_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    revoke_reason VARCHAR(191) NULL,
    status ENUM('active','expired','revoked') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_session_token_hash (session_token_hash),
    KEY idx_sessions_user (user_id, status),
    KEY idx_sessions_expires (expires_at),
    CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    event_type VARCHAR(100) NOT NULL,
    event_key VARCHAR(150) NOT NULL,
    message TEXT NOT NULL,
    context_text LONGTEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    route_path VARCHAR(255) NULL,
    request_id VARCHAR(100) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_activity_user (user_id),
    KEY idx_activity_event (event_type, event_key),
    KEY idx_activity_created (created_at),
    CONSTRAINT fk_activity_logs_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    table_name VARCHAR(150) NOT NULL,
    record_id BIGINT UNSIGNED NULL,
    action_type ENUM('insert','update','delete','restore','login','logout','execute') NOT NULL,
    before_snapshot_text LONGTEXT NULL,
    after_snapshot_text LONGTEXT NULL,
    diff_text LONGTEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    request_id VARCHAR(100) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_audit_table_record (table_name, record_id),
    KEY idx_audit_user (user_id),
    KEY idx_audit_action_created (action_type, created_at),
    CONSTRAINT fk_audit_logs_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_migrations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    migration_key VARCHAR(191) NOT NULL,
    batch_no INT UNSIGNED NOT NULL DEFAULT 1,
    migration_type ENUM('core','module','seed') NOT NULL DEFAULT 'core',
    file_path VARCHAR(255) NULL,
    checksum_sha256 CHAR(64) NULL,
    status ENUM('pending','applied','failed','rolled_back') NOT NULL DEFAULT 'applied',
    applied_at DATETIME NULL,
    rolled_back_at DATETIME NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_migration_key (migration_key),
    KEY idx_migrations_batch (batch_no),
    KEY idx_migrations_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    component_type ENUM('core','plugin','theme','module') NOT NULL,
    component_key VARCHAR(191) NOT NULL,
    version_installed VARCHAR(50) NOT NULL,
    version_available VARCHAR(50) NULL,
    release_channel VARCHAR(50) NOT NULL DEFAULT 'stable',
    install_state ENUM('installed','pending','failed','removed') NOT NULL DEFAULT 'installed',
    checksum_sha256 CHAR(64) NULL,
    installed_at DATETIME NULL,
    checked_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_version_component (component_type, component_key),
    KEY idx_versions_state (install_state),
    KEY idx_versions_checked (checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_post_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type_key VARCHAR(100) NOT NULL,
    singular_label VARCHAR(100) NOT NULL,
    plural_label VARCHAR(100) NOT NULL,
    description TEXT NULL,
    is_hierarchical TINYINT(1) NOT NULL DEFAULT 0,
    has_archive TINYINT(1) NOT NULL DEFAULT 1,
    supports_title TINYINT(1) NOT NULL DEFAULT 1,
    supports_editor TINYINT(1) NOT NULL DEFAULT 1,
    supports_excerpt TINYINT(1) NOT NULL DEFAULT 1,
    supports_thumbnail TINYINT(1) NOT NULL DEFAULT 1,
    supports_comments TINYINT(1) NOT NULL DEFAULT 1,
    supports_author TINYINT(1) NOT NULL DEFAULT 1,
    route_base VARCHAR(191) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_post_type_key (type_key),
    KEY idx_post_types_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_type_id BIGINT UNSIGNED NOT NULL,
    author_id BIGINT UNSIGNED NULL,
    parent_id BIGINT UNSIGNED NULL,
    featured_media_id BIGINT UNSIGNED NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(191) NOT NULL,
    excerpt TEXT NULL,
    content LONGTEXT NULL,
    content_filtered LONGTEXT NULL,
    status ENUM('draft','pending','published','private','trash','archived','scheduled') NOT NULL DEFAULT 'draft',
    visibility ENUM('public','private','password') NOT NULL DEFAULT 'public',
    comment_status ENUM('open','closed') NOT NULL DEFAULT 'open',
    password_hash VARCHAR(255) NULL,
    template_file VARCHAR(191) NULL,
    menu_order INT NOT NULL DEFAULT 0,
    published_at DATETIME NULL,
    scheduled_at DATETIME NULL,
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_posts_type_parent_slug (post_type_id, parent_id, slug),
    KEY idx_posts_author (author_id),
    KEY idx_posts_status_type_date (status, post_type_id, published_at),
    KEY idx_posts_parent (parent_id),
    KEY idx_posts_featured_media (featured_media_id),
    KEY idx_posts_deleted_at (deleted_at),
    CONSTRAINT fk_posts_post_type FOREIGN KEY (post_type_id) REFERENCES gs_post_types(id),
    CONSTRAINT fk_posts_author FOREIGN KEY (author_id) REFERENCES gs_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_posts_parent FOREIGN KEY (parent_id) REFERENCES gs_posts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_post_meta (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(191) NOT NULL,
    meta_value LONGTEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_post_meta (post_id, meta_key),
    KEY idx_post_meta_key (meta_key),
    CONSTRAINT fk_post_meta_post FOREIGN KEY (post_id) REFERENCES gs_posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_terms (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(191) NOT NULL,
    slug VARCHAR(191) NOT NULL,
    description TEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_terms_slug (slug),
    KEY idx_terms_name (name),
    KEY idx_terms_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_term_taxonomy (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    term_id BIGINT UNSIGNED NOT NULL,
    taxonomy_key VARCHAR(100) NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    description TEXT NULL,
    item_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_term_taxonomy (term_id, taxonomy_key),
    KEY idx_term_taxonomy_taxonomy_parent (taxonomy_key, parent_id),
    CONSTRAINT fk_term_taxonomy_term FOREIGN KEY (term_id) REFERENCES gs_terms(id) ON DELETE CASCADE,
    CONSTRAINT fk_term_taxonomy_parent FOREIGN KEY (parent_id) REFERENCES gs_term_taxonomy(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_term_relationships (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    object_type ENUM('post','media','menu_item') NOT NULL DEFAULT 'post',
    object_id BIGINT UNSIGNED NOT NULL,
    term_taxonomy_id BIGINT UNSIGNED NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_term_relationship (object_type, object_id, term_taxonomy_id),
    KEY idx_term_relationship_taxonomy (term_taxonomy_id),
    KEY idx_term_relationship_object (object_type, object_id),
    CONSTRAINT fk_term_relationship_taxonomy FOREIGN KEY (term_taxonomy_id) REFERENCES gs_term_taxonomy(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    author_name VARCHAR(150) NULL,
    author_email VARCHAR(191) NULL,
    author_url VARCHAR(255) NULL,
    author_ip VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    content LONGTEXT NOT NULL,
    status ENUM('pending','approved','spam','trash') NOT NULL DEFAULT 'pending',
    approved_at DATETIME NULL,
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_comments_post_status (post_id, status),
    KEY idx_comments_parent (parent_id),
    KEY idx_comments_user (user_id),
    KEY idx_comments_deleted_at (deleted_at),
    CONSTRAINT fk_comments_post FOREIGN KEY (post_id) REFERENCES gs_posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_comments_parent FOREIGN KEY (parent_id) REFERENCES gs_comments(id) ON DELETE SET NULL,
    CONSTRAINT fk_comments_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_comment_meta (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    comment_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(191) NOT NULL,
    meta_value LONGTEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_comment_meta (comment_id, meta_key),
    KEY idx_comment_meta_key (meta_key),
    CONSTRAINT fk_comment_meta_comment FOREIGN KEY (comment_id) REFERENCES gs_comments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_media (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uploader_id BIGINT UNSIGNED NULL,
    parent_post_id BIGINT UNSIGNED NULL,
    original_name VARCHAR(255) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(191) NOT NULL,
    extension VARCHAR(20) NULL,
    storage_disk VARCHAR(50) NOT NULL DEFAULT 'local',
    storage_path VARCHAR(255) NOT NULL,
    public_url VARCHAR(255) NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    checksum_sha256 CHAR(64) NULL,
    width INT UNSIGNED NULL,
    height INT UNSIGNED NULL,
    alt_text VARCHAR(255) NULL,
    title VARCHAR(255) NULL,
    caption TEXT NULL,
    description TEXT NULL,
    is_image TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('active','orphaned','deleted') NOT NULL DEFAULT 'active',
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_media_storage_path (storage_path),
    KEY idx_media_uploader (uploader_id),
    KEY idx_media_parent_post (parent_post_id),
    KEY idx_media_mime_type (mime_type),
    KEY idx_media_status_created (status, created_at),
    CONSTRAINT fk_media_uploader FOREIGN KEY (uploader_id) REFERENCES gs_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_media_parent_post FOREIGN KEY (parent_post_id) REFERENCES gs_posts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_media_meta (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    media_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(191) NOT NULL,
    meta_value LONGTEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_media_meta (media_id, meta_key),
    KEY idx_media_meta_key (meta_key),
    CONSTRAINT fk_media_meta_media FOREIGN KEY (media_id) REFERENCES gs_media(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_menus (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menu_key VARCHAR(100) NOT NULL,
    menu_name VARCHAR(150) NOT NULL,
    menu_location VARCHAR(100) NULL,
    description TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_menu_key (menu_key),
    KEY idx_menus_location (menu_location),
    KEY idx_menus_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_menu_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    menu_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    item_type ENUM('custom','post','page','term','route','plugin') NOT NULL DEFAULT 'custom',
    related_entity_type VARCHAR(50) NULL,
    related_entity_id BIGINT UNSIGNED NULL,
    title VARCHAR(191) NOT NULL,
    url VARCHAR(255) NULL,
    icon VARCHAR(100) NULL,
    target VARCHAR(20) NOT NULL DEFAULT '_self',
    css_class VARCHAR(191) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_menu_items_menu_sort (menu_id, sort_order),
    KEY idx_menu_items_parent (parent_id),
    KEY idx_menu_items_related (related_entity_type, related_entity_id),
    CONSTRAINT fk_menu_items_menu FOREIGN KEY (menu_id) REFERENCES gs_menus(id) ON DELETE CASCADE,
    CONSTRAINT fk_menu_items_parent FOREIGN KEY (parent_id) REFERENCES gs_menu_items(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_themes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    theme_scope ENUM('frontend','admin') NOT NULL DEFAULT 'frontend',
    theme_key VARCHAR(100) NOT NULL,
    theme_name VARCHAR(150) NOT NULL,
    version VARCHAR(50) NOT NULL,
    author VARCHAR(150) NULL,
    description TEXT NULL,
    entry_file VARCHAR(255) NOT NULL,
    screenshot_path VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    install_state ENUM('not_installed','installed','error') NOT NULL DEFAULT 'installed',
    last_error TEXT NULL,
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_theme_scope_key (theme_scope, theme_key),
    KEY idx_themes_active (theme_scope, is_active),
    KEY idx_themes_state (install_state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_theme_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    theme_id BIGINT UNSIGNED NOT NULL,
    setting_group VARCHAR(100) NOT NULL DEFAULT 'general',
    setting_key VARCHAR(191) NOT NULL,
    setting_value LONGTEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_theme_setting (theme_id, setting_key),
    KEY idx_theme_settings_group (theme_id, setting_group),
    CONSTRAINT fk_theme_settings_theme FOREIGN KEY (theme_id) REFERENCES gs_themes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_widget_areas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    area_key VARCHAR(100) NOT NULL,
    area_name VARCHAR(150) NOT NULL,
    theme_scope ENUM('frontend','admin') NOT NULL DEFAULT 'frontend',
    description TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_widget_area (theme_scope, area_key),
    KEY idx_widget_areas_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_widgets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    widget_area_id BIGINT UNSIGNED NOT NULL,
    widget_key VARCHAR(100) NOT NULL,
    widget_name VARCHAR(150) NOT NULL,
    widget_class VARCHAR(191) NOT NULL,
    settings_text LONGTEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_widgets_area_sort (widget_area_id, sort_order),
    KEY idx_widgets_key (widget_key),
    CONSTRAINT fk_widgets_area FOREIGN KEY (widget_area_id) REFERENCES gs_widget_areas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_plugins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plugin_key VARCHAR(100) NOT NULL,
    package_id VARCHAR(191) NULL,
    plugin_name VARCHAR(150) NOT NULL,
    version VARCHAR(50) NOT NULL,
    author VARCHAR(150) NULL,
    description TEXT NULL,
    entry_file VARCHAR(255) NOT NULL,
    install_state ENUM('not_installed','installed','migrated','error') NOT NULL DEFAULT 'installed',
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    is_core TINYINT(1) NOT NULL DEFAULT 0,
    last_error TEXT NULL,
    installed_at DATETIME NULL,
    activated_at DATETIME NULL,
    deactivated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_plugin_key (plugin_key),
    UNIQUE KEY uniq_plugin_package (package_id),
    KEY idx_plugins_active (is_active, install_state),
    KEY idx_plugins_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_plugin_meta (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plugin_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(191) NOT NULL,
    meta_value LONGTEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_plugin_meta (plugin_id, meta_key),
    KEY idx_plugin_meta_key (meta_key),
    CONSTRAINT fk_plugin_meta_plugin FOREIGN KEY (plugin_id) REFERENCES gs_plugins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_plugin_hooks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plugin_id BIGINT UNSIGNED NOT NULL,
    hook_type ENUM('action','filter') NOT NULL DEFAULT 'action',
    hook_name VARCHAR(191) NOT NULL,
    callback_class VARCHAR(191) NULL,
    callback_method VARCHAR(191) NULL,
    callback_function VARCHAR(191) NULL,
    priority SMALLINT NOT NULL DEFAULT 10,
    accepted_args SMALLINT NOT NULL DEFAULT 1,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_plugin_hooks_plugin (plugin_id),
    KEY idx_plugin_hooks_name (hook_type, hook_name, status),
    CONSTRAINT fk_plugin_hooks_plugin FOREIGN KEY (plugin_id) REFERENCES gs_plugins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_plugin_migrations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plugin_id BIGINT UNSIGNED NOT NULL,
    migration_key VARCHAR(191) NOT NULL,
    batch_no INT UNSIGNED NOT NULL DEFAULT 1,
    checksum_sha256 CHAR(64) NULL,
    status ENUM('pending','applied','failed','rolled_back') NOT NULL DEFAULT 'applied',
    applied_at DATETIME NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_plugin_migration (plugin_id, migration_key),
    KEY idx_plugin_migrations_batch (plugin_id, batch_no),
    CONSTRAINT fk_plugin_migrations_plugin FOREIGN KEY (plugin_id) REFERENCES gs_plugins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_routes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    route_name VARCHAR(191) NOT NULL,
    request_method VARCHAR(10) NOT NULL DEFAULT 'GET',
    route_path VARCHAR(255) NOT NULL,
    controller_class VARCHAR(191) NOT NULL,
    controller_method VARCHAR(191) NOT NULL,
    middleware_stack_text TEXT NULL,
    source_type ENUM('core','plugin','theme') NOT NULL DEFAULT 'core',
    source_key VARCHAR(100) NOT NULL DEFAULT 'core',
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_route_method_path (request_method, route_path),
    UNIQUE KEY uniq_route_name (route_name),
    KEY idx_routes_source (source_type, source_key),
    KEY idx_routes_status_sort (status, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_redirects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_host VARCHAR(191) NULL,
    source_path VARCHAR(255) NOT NULL,
    match_type ENUM('exact','prefix','regex') NOT NULL DEFAULT 'exact',
    target_type ENUM('internal','external') NOT NULL DEFAULT 'internal',
    target_path VARCHAR(255) NOT NULL,
    http_code SMALLINT UNSIGNED NOT NULL DEFAULT 301,
    hit_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    last_hit_at DATETIME NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_redirect_source (source_host, source_path, match_type),
    KEY idx_redirects_status_code (status, http_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_email_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_key VARCHAR(100) NOT NULL,
    template_name VARCHAR(150) NOT NULL,
    subject_line VARCHAR(255) NOT NULL,
    body_html LONGTEXT NULL,
    body_text LONGTEXT NULL,
    from_name VARCHAR(150) NULL,
    from_email VARCHAR(191) NULL,
    reply_to_email VARCHAR(191) NULL,
    is_system TINYINT(1) NOT NULL DEFAULT 1,
    status ENUM('draft','active','inactive') NOT NULL DEFAULT 'active',
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_email_template_key (template_key),
    KEY idx_email_templates_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    channel ENUM('panel','email','sms','webhook') NOT NULL DEFAULT 'panel',
    notification_type VARCHAR(100) NOT NULL,
    title VARCHAR(191) NOT NULL,
    message TEXT NOT NULL,
    action_url VARCHAR(255) NULL,
    status ENUM('unread','read','archived','failed') NOT NULL DEFAULT 'unread',
    delivered_at DATETIME NULL,
    read_at DATETIME NULL,
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_notifications_user_status (user_id, status),
    KEY idx_notifications_type (notification_type),
    KEY idx_notifications_created (created_at),
    CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_mail_queue (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    recipient_email VARCHAR(191) NOT NULL,
    recipient_name VARCHAR(150) NULL,
    subject_line VARCHAR(255) NOT NULL,
    body_html LONGTEXT NULL,
    body_text LONGTEXT NULL,
    from_name VARCHAR(150) NULL,
    from_email VARCHAR(191) NULL,
    reply_to_email VARCHAR(191) NULL,
    attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 3,
    scheduled_at DATETIME NULL,
    sent_at DATETIME NULL,
    failed_at DATETIME NULL,
    last_error TEXT NULL,
    status ENUM('queued','processing','sent','failed','cancelled') NOT NULL DEFAULT 'queued',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_mail_queue_status_sched (status, scheduled_at),
    KEY idx_mail_queue_template (template_id),
    KEY idx_mail_queue_user (user_id),
    CONSTRAINT fk_mail_queue_template FOREIGN KEY (template_id) REFERENCES gs_email_templates(id) ON DELETE SET NULL,
    CONSTRAINT fk_mail_queue_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue_name VARCHAR(100) NOT NULL DEFAULT 'default',
    job_key VARCHAR(191) NOT NULL,
    handler_class VARCHAR(191) NOT NULL,
    handler_method VARCHAR(191) NOT NULL,
    payload_text LONGTEXT NULL,
    priority SMALLINT NOT NULL DEFAULT 100,
    attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 3,
    available_at DATETIME NULL,
    reserved_at DATETIME NULL,
    run_after_at DATETIME NULL,
    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    failed_at DATETIME NULL,
    status ENUM('queued','reserved','running','completed','failed','cancelled') NOT NULL DEFAULT 'queued',
    last_error TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_jobs_key (job_key),
    KEY idx_jobs_queue_status (queue_name, status, priority),
    KEY idx_jobs_run_after (run_after_at),
    KEY idx_jobs_available (available_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_job_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_id BIGINT UNSIGNED NOT NULL,
    log_level ENUM('debug','info','warning','error') NOT NULL DEFAULT 'info',
    message TEXT NOT NULL,
    context_text LONGTEXT NULL,
    logged_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_job_logs_job_logged (job_id, logged_at),
    CONSTRAINT fk_job_logs_job FOREIGN KEY (job_id) REFERENCES gs_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_cron_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    event_key VARCHAR(191) NOT NULL,
    event_name VARCHAR(191) NOT NULL,
    schedule_type ENUM('cron','interval','once') NOT NULL DEFAULT 'interval',
    cron_expression VARCHAR(100) NULL,
    interval_seconds INT UNSIGNED NULL,
    handler_class VARCHAR(191) NOT NULL,
    handler_method VARCHAR(191) NOT NULL,
    payload_text LONGTEXT NULL,
    next_run_at DATETIME NULL,
    last_run_at DATETIME NULL,
    last_success_at DATETIME NULL,
    last_error TEXT NULL,
    status ENUM('scheduled','running','completed','failed','disabled') NOT NULL DEFAULT 'scheduled',
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_cron_event_key (event_key),
    KEY idx_cron_next_run (status, next_run_at),
    KEY idx_cron_schedule_type (schedule_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_login_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    login_identifier VARCHAR(191) NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    is_success TINYINT(1) NOT NULL DEFAULT 0,
    failure_reason VARCHAR(191) NULL,
    locked_until DATETIME NULL,
    attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    request_id VARCHAR(100) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_login_attempts_identifier (login_identifier),
    KEY idx_login_attempts_ip_time (ip_address, attempted_at),
    KEY idx_login_attempts_user_time (user_id, attempted_at),
    CONSTRAINT fk_login_attempts_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_rate_limits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scope_key VARCHAR(100) NOT NULL,
    identifier_key VARCHAR(191) NOT NULL,
    route_key VARCHAR(191) NOT NULL DEFAULT '*',
    hit_count INT UNSIGNED NOT NULL DEFAULT 0,
    max_hits INT UNSIGNED NOT NULL DEFAULT 60,
    window_started_at DATETIME NOT NULL,
    window_ends_at DATETIME NOT NULL,
    blocked_until DATETIME NULL,
    status ENUM('active','blocked','expired') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_rate_limit_window (scope_key, identifier_key, route_key, window_started_at),
    KEY idx_rate_limits_blocked (status, blocked_until),
    KEY idx_rate_limits_window_ends (window_ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_security_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    event_type VARCHAR(100) NOT NULL,
    severity ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
    source_ip VARCHAR(45) NULL,
    route_path VARCHAR(255) NULL,
    message TEXT NOT NULL,
    payload_excerpt LONGTEXT NULL,
    response_code SMALLINT UNSIGNED NULL,
    blocked_until DATETIME NULL,
    request_id VARCHAR(100) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_security_events_type_created (event_type, created_at),
    KEY idx_security_events_severity (severity, created_at),
    KEY idx_security_events_ip_created (source_ip, created_at),
    CONSTRAINT fk_security_events_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Deferred foreign keys are added by the PHP migrator to keep reruns idempotent.

INSERT INTO gs_roles (role_key, role_name, description, priority, is_system, status)
VALUES
('super_admin','Super Admin','Akses penuh seluruh sistem.',1,1,'active'),
('admin','Admin','Mengelola operasional admin dan pengaturan umum.',10,1,'active'),
('editor','Editor','Mengelola konten dan moderasi komentar.',30,1,'active'),
('author','Author','Menulis dan mengelola konten miliknya sendiri.',50,1,'active'),
('contributor','Contributor','Mengirim draft tanpa publish langsung.',70,1,'active')
ON DUPLICATE KEY UPDATE role_name = VALUES(role_name);

INSERT INTO gs_post_types (
    type_key, singular_label, plural_label, description,
    is_hierarchical, has_archive, supports_title, supports_editor,
    supports_excerpt, supports_thumbnail, supports_comments, supports_author,
    route_base, status
) VALUES
('post','Post','Posts','Konten artikel standar.',0,1,1,1,1,1,1,1,'blog','active'),
('page','Page','Pages','Konten halaman statis.',1,0,1,1,1,1,0,1,'page','active')
ON DUPLICATE KEY UPDATE singular_label = VALUES(singular_label);

INSERT INTO gs_email_templates (template_key, template_name, subject_line, body_html, body_text, is_system, status)
VALUES
('welcome_user','Welcome User','Selamat datang di {{site_name}}','<p>Halo {{display_name}}, selamat datang di {{site_name}}.</p>','Halo {{display_name}}, selamat datang di {{site_name}}.',1,'active'),
('reset_password','Reset Password','Reset Password {{site_name}}','<p>Klik tautan reset password Anda.</p>','Klik tautan reset password Anda.',1,'active')
ON DUPLICATE KEY UPDATE template_name = VALUES(template_name);

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE IF NOT EXISTS gs_plugin_uploads (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    upload_token CHAR(32) NOT NULL,
    original_file_name VARCHAR(255) NOT NULL,
    stored_file_name VARCHAR(255) NOT NULL,
    storage_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(191) NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    checksum_sha256 CHAR(64) NULL,
    uploader_id BIGINT UNSIGNED NULL,
    status ENUM('uploaded','quarantined','rejected','installed') NOT NULL DEFAULT 'uploaded',
    rejection_reason TEXT NULL,
    installed_plugin_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_plugin_upload_token (upload_token),
    KEY idx_plugin_upload_status (status, created_at),
    KEY idx_plugin_upload_uploader (uploader_id),
    CONSTRAINT fk_plugin_upload_uploader FOREIGN KEY (uploader_id) REFERENCES gs_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_plugin_upload_plugin FOREIGN KEY (installed_plugin_id) REFERENCES gs_plugins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_plugin_packages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    upload_id BIGINT UNSIGNED NOT NULL,
    plugin_slug VARCHAR(100) NOT NULL,
    plugin_name VARCHAR(150) NOT NULL,
    version VARCHAR(50) NOT NULL,
    author VARCHAR(150) NULL,
    publisher VARCHAR(150) NULL,
    package_id VARCHAR(191) NULL,
    signature_key_id VARCHAR(191) NULL,
    signature TEXT NULL,
    requires_core VARCHAR(50) NULL,
    root_directory VARCHAR(191) NOT NULL,
    entry_file VARCHAR(255) NOT NULL,
    file_count INT UNSIGNED NOT NULL DEFAULT 0,
    total_uncompressed_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    nested_depth INT UNSIGNED NOT NULL DEFAULT 0,
    validation_status ENUM('pending','valid','invalid','quarantined','rejected','installed') NOT NULL DEFAULT 'pending',
    validation_notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_plugin_packages_upload (upload_id),
    KEY idx_plugin_packages_slug (plugin_slug),
    KEY idx_plugin_packages_status (validation_status),
    CONSTRAINT fk_plugin_packages_upload FOREIGN KEY (upload_id) REFERENCES gs_plugin_uploads(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_plugin_quarantine (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    upload_id BIGINT UNSIGNED NOT NULL,
    package_id BIGINT UNSIGNED NULL,
    quarantine_token CHAR(32) NOT NULL,
    quarantine_path VARCHAR(255) NOT NULL,
    status ENUM('quarantined','released','rejected','cleaned') NOT NULL DEFAULT 'quarantined',
    reason TEXT NULL,
    released_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_plugin_quarantine_upload (upload_id),
    KEY idx_plugin_quarantine_status (status, created_at),
    CONSTRAINT fk_plugin_quarantine_upload FOREIGN KEY (upload_id) REFERENCES gs_plugin_uploads(id) ON DELETE CASCADE,
    CONSTRAINT fk_plugin_quarantine_package FOREIGN KEY (package_id) REFERENCES gs_plugin_packages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_plugin_files (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    upload_id BIGINT UNSIGNED NOT NULL,
    package_id BIGINT UNSIGNED NULL,
    relative_path VARCHAR(255) NOT NULL,
    file_type VARCHAR(50) NOT NULL DEFAULT 'unknown',
    mime_type VARCHAR(191) NULL,
    extension VARCHAR(20) NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    checksum_sha256 CHAR(64) NULL,
    is_suspicious TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('scanned','rejected','installed') NOT NULL DEFAULT 'scanned',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_plugin_files_upload (upload_id),
    KEY idx_plugin_files_package (package_id),
    KEY idx_plugin_files_path (relative_path),
    CONSTRAINT fk_plugin_files_upload FOREIGN KEY (upload_id) REFERENCES gs_plugin_uploads(id) ON DELETE CASCADE,
    CONSTRAINT fk_plugin_files_package FOREIGN KEY (package_id) REFERENCES gs_plugin_packages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_plugin_install_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    upload_id BIGINT UNSIGNED NULL,
    plugin_id BIGINT UNSIGNED NULL,
    action_type ENUM('upload','validate','quarantine','install','activate','deactivate','delete','reject') NOT NULL DEFAULT 'upload',
    status ENUM('success','failed','info') NOT NULL DEFAULT 'info',
    message TEXT NOT NULL,
    context_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_plugin_install_logs_upload (upload_id),
    KEY idx_plugin_install_logs_plugin (plugin_id),
    KEY idx_plugin_install_logs_action (action_type, status),
    CONSTRAINT fk_plugin_install_logs_upload FOREIGN KEY (upload_id) REFERENCES gs_plugin_uploads(id) ON DELETE SET NULL,
    CONSTRAINT fk_plugin_install_logs_plugin FOREIGN KEY (plugin_id) REFERENCES gs_plugins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS gs_plugin_backups (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plugin_id BIGINT UNSIGNED NULL,
    upload_id BIGINT UNSIGNED NULL,
    plugin_key VARCHAR(100) NOT NULL,
    source_version VARCHAR(50) NULL,
    target_version VARCHAR(50) NULL,
    backup_token VARCHAR(64) NOT NULL,
    backup_path VARCHAR(255) NOT NULL,
    file_count INT UNSIGNED NOT NULL DEFAULT 0,
    total_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
    checksum_sha256 CHAR(64) NULL,
    snapshot_json LONGTEXT NULL,
    status ENUM('available','restored','expired','failed') NOT NULL DEFAULT 'available',
    notes TEXT NULL,
    restored_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_plugin_backup_token (backup_token),
    KEY idx_plugin_backups_plugin (plugin_id, status, created_at),
    KEY idx_plugin_backups_upload (upload_id),
    KEY idx_plugin_backups_key (plugin_key),
    CONSTRAINT fk_plugin_backups_plugin FOREIGN KEY (plugin_id) REFERENCES gs_plugins(id) ON DELETE SET NULL,
    CONSTRAINT fk_plugin_backups_upload FOREIGN KEY (upload_id) REFERENCES gs_plugin_uploads(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_plugin_rollbacks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plugin_id BIGINT UNSIGNED NULL,
    backup_id BIGINT UNSIGNED NOT NULL,
    upload_id BIGINT UNSIGNED NULL,
    action_type ENUM('auto','manual') NOT NULL DEFAULT 'manual',
    from_version VARCHAR(50) NULL,
    to_version VARCHAR(50) NULL,
    status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
    reason TEXT NULL,
    details_json LONGTEXT NULL,
    triggered_by BIGINT UNSIGNED NULL,
    rolled_back_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_plugin_rollbacks_plugin (plugin_id, status, created_at),
    KEY idx_plugin_rollbacks_backup (backup_id),
    KEY idx_plugin_rollbacks_upload (upload_id),
    CONSTRAINT fk_plugin_rollbacks_plugin FOREIGN KEY (plugin_id) REFERENCES gs_plugins(id) ON DELETE SET NULL,
    CONSTRAINT fk_plugin_rollbacks_backup FOREIGN KEY (backup_id) REFERENCES gs_plugin_backups(id) ON DELETE CASCADE,
    CONSTRAINT fk_plugin_rollbacks_upload FOREIGN KEY (upload_id) REFERENCES gs_plugin_uploads(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_plugin_dependencies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    upload_id BIGINT UNSIGNED NULL,
    plugin_id BIGINT UNSIGNED NULL,
    dependency_type ENUM('core','php','db','plugin','channel') NOT NULL DEFAULT 'plugin',
    dependency_key VARCHAR(191) NOT NULL,
    constraint_expression VARCHAR(100) NULL,
    installed_version VARCHAR(100) NULL,
    required_version VARCHAR(100) NULL,
    status ENUM('compatible','warning','blocked') NOT NULL DEFAULT 'compatible',
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_plugin_dependencies_upload (upload_id),
    KEY idx_plugin_dependencies_plugin (plugin_id),
    KEY idx_plugin_dependencies_status (status, dependency_type),
    CONSTRAINT fk_plugin_dependencies_upload FOREIGN KEY (upload_id) REFERENCES gs_plugin_uploads(id) ON DELETE CASCADE,
    CONSTRAINT fk_plugin_dependencies_plugin FOREIGN KEY (plugin_id) REFERENCES gs_plugins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS gs_system_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    log_channel VARCHAR(100) NOT NULL,
    log_level ENUM('debug','info','warning','error','critical') NOT NULL DEFAULT 'info',
    message TEXT NOT NULL,
    context_text LONGTEXT NULL,
    route_path VARCHAR(255) NULL,
    request_id VARCHAR(100) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_system_logs_channel_level (log_channel, log_level),
    KEY idx_system_logs_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gs_api_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_name VARCHAR(150) NOT NULL,
    token_prefix VARCHAR(20) NULL,
    token_hash CHAR(64) NOT NULL,
    allowed_scopes_text LONGTEXT NULL,
    last_used_at DATETIME NULL,
    last_used_ip VARCHAR(45) NULL,
    expires_at DATETIME NULL,
    revoked_at DATETIME NULL,
    status ENUM('active','expired','revoked') NOT NULL DEFAULT 'active',
    deleted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_api_token_hash (token_hash),
    KEY idx_api_tokens_user_status (user_id, status),
    KEY idx_api_tokens_expires (expires_at),
    CONSTRAINT fk_api_tokens_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Phase 6 Security Enterprise Hardening
-- Policy versioning keys yang dipakai runtime: network.admin_access, device.risk, csp.frontend, csp.admin, secret.rotation
-- Aturan: setiap perubahan policy harus insert versi baru, bukan overwrite config lama.
CREATE TABLE IF NOT EXISTS gs_security_incidents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    incident_key VARCHAR(100) NOT NULL,
    title VARCHAR(191) NOT NULL,
    severity ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
    status ENUM('open','investigating','contained','resolved','false_positive','closed') NOT NULL DEFAULT 'open',
    source_type ENUM('security_event','login_risk','network_policy','csp','manual') NOT NULL DEFAULT 'manual',
    source_ref_id BIGINT UNSIGNED NULL,
    assigned_user_id BIGINT UNSIGNED NULL,
    detected_at DATETIME NOT NULL,
    acknowledged_at DATETIME NULL,
    resolved_at DATETIME NULL,
    last_activity_at DATETIME NULL,
    summary_text LONGTEXT NULL,
    evidence_text LONGTEXT NULL,
    containment_text LONGTEXT NULL,
    resolution_text LONGTEXT NULL,
    tags_text LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_security_incident_key (incident_key),
    KEY idx_security_incidents_status_severity_detected (status, severity, detected_at),
    KEY idx_security_incidents_source (source_type, source_ref_id),
    KEY idx_security_incidents_assigned (assigned_user_id, status),
    CONSTRAINT fk_security_incidents_assigned_user FOREIGN KEY (assigned_user_id) REFERENCES gs_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_security_incidents_created_by FOREIGN KEY (created_by) REFERENCES gs_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_security_incidents_updated_by FOREIGN KEY (updated_by) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS gs_security_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    policy_key VARCHAR(191) NOT NULL,
    policy_scope ENUM('security','network','device','csp','secret') NOT NULL DEFAULT 'security',
    version_no INT NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    config_text LONGTEXT NULL,
    notes_text TEXT NULL,
    effective_from DATETIME NOT NULL,
    superseded_at DATETIME NULL,
    approved_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_security_policy_version (policy_key, version_no),
    KEY idx_security_policies_scope_active (policy_scope, is_active),
    KEY idx_security_policies_key_active (policy_key, is_active),
    CONSTRAINT fk_security_policies_approved_by FOREIGN KEY (approved_by) REFERENCES gs_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_security_policies_created_by FOREIGN KEY (created_by) REFERENCES gs_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_security_policies_updated_by FOREIGN KEY (updated_by) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS gs_device_fingerprints (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    fingerprint_hash CHAR(64) NOT NULL,
    device_label VARCHAR(191) NULL,
    first_seen_at DATETIME NOT NULL,
    last_seen_at DATETIME NULL,
    first_ip_address VARCHAR(45) NULL,
    last_ip_address VARCHAR(45) NULL,
    first_user_agent VARCHAR(255) NULL,
    last_user_agent VARCHAR(255) NULL,
    trust_state ENUM('new','trusted','challenged','blocked') NOT NULL DEFAULT 'new',
    risk_score_cached INT NOT NULL DEFAULT 0,
    session_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_session_id BIGINT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    UNIQUE KEY uniq_device_fingerprint_user (user_id, fingerprint_hash),
    KEY idx_device_fingerprints_trust (user_id, trust_state),
    KEY idx_device_fingerprints_last_seen (last_seen_at),
    CONSTRAINT fk_device_fingerprints_user FOREIGN KEY (user_id) REFERENCES gs_users(id) ON DELETE CASCADE,
    CONSTRAINT fk_device_fingerprints_created_by FOREIGN KEY (created_by) REFERENCES gs_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_device_fingerprints_updated_by FOREIGN KEY (updated_by) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS gs_secret_rotations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    secret_key VARCHAR(191) NOT NULL,
    purpose_scope ENUM('csrf','session','remember_me','api','webhook','internal') NOT NULL DEFAULT 'internal',
    storage_driver ENUM('config','options','env','external') NOT NULL DEFAULT 'config',
    version_no INT NOT NULL,
    old_fingerprint_sha256 CHAR(64) NULL,
    new_fingerprint_sha256 CHAR(64) NOT NULL,
    rotation_reason VARCHAR(191) NULL,
    grace_until DATETIME NULL,
    status ENUM('scheduled','active','retired','rolled_back') NOT NULL DEFAULT 'active',
    notes_text TEXT NULL,
    rotated_at DATETIME NOT NULL,
    rotated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    KEY idx_secret_rotations_secret_key_version (secret_key, version_no),
    KEY idx_secret_rotations_status_grace (status, grace_until),
    CONSTRAINT fk_secret_rotations_rotated_by FOREIGN KEY (rotated_by) REFERENCES gs_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_secret_rotations_created_by FOREIGN KEY (created_by) REFERENCES gs_users(id) ON DELETE SET NULL,
    CONSTRAINT fk_secret_rotations_updated_by FOREIGN KEY (updated_by) REFERENCES gs_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;