-- ============================================================
-- Migration 032  -  Voice AI Receptionist
-- Adds tables for voice agents, sessions, prompts,
-- provisioned phone numbers, and doctor/staff registries.
-- ============================================================

-- Voice AI agents: one per configured receptionist persona.
CREATE TABLE IF NOT EXISTS voice_ai_agents (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id          BIGINT UNSIGNED NOT NULL,
    name            VARCHAR(120) NOT NULL,                          -- e.g. "Reception  -  City Clinic"
    persona_name    VARCHAR(80)  NOT NULL DEFAULT 'Alex',           -- spoken name used in conversation
    vertical        VARCHAR(60)  NOT NULL DEFAULT 'general',        -- medical | real_estate | legal | general
    prompt_template TEXT,                                           -- base system prompt
    voice_provider  VARCHAR(40)  NOT NULL DEFAULT 'openai',         -- openai | elevenlabs | google
    voice_id        VARCHAR(120) DEFAULT NULL,                      -- provider-specific voice id
    llm_provider    VARCHAR(40)  NOT NULL DEFAULT 'openai',         -- openai | anthropic | local
    llm_model       VARCHAR(80)  NOT NULL DEFAULT 'gpt-4o-realtime-preview',
    phone_number_id BIGINT UNSIGNED DEFAULT NULL,                      -- FK -> voice_phone_numbers.id
    max_call_secs   SMALLINT UNSIGNED NOT NULL DEFAULT 600,         -- hard limit 10 min
    active          TINYINT(1)   NOT NULL DEFAULT 1,
    created_at      DATETIME     NOT NULL,
    updated_at      DATETIME     DEFAULT NULL,
    INDEX idx_vaia_org (org_id),
    FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Voice AI call sessions (one row per call).
CREATE TABLE IF NOT EXISTS voice_ai_sessions (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id          BIGINT UNSIGNED    NOT NULL,
    agent_id        BIGINT UNSIGNED    NOT NULL,
    call_sid        VARCHAR(80)     DEFAULT NULL,                   -- provider call ID (Twilio SID etc.)
    caller_number   VARCHAR(30)     DEFAULT NULL,
    direction       ENUM('inbound','outbound') NOT NULL DEFAULT 'inbound',
    status          ENUM('ringing','active','completed','failed','no_answer') NOT NULL DEFAULT 'ringing',
    duration_secs   SMALLINT UNSIGNED DEFAULT 0,
    tokens_used     INT UNSIGNED    DEFAULT 0,
    cost_cents      INT UNSIGNED    DEFAULT 0,                      -- AUD cents
    transcript      MEDIUMTEXT      DEFAULT NULL,                   -- full JSON transcript
    outcome_summary VARCHAR(500)    DEFAULT NULL,                   -- AI-generated 1-line summary
    appointment_booked TINYINT(1)   NOT NULL DEFAULT 0,
    lead_id         INT UNSIGNED    DEFAULT NULL,                   -- linked lead if auto-created
    started_at      DATETIME        NOT NULL,
    ended_at        DATETIME        DEFAULT NULL,
    INDEX idx_vais_org    (org_id),
    INDEX idx_vais_agent  (agent_id),
    INDEX idx_vais_started (started_at),
    FOREIGN KEY (org_id)   REFERENCES organizations(id)    ON DELETE CASCADE,
    FOREIGN KEY (agent_id) REFERENCES voice_ai_agents(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reusable prompt templates per vertical (shared across all orgs).
CREATE TABLE IF NOT EXISTS voice_ai_prompts (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug            VARCHAR(80)  NOT NULL UNIQUE,
    vertical        VARCHAR(60)  NOT NULL,
    name            VARCHAR(120) NOT NULL,
    description     VARCHAR(300) DEFAULT NULL,
    system_prompt   TEXT         NOT NULL,
    example_convo   TEXT         DEFAULT NULL,                      -- JSON array of example turns
    is_builtin      TINYINT(1)   NOT NULL DEFAULT 0,                -- 1 = shipped with B-connex, 0 = org-created
    org_id          BIGINT UNSIGNED DEFAULT NULL,                      -- NULL = global built-in
    created_at      DATETIME     NOT NULL,
    INDEX idx_vaip_vertical (vertical),
    INDEX idx_vaip_org (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Provisioned phone numbers (one row per number, may be shared across agents).
CREATE TABLE IF NOT EXISTS voice_phone_numbers (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id          BIGINT UNSIGNED NOT NULL,
    number_e164     VARCHAR(20)  NOT NULL,                          -- +61299991234
    friendly_name   VARCHAR(60)  DEFAULT NULL,
    provider        VARCHAR(40)  NOT NULL DEFAULT 'twilio',         -- twilio | vonage | sip_direct
    provider_sid    VARCHAR(100) DEFAULT NULL,                      -- external resource ID
    sip_endpoint    VARCHAR(200) DEFAULT NULL,                      -- SIP URI if direct
    capabilities    JSON         DEFAULT NULL,                      -- {"voice":true,"sms":false}
    status          ENUM('active','released','porting') NOT NULL DEFAULT 'active',
    monthly_cents   INT UNSIGNED NOT NULL DEFAULT 200,              -- A$2/month Twilio local
    provisioned_at  DATETIME     NOT NULL,
    released_at     DATETIME     DEFAULT NULL,
    UNIQUE KEY uq_vpn_number (number_e164),
    INDEX idx_vpn_org (org_id),
    FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Doctor / staff registry per org (medical vertical).
-- Loaded from AHPRA-style scraped data or manually entered.
CREATE TABLE IF NOT EXISTS voice_registry_entries (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id          BIGINT UNSIGNED NOT NULL,
    registry_type   VARCHAR(40)  NOT NULL DEFAULT 'doctor',         -- doctor | specialist | nurse | staff
    full_name       VARCHAR(150) NOT NULL,
    gender          ENUM('male','female','non_binary','unspecified') NOT NULL DEFAULT 'unspecified',
    specialty       VARCHAR(120) DEFAULT NULL,                      -- e.g. Mental Health, General Practice
    languages       VARCHAR(200) DEFAULT NULL,                      -- comma-separated
    qualifications  VARCHAR(300) DEFAULT NULL,
    availability_json TEXT        DEFAULT NULL,                     -- JSON: {"mon":["09:00-17:00"],...}
    accepting_new   TINYINT(1)   NOT NULL DEFAULT 1,
    notes           VARCHAR(500) DEFAULT NULL,
    active          TINYINT(1)   NOT NULL DEFAULT 1,
    created_at      DATETIME     NOT NULL,
    updated_at      DATETIME     DEFAULT NULL,
    INDEX idx_vre_org      (org_id),
    INDEX idx_vre_type     (registry_type),
    INDEX idx_vre_specialty (specialty(40)),
    FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Voice AI usage token ledger (mirrors ai_usage pattern).
CREATE TABLE IF NOT EXISTS voice_ai_usage (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    org_id          BIGINT UNSIGNED    NOT NULL,
    month           CHAR(7)         NOT NULL,                       -- YYYY-MM
    minutes_used    INT UNSIGNED    NOT NULL DEFAULT 0,
    tokens_used     INT UNSIGNED    NOT NULL DEFAULT 0,
    cost_cents      INT UNSIGNED    NOT NULL DEFAULT 0,
    updated_at      DATETIME        NOT NULL,
    UNIQUE KEY uq_vau_org_month (org_id, month),
    FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed built-in prompt templates for key verticals --

INSERT IGNORE INTO voice_ai_prompts
    (slug, vertical, name, description, system_prompt, is_builtin, org_id, created_at)
VALUES
(
    'medical_gp_reception',
    'medical',
    'GP Clinic Reception',
    'General practice clinic  -  route patients to available doctors, match preferences (gender, specialty, language), and book appointment slots.',
    'You are {{persona_name}}, the AI receptionist at {{clinic_name}}. Your role is to help patients find the right doctor and book appointments. Be warm, professional and concise.\n\nYou have access to the clinic doctor list. When a patient calls:\n1. Greet them warmly (only at the very beginning of the call; do not repeat greetings or clinic introductions in subsequent turns).\n2. Ask the nature of their appointment (general checkup, mental health, chronic disease, etc.).\n3. Ask if they have any preferences for the doctor (gender, language, name).\n4. Search the available doctors that match and present 1-2 options with their next available slot.\n5. Confirm the booking or offer to take a callback number.\n6. Always offer the clinic address and remind them to bring Medicare card.\n\nNever provide medical advice. Escalate anything urgent to "please call 000 or go to your nearest emergency department".\n\nDoctor list (injected at runtime): {{doctor_list}}\n\nCurrent date/time: {{datetime}}',
    1, NULL, NOW()
),
(
    'medical_specialist_referral',
    'medical',
    'Specialist Referral Coordination',
    'Coordinates specialist referrals  -  confirms referral received, explains what to bring, books first appointment.',
    'You are {{persona_name}}, the appointment coordinator at {{clinic_name}}. You handle referral intake calls.\n\nWhen a patient calls about a referral:\n1. Confirm their name and date of birth.\n2. Confirm the referral has been received from their GP.\n3. Explain the next steps and approximate wait time.\n4. Offer an appointment booking if slots are available.\n5. Explain what to bring (referral letter, test results, Medicare card).\n6. For urgent referrals, escalate to the clinic manager.\n\nSpecialist list: {{doctor_list}}\nCurrent date/time: {{datetime}}',
    1, NULL, NOW()
),
(
    'real_estate_enquiry',
    'real_estate',
    'Property Enquiry Handling',
    'Real estate agency  -  routes property enquiries to the right agent, books property inspections.',
    'You are {{persona_name}}, the AI receptionist at {{agency_name}}. Help callers with property enquiries.\n\n1. Ask if they are buying, selling, or renting.\n2. Ask for their preferred suburb or property type.\n3. Match them to an available agent with relevant expertise.\n4. Offer to book an inspection or callback.\n5. Never quote prices  -  direct pricing questions to the agent.\n\nAgent list: {{doctor_list}}\nCurrent date/time: {{datetime}}',
    1, NULL, NOW()
),
(
    'general_reception',
    'general',
    'General Business Reception',
    'General-purpose AI receptionist  -  routes calls, takes messages, and answers FAQs.',
    'You are {{persona_name}}, the AI receptionist at {{clinic_name}}. Your goal is to route callers to the right person or department efficiently.\n\n1. Greet the caller and ask how you can help (only at the very beginning of the call; do not repeat greetings or introductions in subsequent turns).\n2. Determine the nature of their enquiry.\n3. Route to the appropriate team member or department.\n4. If unavailable, take a message (name, number, brief reason).\n5. Answer common FAQs from the knowledge base: {{faq_list}}\n\nCurrent date/time: {{datetime}}',
    1, NULL, NOW()
),
(
    'legal_intake',
    'legal',
    'Legal Firm Client Intake',
    'Screens new client enquiries, gathers matter type, and books consultation appointments.',
    'You are {{persona_name}}, the intake coordinator at {{clinic_name}}. You handle new client enquiries.\n\n1. Ask the nature of the legal matter (family law, property, commercial, wills, etc.).\n2. Ask if it is time-sensitive or urgent.\n3. Match to an available solicitor with the right specialisation.\n4. Book a free 15-minute consultation if available.\n5. Never give legal advice. Remind the caller all consultations are confidential.\n\nSolicitor list: {{doctor_list}}\nCurrent date/time: {{datetime}}',
    1, NULL, NOW()
);
