-- Migration 0016: create_ai_modules_and_integrations
-- 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 16 — AI MODULES & INTEGRATIONS (Phase 5)
-- Additive only. `conversations` (Phase 1) already unified chatbot and
-- voice under one table with the right shape (visitor_identifier,
-- outcome, recording_url) — it just gains an 'ai_assistant' channel
-- and a few voice-specific columns. `integrations` (Phase 1) already
-- has slug + encrypted-credentials-blob storage generic enough for
-- every provider in this phase; nothing about it needed to change,
-- only more rows need seeding (see MarketingSeeder's sibling for
-- Phase 5, IntegrationCatalogSeeder).
-- =====================================================================

ALTER TABLE conversations
    MODIFY COLUMN channel ENUM('chatbot','voice_agent','ai_assistant') NOT NULL,
    ADD COLUMN direction ENUM('inbound','outbound') NULL COMMENT 'Voice only' AFTER channel,
    ADD COLUMN external_ref VARCHAR(100) NULL COMMENT 'Twilio Call SID, WhatsApp message id, etc — for correlating with provider dashboards/webhooks' AFTER visitor_identifier,
    ADD COLUMN transcript_text MEDIUMTEXT NULL COMMENT 'Full flattened transcript, separate from the per-turn conversation_messages rows — convenient for voice calls and AI summarization' AFTER recording_url,
    ADD KEY idx_conversations_external_ref (external_ref);

-- Two-way Google Calendar sync bookkeeping — nullable, so appointments
-- created before Google Calendar was connected are unaffected.
ALTER TABLE appointments
    ADD COLUMN google_calendar_event_id VARCHAR(255) NULL AFTER reminder_sent_at;

-- Admin-editable system prompts, one per AI feature (and optionally
-- per business line, since Homecare and Cleaner want a different
-- voice). AiAssistantService/ChatbotService/VoiceAgentService all read
-- their active prompt from here instead of a hardcoded string.
CREATE TABLE IF NOT EXISTS ai_prompts (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    feature           ENUM('ai_assistant','chatbot','voice_agent') NOT NULL,
    business_line_id  BIGINT UNSIGNED NULL COMMENT 'NULL = applies to both brands',
    name              VARCHAR(150)    NOT NULL,
    system_prompt     TEXT            NOT NULL,
    is_active         TINYINT(1)      NOT NULL DEFAULT 1,
    created_by        BIGINT UNSIGNED NULL,
    updated_by        BIGINT UNSIGNED NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_ai_prompts_feature_active (feature, is_active),
    CONSTRAINT fk_ai_prompts_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_ai_prompts_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_ai_prompts_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;

-- FAQ-style retrieval knowledge base — the Chatbot's "FAQ" feature and
-- the AI Assistant's "Answer Questions" feature both search this
-- before (or instead of) calling out to the LLM.
CREATE TABLE IF NOT EXISTS knowledge_base_articles (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NULL,
    category          VARCHAR(100)    NULL,
    question          VARCHAR(500)    NOT NULL,
    answer            TEXT            NOT NULL,
    is_active         TINYINT(1)      NOT NULL DEFAULT 1,
    view_count        INT UNSIGNED    NOT NULL DEFAULT 0,
    created_by        BIGINT UNSIGNED NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at        DATETIME        NULL,
    FULLTEXT KEY ft_knowledge_base_question (question, answer),
    KEY idx_knowledge_base_business_line (business_line_id),
    CONSTRAINT fk_knowledge_base_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_knowledge_base_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- "Training Data": curated example prompt/ideal-response pairs kept
-- for review and export (JSONL) — e.g. for future fine-tuning or for
-- a human to audit what the AI should say in tricky situations. This
-- is deliberately NOT fed automatically into every request (that's
-- what ai_prompts + knowledge_base_articles are for); it's a curated,
-- human-reviewed reference set.
CREATE TABLE IF NOT EXISTS training_examples (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    feature        ENUM('ai_assistant','chatbot','voice_agent') NOT NULL,
    prompt_text    TEXT            NOT NULL,
    ideal_response TEXT            NOT NULL,
    notes          VARCHAR(500)    NULL,
    created_by     BIGINT UNSIGNED NULL,
    created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_training_examples_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Generic inbound-webhook audit log — every Twilio call-status ping,
-- Meta lead-ad submission, WhatsApp delivery receipt, etc. lands here
-- first (raw payload preserved) before being processed, so a failed
-- or malformed webhook is debuggable from Admin instead of only ever
-- existing in a provider's dashboard.
CREATE TABLE IF NOT EXISTS webhook_events (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider     VARCHAR(60)     NOT NULL COMMENT 'twilio, meta, whatsapp, google...',
    event_type   VARCHAR(100)    NULL,
    payload      JSON            NOT NULL,
    processed    TINYINT(1)      NOT NULL DEFAULT 0,
    error_message VARCHAR(500)   NULL,
    received_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_webhook_events_provider (provider, received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;


SET FOREIGN_KEY_CHECKS = 1;
