-- ============================================================
-- AI Support Ticket System - Complete Database Schema
-- Engine: MySQL 8.0+
-- ============================================================


-- ── TENANTS ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tenants (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(150) NOT NULL,
    slug          VARCHAR(100) NOT NULL UNIQUE,
    domain        VARCHAR(200) NULL,
    logo_url      VARCHAR(500) NULL,
    brand_color   VARCHAR(7)   DEFAULT '#4472C4',
    plan          ENUM('starter','growth','business') DEFAULT 'starter',
    is_vip        TINYINT(1)   DEFAULT 0,
    sla_response_minutes  INT  DEFAULT 240,
    sla_resolve_hours     INT  DEFAULT 48,
    ai_auto_send_threshold DECIMAL(4,2) DEFAULT 0.90,
    gemini_context TEXT NULL COMMENT 'Brand voice / system prompt context for this tenant',
    is_active     TINYINT(1)   DEFAULT 1,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SESSIONS ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sessions (
    id         VARCHAR(128) PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    ip_address VARCHAR(45)  NULL,
    user_agent VARCHAR(500) NULL,
    expires_at TIMESTAMP    NOT NULL,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user    (user_id),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── TICKETS ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tickets (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id       INT UNSIGNED NOT NULL,
    ticket_number   VARCHAR(20)  NOT NULL UNIQUE,
    subject         VARCHAR(500) NOT NULL,
    body            TEXT         NOT NULL,
    customer_name   VARCHAR(150) NOT NULL,
    customer_email  VARCHAR(200) NOT NULL,
    channel         ENUM('email','whatsapp','portal','api','chat') DEFAULT 'portal',
    status          ENUM('open','pending','waiting_client','resolved','closed') DEFAULT 'open',
    priority        TINYINT      DEFAULT 3 COMMENT '1=critical 2=high 3=medium 4=low 5=minimal',
    priority_score  DECIMAL(5,2) DEFAULT 0.00 COMMENT 'AI computed score 0-100',
    category        VARCHAR(100) NULL,
    sub_category    VARCHAR(100) NULL,
    sentiment       ENUM('positive','neutral','negative','frustrated') DEFAULT 'neutral',
    language        VARCHAR(10)  DEFAULT 'en',
    is_vip          TINYINT(1)   DEFAULT 0,
    churn_risk      TINYINT(1)   DEFAULT 0,
    duplicate_of    INT UNSIGNED NULL,
    ai_resolved     TINYINT(1)   DEFAULT 0,
    ai_confidence   DECIMAL(4,2) DEFAULT 0.00,
    sla_response_due TIMESTAMP   NULL,
    sla_resolve_due  TIMESTAMP   NULL,
    sla_breached    TINYINT(1)   DEFAULT 0,
    first_response_at TIMESTAMP  NULL,
    resolved_at     TIMESTAMP    NULL,
    closed_at       TIMESTAMP    NULL,
    csat_score      TINYINT      NULL COMMENT '1-5',
    csat_comment    TEXT         NULL,
    created_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (duplicate_of) REFERENCES tickets(id) ON DELETE SET NULL,
    INDEX idx_tenant   (tenant_id),
    INDEX idx_status   (status),
    INDEX idx_priority (priority_score DESC),
    INDEX idx_sla      (sla_response_due),
    INDEX idx_customer (customer_email),
    INDEX idx_created  (created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── TICKET MESSAGES ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ticket_messages (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id   INT UNSIGNED NOT NULL,
    sender_type ENUM('customer','agent','ai','system') NOT NULL,
    sender_name VARCHAR(150) NULL,
    body        TEXT         NOT NULL,
    is_internal TINYINT(1)   DEFAULT 0 COMMENT 'Internal note only',
    ai_draft    TINYINT(1)   DEFAULT 0 COMMENT 'Was this an AI draft?',
    ai_edited   TINYINT(1)   DEFAULT 0 COMMENT 'Was AI draft edited before send?',
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    INDEX idx_ticket  (ticket_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AI TRIAGE LOG ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ai_triage_logs (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id       INT UNSIGNED NOT NULL,
    category        VARCHAR(100) NULL,
    sub_category    VARCHAR(100) NULL,
    priority        TINYINT      NULL,
    priority_score  DECIMAL(5,2) NULL,
    sentiment       VARCHAR(20)  NULL,
    language        VARCHAR(10)  NULL,
    intent          VARCHAR(200) NULL,
    churn_risk      TINYINT(1)   DEFAULT 0,
    is_duplicate    TINYINT(1)   DEFAULT 0,
    duplicate_of_id INT UNSIGNED NULL,
    confidence      DECIMAL(4,2) NULL,
    raw_response    JSON         NULL,
    model_used      VARCHAR(100) NULL,
    tokens_used     INT          NULL,
    latency_ms      INT          NULL,
    created_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    INDEX idx_ticket (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AI REPLY DRAFTS ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ai_reply_drafts (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id     INT UNSIGNED NOT NULL,
    draft_body    TEXT         NOT NULL,
    tone          ENUM('formal','friendly','brief') DEFAULT 'friendly',
    confidence    DECIMAL(4,2) DEFAULT 0.00,
    kb_articles_used JSON      NULL COMMENT 'Array of KB article IDs used in RAG',
    auto_sent     TINYINT(1)   DEFAULT 0,
    accepted      TINYINT(1)   DEFAULT 0,
    edited        TINYINT(1)   DEFAULT 0,
    model_used    VARCHAR(100) NULL,
    tokens_used   INT          NULL,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    INDEX idx_ticket (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── KNOWLEDGE BASE ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS kb_articles (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT UNSIGNED NOT NULL,
    title         VARCHAR(400) NOT NULL,
    body          LONGTEXT     NOT NULL,
    category      VARCHAR(100) NULL,
    tags          JSON         NULL,
    embedding     JSON         NULL COMMENT 'Float array from Gemini embedding model',
    embedding_hash VARCHAR(64) NULL COMMENT 'SHA256 of body for change detection',
    is_published  TINYINT(1)   DEFAULT 0,
    ai_generated  TINYINT(1)   DEFAULT 0,
    times_matched INT UNSIGNED DEFAULT 0,
    times_resolved INT UNSIGNED DEFAULT 0,
    created_by    INT UNSIGNED NULL,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id)  REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_tenant    (tenant_id),
    INDEX idx_published (is_published),
    INDEX idx_category  (category),
    FULLTEXT INDEX ft_search (title, body)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CANNED RESPONSES ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS canned_responses (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    title     VARCHAR(200) NOT NULL,
    body      TEXT         NOT NULL,
    category  VARCHAR(100) NULL,
    use_count INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP   DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── ALERTS ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS alerts (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ticket_id   INT UNSIGNED NULL,
    type        ENUM('sla_breach','churn_risk','negative_csat','inactivity','repeat_contact') NOT NULL,
    message     VARCHAR(500) NOT NULL,
    is_read     TINYINT(1)   DEFAULT 0,
    sent_via    SET('dashboard','email','whatsapp') DEFAULT 'dashboard',
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    INDEX idx_read    (is_read),
    INDEX idx_created (created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AUDIT LOG ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_logs (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NULL,
    tenant_id  INT UNSIGNED NULL,
    action     VARCHAR(100) NOT NULL,
    entity     VARCHAR(50)  NULL,
    entity_id  INT UNSIGNED NULL,
    meta       JSON         NULL,
    ip_address VARCHAR(45)  NULL,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_entity  (entity, entity_id),
    INDEX idx_user    (user_id),
    INDEX idx_created (created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SEED DATA ─────────────────────────────────────────────────
INSERT INTO tenants (name, slug, plan, sla_response_minutes, sla_resolve_hours, gemini_context)
VALUES ('Demo Client', 'demo-client', 'growth', 240, 48,
        'You are a helpful support agent for Demo Client. Be professional, concise, and empathetic.');



INSERT INTO canned_responses (tenant_id, title, body, category)
VALUES
  (1, 'Thank you for contacting us',
   'Thank you for reaching out! I have received your request and will look into it shortly. I will get back to you within {sla_time}.',
   'General'),
  (1, 'Request resolved',
   'I am happy to let you know that your issue has been resolved. Please let us know if you need any further assistance.',
   'Resolution'),
  (1, 'Need more information',
   'Thank you for contacting us. To help you better, could you please provide the following additional details:\n\n1. {detail_1}\n2. {detail_2}\n\nOnce I have this information, I will be able to assist you promptly.',
   'Follow-up');

