-- =====================================================================
-- AI Front Desk Platform — Migration 003
--
-- Phase 2: Knowledge Base enforcement, Appointments, SMS follow-ups,
-- Business Rules Engine.
--
-- What this adds and why:
--   1. tenants.business_hours / tenants.sms_templates — Phase 1 shipped
--      the `appointments`, `sms_messages`, `business_rules` tables but
--      never added anywhere for a tenant to configure business hours or
--      SMS copy. Both are simple per-tenant JSON blobs rather than new
--      tables — this is small, tenant-specific config, not relational
--      data that needs its own joins/indexes.
--   2. contacts.sms_opt_in / sms_opt_in_at — TCPA-style consent must be
--      captured once and remembered; this is the system of record for
--      "are we allowed to text this person automated messages".
--   3. calls.followup_status — idempotency guard + queryable state for
--      the missed-call flow, so a retried call.hangup webhook (Telnyx
--      redelivers on non-2xx, and occasionally duplicates regardless)
--      can never double-send a follow-up text.
--   4. tickets — action_type = 'create_ticket' on business_rules has no
--      backing table anywhere in the Phase 1 schema. Added here as a
--      minimal table; no ticket UI/workflow is implied beyond storage.
-- =====================================================================

SET NAMES utf8mb4;

ALTER TABLE tenants
    ADD COLUMN business_hours JSON NULL
        COMMENT 'Per-day open/close, e.g. {"mon":{"open":"09:00","close":"17:00"},"sat":null}. Missing/null day = closed. Times are in tenants.timezone.'
        AFTER emergency_transfer_number,
    ADD COLUMN sms_templates JSON NULL
        COMMENT 'Per-tenant SMS copy, e.g. {"appointment_confirmation":"...","missed_call":"..."}. Falls back to a built-in default when a key is absent.'
        AFTER business_hours;

ALTER TABLE contacts
    ADD COLUMN sms_opt_in TINYINT(1) NOT NULL DEFAULT 0
        COMMENT 'Explicit spoken consent to receive automated SMS, captured once per contact.'
        AFTER email,
    ADD COLUMN sms_opt_in_at DATETIME NULL
        AFTER sms_opt_in;

ALTER TABLE calls
    ADD COLUMN followup_status ENUM('none','sms_sent','manual_required') NOT NULL DEFAULT 'none'
        COMMENT 'Missed-call follow-up outcome for this call. Set once at call.hangup; guards against duplicate webhook delivery double-sending.'
        AFTER intent;

-- ---------------------------------------------------------------------
-- tickets  (Phase 2 — backs business_rules.action_type = 'create_ticket')
-- ---------------------------------------------------------------------
CREATE TABLE tickets (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    contact_id          BIGINT UNSIGNED NULL,
    call_id             BIGINT UNSIGNED NULL,
    subject              VARCHAR(191)    NOT NULL,
    description          TEXT            NULL,
    status              ENUM('open','in_progress','closed') NOT NULL DEFAULT 'open',
    priority             ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    source               VARCHAR(191)    NULL COMMENT 'e.g. business_rule:<rule name>, for traceability',
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_tickets_tenant (tenant_id),
    KEY idx_tickets_contact (contact_id),
    KEY idx_tickets_call (call_id),
    KEY idx_tickets_status (status),
    CONSTRAINT fk_tickets_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_tickets_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
    CONSTRAINT fk_tickets_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;