-- Migration 0010: create_ai_conversations
-- Auto-generated from database/schema.sql by database/build_migrations.php
-- Do not hand-edit this file — edit schema.sql and re-run that script instead.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 9 — AI ASSISTANT, CHATBOT & VOICE AGENT (data foundation only)
-- =====================================================================

CREATE TABLE IF NOT EXISTS chatbot_settings (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id BIGINT UNSIGNED NOT NULL,
    bot_name         VARCHAR(80)     NOT NULL DEFAULT 'A&B Assistant',
    greeting_message VARCHAR(500)    NULL,
    is_live          TINYINT(1)      NOT NULL DEFAULT 0,
    handoff_keywords VARCHAR(255)    NULL,
    updated_by       BIGINT UNSIGNED NULL,
    updated_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_chatbot_settings_business_line (business_line_id),
    CONSTRAINT fk_chatbot_settings_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_chatbot_settings_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Unifies chatbot conversations and voice calls under one "channel"
-- column so Conversation Logs can list both from a single table.
CREATE TABLE IF NOT EXISTS conversations (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NOT NULL,
    channel           ENUM('chatbot','voice_agent') NOT NULL,
    visitor_identifier VARCHAR(191)   NULL COMMENT 'Phone number, session id, or anonymous cookie id',
    related_lead_id   BIGINT UNSIGNED NULL,
    related_customer_id BIGINT UNSIGNED NULL,
    outcome           ENUM('lead_created','booked','escalated','resolved','abandoned') NULL,
    handled_by_user_id BIGINT UNSIGNED NULL COMMENT 'Set if escalated to a human',
    duration_seconds  INT UNSIGNED    NULL,
    recording_url     VARCHAR(500)    NULL COMMENT 'Voice only',
    started_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at          DATETIME        NULL,
    KEY idx_conversations_business_line (business_line_id),
    KEY idx_conversations_channel (channel),
    KEY idx_conversations_lead (related_lead_id),
    KEY idx_conversations_customer (related_customer_id),
    CONSTRAINT fk_conversations_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_conversations_lead FOREIGN KEY (related_lead_id) REFERENCES leads(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_conversations_customer FOREIGN KEY (related_customer_id) REFERENCES customers(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_conversations_handled_by FOREIGN KEY (handled_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conversation_messages (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    sender          ENUM('visitor','bot','agent') NOT NULL,
    message         MEDIUMTEXT      NOT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_conversation_messages_conversation (conversation_id, created_at),
    CONSTRAINT fk_conversation_messages_conversation FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SET FOREIGN_KEY_CHECKS = 1;
