-- =====================================================================
-- AI Front Desk Platform — Initial Schema
-- Separate database from the existing Telnyx/OpenSIP calling-card
-- platform. Runs on the same MySQL server. No cross-DB foreign keys —
-- any linkage to the calling-card platform (e.g. shared DID inventory)
-- happens at the application layer via API/service calls, not JOINs,
-- so the two systems stay decoupled and independently deployable.
-- =====================================================================


-- ---------------------------------------------------------------------
-- tenants
-- ---------------------------------------------------------------------
CREATE TABLE tenants (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid                CHAR(36)        NOT NULL,
    business_name       VARCHAR(191)    NOT NULL,
    industry            VARCHAR(64)     NULL,          -- hvac, plumbing, dental, legal, etc.
    timezone            VARCHAR(64)     NOT NULL DEFAULT 'America/New_York',
    status              ENUM('trial','active','suspended','cancelled') NOT NULL DEFAULT 'trial',
    plan                VARCHAR(64)     NOT NULL DEFAULT 'starter',
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tenants_uuid (uuid),
    KEY idx_tenants_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- users  (admin portal logins — tenant staff + platform superadmins)
-- ---------------------------------------------------------------------
CREATE TABLE users (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NULL,           -- NULL = platform superadmin
    name                VARCHAR(191)    NOT NULL,
    email               VARCHAR(191)    NOT NULL,
    password_hash       VARCHAR(255)    NOT NULL,
    role                ENUM('superadmin','owner','manager','agent') NOT NULL DEFAULT 'owner',
    is_active           TINYINT(1)      NOT NULL DEFAULT 1,
    last_login_at       DATETIME        NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_tenant (tenant_id),
    CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- phone_numbers  (DIDs assigned to a tenant, provisioned via Telnyx)
-- ---------------------------------------------------------------------
CREATE TABLE phone_numbers (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    e164_number         VARCHAR(20)     NOT NULL,       -- +14045551234
    provider             VARCHAR(32)     NOT NULL DEFAULT 'telnyx',
    provider_number_id  VARCHAR(128)    NULL,           -- Telnyx phone number resource id
    connection_id       VARCHAR(128)    NULL,           -- Telnyx Call Control connection id
    label               VARCHAR(191)    NULL,
    status              ENUM('active','porting','disabled') NOT NULL DEFAULT 'active',
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_phone_numbers_e164 (e164_number),
    KEY idx_phone_numbers_tenant (tenant_id),
    CONSTRAINT fk_phone_numbers_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- calls
-- ---------------------------------------------------------------------
CREATE TABLE calls (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    phone_number_id     BIGINT UNSIGNED NOT NULL,
    provider             VARCHAR(32)     NOT NULL DEFAULT 'telnyx',
    provider_call_id    VARCHAR(191)    NOT NULL,       -- Telnyx call_control_id
    direction           ENUM('inbound','outbound') NOT NULL DEFAULT 'inbound',
    from_number         VARCHAR(20)     NOT NULL,
    to_number            VARCHAR(20)     NOT NULL,
    status              ENUM('ringing','in_progress','completed','failed','no_answer','transferred') NOT NULL DEFAULT 'ringing',
    intent               VARCHAR(64)     NULL,           -- appointment, pricing, emergency, lead, etc.
    started_at          DATETIME        NULL,
    answered_at         DATETIME        NULL,
    ended_at            DATETIME        NULL,
    duration_seconds    INT UNSIGNED    NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_calls_provider_call_id (provider_call_id),
    KEY idx_calls_tenant (tenant_id),
    KEY idx_calls_phone_number (phone_number_id),
    KEY idx_calls_started_at (started_at),
    CONSTRAINT fk_calls_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_calls_phone_number FOREIGN KEY (phone_number_id) REFERENCES phone_numbers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- call_recordings
-- ---------------------------------------------------------------------
CREATE TABLE call_recordings (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    call_id             BIGINT UNSIGNED NOT NULL,
    provider_recording_id VARCHAR(191)  NULL,
    storage_url         VARCHAR(512)    NULL,
    duration_seconds    INT UNSIGNED    NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_recordings_tenant (tenant_id),
    KEY idx_recordings_call (call_id),
    CONSTRAINT fk_recordings_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_recordings_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- call_transcripts  (turn-by-turn conversation log)
-- ---------------------------------------------------------------------
CREATE TABLE call_transcripts (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    call_id             BIGINT UNSIGNED NOT NULL,
    turn_index          INT UNSIGNED    NOT NULL,
    speaker             ENUM('caller','ai','system') NOT NULL,
    message              TEXT            NOT NULL,
    tool_call            JSON            NULL,           -- tool invoked on this turn, if any
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_transcripts_tenant (tenant_id),
    KEY idx_transcripts_call (call_id),
    CONSTRAINT fk_transcripts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_transcripts_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- call_summaries
-- ---------------------------------------------------------------------
CREATE TABLE call_summaries (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    call_id             BIGINT UNSIGNED NOT NULL,
    summary              TEXT            NOT NULL,
    sentiment            VARCHAR(32)     NULL,
    action_items         JSON            NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_summaries_call (call_id),
    KEY idx_summaries_tenant (tenant_id),
    CONSTRAINT fk_summaries_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_summaries_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- contacts
-- ---------------------------------------------------------------------
CREATE TABLE contacts (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    name                VARCHAR(191)    NULL,
    phone               VARCHAR(20)     NOT NULL,
    email               VARCHAR(191)    NULL,
    notes               TEXT            NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_contacts_tenant_phone (tenant_id, phone),
    KEY idx_contacts_tenant (tenant_id),
    CONSTRAINT fk_contacts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- leads
-- ---------------------------------------------------------------------
CREATE TABLE leads (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    contact_id          BIGINT UNSIGNED NOT NULL,
    call_id             BIGINT UNSIGNED NULL,
    source              VARCHAR(64)     NOT NULL DEFAULT 'inbound_call',
    status              ENUM('new','contacted','qualified','won','lost') NOT NULL DEFAULT 'new',
    interest             VARCHAR(191)    NULL,
    notes               TEXT            NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_leads_tenant (tenant_id),
    KEY idx_leads_contact (contact_id),
    KEY idx_leads_call (call_id),
    CONSTRAINT fk_leads_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_leads_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    CONSTRAINT fk_leads_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- appointments  (Phase 2)
-- ---------------------------------------------------------------------
CREATE TABLE appointments (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    contact_id          BIGINT UNSIGNED NOT NULL,
    call_id             BIGINT UNSIGNED NULL,
    scheduled_at         DATETIME        NOT NULL,
    duration_minutes     INT UNSIGNED    NOT NULL DEFAULT 60,
    status              ENUM('scheduled','confirmed','cancelled','completed','no_show') NOT NULL DEFAULT 'scheduled',
    notes               TEXT            NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_appointments_tenant (tenant_id),
    KEY idx_appointments_contact (contact_id),
    KEY idx_appointments_scheduled_at (scheduled_at),
    CONSTRAINT fk_appointments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_appointments_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    CONSTRAINT fk_appointments_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- sms_messages  (Phase 2)
-- ---------------------------------------------------------------------
CREATE TABLE sms_messages (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    contact_id          BIGINT UNSIGNED NULL,
    call_id             BIGINT UNSIGNED NULL,
    provider             VARCHAR(32)     NOT NULL DEFAULT 'telnyx',
    provider_message_id  VARCHAR(191)    NULL,
    direction           ENUM('outbound','inbound') NOT NULL,
    from_number         VARCHAR(20)     NOT NULL,
    to_number            VARCHAR(20)     NOT NULL,
    body                TEXT            NOT NULL,
    status              ENUM('queued','sent','delivered','failed','received') NOT NULL DEFAULT 'queued',
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_sms_tenant (tenant_id),
    KEY idx_sms_contact (contact_id),
    KEY idx_sms_call (call_id),
    CONSTRAINT fk_sms_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_sms_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
    CONSTRAINT fk_sms_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- knowledge_base  (Phase 2)
-- ---------------------------------------------------------------------
CREATE TABLE knowledge_base (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    category            VARCHAR(64)     NULL,            -- hours, pricing, coverage_area, policy, etc.
    question             VARCHAR(512)    NOT NULL,
    answer               TEXT            NOT NULL,
    is_active            TINYINT(1)      NOT NULL DEFAULT 1,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_kb_tenant (tenant_id),
    KEY idx_kb_category (category),
    FULLTEXT KEY ft_kb_question_answer (question, answer),
    CONSTRAINT fk_kb_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- business_rules  (Phase 2)
-- ---------------------------------------------------------------------
CREATE TABLE business_rules (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NOT NULL,
    name                VARCHAR(191)    NOT NULL,
    trigger_type         VARCHAR(64)     NOT NULL,        -- intent_detected, keyword, office_hours, etc.
    trigger_value         JSON            NOT NULL,
    action_type          VARCHAR(64)     NOT NULL,        -- transfer_call, send_sms, create_ticket, etc.
    action_value          JSON            NOT NULL,
    priority             INT UNSIGNED    NOT NULL DEFAULT 100,
    is_active            TINYINT(1)      NOT NULL DEFAULT 1,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_rules_tenant (tenant_id),
    KEY idx_rules_priority (priority),
    CONSTRAINT fk_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- audit_logs
-- ---------------------------------------------------------------------
CREATE TABLE audit_logs (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id           BIGINT UNSIGNED NULL,
    user_id             BIGINT UNSIGNED NULL,
    action                VARCHAR(128)    NOT NULL,
    entity_type           VARCHAR(64)     NULL,
    entity_id             BIGINT UNSIGNED NULL,
    metadata              JSON            NULL,
    ip_address            VARCHAR(45)     NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_tenant (tenant_id),
    KEY idx_audit_user (user_id),
    KEY idx_audit_created_at (created_at),
    CONSTRAINT fk_audit_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL,
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

