CREATE TABLE IF NOT EXISTS commands (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    admin_phone VARCHAR(40) NOT NULL,
    raw_text TEXT NOT NULL,
    status VARCHAR(64) NOT NULL,
    target_page_alias VARCHAR(100) NULL,
    details_json LONGTEXT NULL,
    created_at VARCHAR(64) NOT NULL,
    updated_at VARCHAR(64) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_commands_status_created_at (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rules (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    command_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    target_page_alias VARCHAR(100) NULL,
    channel VARCHAR(32) NOT NULL,
    action VARCHAR(64) NOT NULL,
    trigger_keywords LONGTEXT NULL,
    response_text TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at VARCHAR(64) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_rules_active_action (is_active, action),
    KEY idx_rules_target_page (target_page_alias, is_active),
    CONSTRAINT fk_rules_command_id FOREIGN KEY (command_id) REFERENCES commands(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pending_questions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    admin_phone VARCHAR(40) NOT NULL,
    command_id BIGINT UNSIGNED NOT NULL,
    question_text TEXT NOT NULL,
    missing_keys LONGTEXT NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'open',
    created_at VARCHAR(64) NOT NULL,
    answered_at VARCHAR(64) NULL,
    PRIMARY KEY (id),
    KEY idx_pending_questions_admin_status (admin_phone, status, created_at),
    CONSTRAINT fk_pending_questions_command_id FOREIGN KEY (command_id) REFERENCES commands(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS processed_actions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    rule_id BIGINT UNSIGNED NOT NULL,
    target_id VARCHAR(255) NOT NULL,
    action_type VARCHAR(64) NOT NULL,
    created_at VARCHAR(64) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_processed_actions (rule_id, target_id, action_type),
    CONSTRAINT fk_processed_actions_rule_id FOREIGN KEY (rule_id) REFERENCES rules(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS runtime_state (
    state_key VARCHAR(191) NOT NULL,
    state_value LONGTEXT NOT NULL,
    updated_at VARCHAR(64) NOT NULL,
    PRIMARY KEY (state_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    level VARCHAR(32) NOT NULL,
    message TEXT NOT NULL,
    context_json LONGTEXT NULL,
    created_at VARCHAR(64) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_profiles (
    user_id VARCHAR(191) NOT NULL,
    user_name VARCHAR(255) NULL,
    first_seen_at VARCHAR(64) NOT NULL,
    last_seen_at VARCHAR(64) NOT NULL,
    last_channel VARCHAR(32) NULL,
    total_incoming_comments INT NOT NULL DEFAULT 0,
    total_incoming_messages INT NOT NULL DEFAULT 0,
    total_outgoing_replies INT NOT NULL DEFAULT 0,
    last_incoming_text TEXT NULL,
    last_outgoing_text TEXT NULL,
    metadata_json LONGTEXT NULL,
    PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS facebook_pages (
    alias VARCHAR(100) NOT NULL,
    label VARCHAR(255) NULL,
    page_id VARCHAR(100) NOT NULL,
    access_token LONGTEXT NOT NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at VARCHAR(64) NOT NULL,
    updated_at VARCHAR(64) NOT NULL,
    PRIMARY KEY (alias),
    KEY idx_facebook_pages_default (is_default, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_page_context (
    admin_phone VARCHAR(40) NOT NULL,
    page_alias VARCHAR(100) NOT NULL,
    updated_at VARCHAR(64) NOT NULL,
    PRIMARY KEY (admin_phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS interaction_history (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id VARCHAR(191) NOT NULL,
    user_name VARCHAR(255) NULL,
    channel VARCHAR(32) NOT NULL,
    direction VARCHAR(32) NOT NULL,
    text_body TEXT NOT NULL,
    source_item_id VARCHAR(255) NOT NULL,
    created_at VARCHAR(64) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_interaction_source (direction, channel, source_item_id),
    KEY idx_interaction_history_user_created (user_id, created_at),
    KEY idx_interaction_history_source (source_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
