-- QuitSure AI Support — chat + feedback logging
-- Naming follows the QuitSure convention: tbl_ tables, Hungarian columns, iXxxID auto PK.
-- Create these two tables in the test DB (quitsure). The app only INSERTs/SELECTs.
-- App connects via .env: DB_HOST / DB_PORT / DB_DATABASE / DB_USERNAME / DB_PASSWORD (PyMySQL).
-- App DB user needs INSERT + SELECT on these two tables (no CREATE).

-- One row per assistant reply
CREATE TABLE IF NOT EXISTS tbl_SupportChatLog (
    iChatLogID       BIGINT AUTO_INCREMENT PRIMARY KEY,
    vMessageID       VARCHAR(32) NOT NULL,          -- id returned by /chat (links to feedback)
    iUserID          INT NULL,                      -- app iUserID (null for anonymous/test)
    vProgram         VARCHAR(8) NULL,               -- P3 / P9
    vPlatform        VARCHAR(16) NULL,              -- ios / android
    vMessage         TEXT NULL,                     -- the user's message
    vRetrievalQuery  TEXT NULL,                     -- condensed query used for retrieval
    decBestSim       DECIMAL(6,4) NULL,             -- top cosine similarity
    bEscalate        TINYINT(1) NOT NULL DEFAULT 0, -- 1 = handed to a human
    vEscalateReason  VARCHAR(32) NULL,              -- greeting / out_of_scope / medical_topic / ...
    vAnswer          MEDIUMTEXT NULL,               -- the reply shown to the user
    vGate            VARCHAR(24) NULL,              -- model / crisis_filter / error
    dCreated         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_message (vMessageID),
    KEY idx_created (dCreated),
    KEY idx_reason (vEscalateReason)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per thumbs up/down, linked to tbl_SupportChatLog by vMessageID
CREATE TABLE IF NOT EXISTS tbl_SupportChatFeedback (
    iFeedbackID  BIGINT AUTO_INCREMENT PRIMARY KEY,
    vMessageID   VARCHAR(32) NOT NULL,              -- -> tbl_SupportChatLog.vMessageID
    iUserID      INT NULL,
    vRating      VARCHAR(8) NULL,                   -- up / down
    vNote        TEXT NULL,                         -- optional "what felt off" on a down
    dCreated     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_message (vMessageID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- Handy queries for the analytics / weekly review ----

-- Answer vs escalate vs decline
-- SELECT
--   SUM(bEscalate=0 AND (vEscalateReason IS NULL OR vEscalateReason='greeting')) AS answered,
--   SUM(vEscalateReason='out_of_scope')                                          AS declined,
--   SUM(bEscalate=1)                                                             AS escalated,
--   COUNT(*)                                                                     AS total
-- FROM tbl_SupportChatLog;

-- Escalations grouped by reason
-- SELECT vEscalateReason, COUNT(*) FROM tbl_SupportChatLog WHERE bEscalate=1
-- GROUP BY vEscalateReason ORDER BY COUNT(*) DESC;

-- Thumbs-up rate
-- SELECT vRating, COUNT(*) FROM tbl_SupportChatFeedback GROUP BY vRating;

-- Every thumbs-down with the question and answer (the weekly fix list)
-- SELECT f.dCreated, f.vNote, c.vMessage, c.vAnswer, c.vEscalateReason
-- FROM tbl_SupportChatFeedback f LEFT JOIN tbl_SupportChatLog c ON f.vMessageID = c.vMessageID
-- WHERE f.vRating='down' ORDER BY f.dCreated DESC;
