CREATE TABLE IF NOT EXISTS wa_chatbot_settings (
    id TINYINT UNSIGNED NOT NULL PRIMARY KEY,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    ai_enabled TINYINT(1) NOT NULL DEFAULT 0,
    bot_name VARCHAR(100) NOT NULL DEFAULT 'Support Assistant',
    welcome_message TEXT NOT NULL,
    fallback_message TEXT NOT NULL,
    handoff_message TEXT NOT NULL,
    handoff_keywords VARCHAR(500) NOT NULL,
    business_knowledge MEDIUMTEXT NULL,
    system_prompt TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO wa_chatbot_settings
    (id, enabled, ai_enabled, bot_name, welcome_message, fallback_message, handoff_message, handoff_keywords, business_knowledge, system_prompt)
VALUES
    (1, 1, 0, 'Support Assistant',
     'Hello! 👋 I am the automated support assistant. Ask a question, or type AGENT to speak with our team.',
     'I am sorry, I could not answer that. Type AGENT and a team member will assist you.',
     'I have handed this conversation to our support team. Someone will reply as soon as possible.',
     'agent,human,representative,talk to someone,speak to someone', '',
     'Answer as a concise, helpful customer-support assistant. Use only the supplied business knowledge. Never invent prices, policies, availability, account details, or promises. If the answer is not supported by the supplied knowledge, begin the response with [[HANDOFF]].');

CREATE TABLE IF NOT EXISTS wa_chatbot_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    triggers VARCHAR(1000) NOT NULL,
    match_type ENUM('EXACT','CONTAINS') NOT NULL DEFAULT 'EXACT',
    response TEXT NOT NULL,
    handoff TINYINT(1) NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    priority INT NOT NULL DEFAULT 100,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_chatbot_rule_order (active, priority, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO wa_chatbot_rules (name, triggers, match_type, response, priority)
SELECT 'Welcome menu', 'hi,hello,hey,start,menu', 'EXACT', welcome_message, 10
FROM wa_chatbot_settings
WHERE id=1 AND NOT EXISTS (SELECT 1 FROM wa_chatbot_rules WHERE name='Welcome menu');

CREATE TABLE IF NOT EXISTS wa_chatbot_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    inbound_message_id BIGINT UNSIGNED NOT NULL,
    rule_id BIGINT UNSIGNED NULL,
    source ENUM('RULE','AI','FALLBACK','HANDOFF') NULL,
    status ENUM('PROCESSING','SENT','FAILED','SKIPPED','HANDOFF') NOT NULL DEFAULT 'PROCESSING',
    response_text TEXT NULL,
    provider_response_id VARCHAR(255) NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    UNIQUE KEY uq_chatbot_inbound_message (inbound_message_id),
    KEY idx_chatbot_run_conversation (conversation_id, id),
    CONSTRAINT fk_chatbot_run_conversation FOREIGN KEY (conversation_id) REFERENCES wa_conversations(id) ON DELETE CASCADE,
    CONSTRAINT fk_chatbot_run_message FOREIGN KEY (inbound_message_id) REFERENCES wa_messages(id) ON DELETE CASCADE,
    CONSTRAINT fk_chatbot_run_rule FOREIGN KEY (rule_id) REFERENCES wa_chatbot_rules(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;