-- ============================================================================
--  Lane Cove Acupuncture — Website Chatbot
--  MySQL 8.x / MariaDB 10.4+ schema
--  Lives in the shared `clinics_db` database; tables prefixed `lca_`.
-- ============================================================================

CREATE DATABASE IF NOT EXISTS clinics_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE clinics_db;

-- ----------------------------------------------------------------------------
--  Conversations / sessions
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS lca_sessions (
  id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  token           CHAR(36)         NULL,
  page_url        VARCHAR(500)     NULL,
  referrer        VARCHAR(500)     NULL,
  ip_address      VARCHAR(64)      NULL,
  user_agent      VARCHAR(255)     NULL,
  language        VARCHAR(10)      NULL,
  lead_name       VARCHAR(160)     NULL,
  lead_email      VARCHAR(160)     NULL,
  lead_phone      VARCHAR(40)      NULL,
  lead_topic      VARCHAR(120)     NULL,
  lead_captured_at DATETIME        NULL,
  message_count   INT UNSIGNED     NULL DEFAULT 0,
  total_tokens    INT UNSIGNED     NULL DEFAULT 0,
  status          VARCHAR(20)      NULL DEFAULT 'open',
  started_at      DATETIME         NULL DEFAULT CURRENT_TIMESTAMP,
  last_message_at DATETIME         NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_token (token),
  KEY idx_lead_email (lead_email),
  KEY idx_started (started_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS lca_messages (
  id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  session_id      BIGINT UNSIGNED  NULL,
  role            VARCHAR(20)      NULL,
  content         MEDIUMTEXT       NULL,
  tokens_in       INT UNSIGNED     NULL,
  tokens_out      INT UNSIGNED     NULL,
  model           VARCHAR(60)      NULL,
  latency_ms      INT UNSIGNED     NULL,
  retrieved_chunk_ids VARCHAR(255) NULL,
  created_at      DATETIME         NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_session (session_id, created_at),
  CONSTRAINT fk_lcamsg_session FOREIGN KEY (session_id)
    REFERENCES lca_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS lca_kb_documents (
  id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  source_type     VARCHAR(40)      NULL,
  source_url      VARCHAR(500)     NULL,
  title           VARCHAR(255)     NULL,
  language        VARCHAR(10)      NULL DEFAULT 'en',
  content_hash    CHAR(64)         NULL,
  raw_content     MEDIUMTEXT       NULL,
  word_count      INT UNSIGNED     NULL,
  is_active       TINYINT(1)       NULL DEFAULT 1,
  ingested_at     DATETIME         NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME         NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_hash (content_hash),
  KEY idx_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS lca_kb_chunks (
  id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  document_id     BIGINT UNSIGNED  NULL,
  chunk_index     INT UNSIGNED     NULL,
  chunk_text      TEXT             NULL,
  word_count      INT UNSIGNED     NULL,
  PRIMARY KEY (id),
  KEY idx_doc (document_id, chunk_index),
  FULLTEXT KEY ft_chunk (chunk_text),
  CONSTRAINT fk_lcachunk_doc FOREIGN KEY (document_id)
    REFERENCES lca_kb_documents(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS lca_faq (
  id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  question        VARCHAR(500)     NULL,
  answer          TEXT             NULL,
  keywords        VARCHAR(500)     NULL,
  language        VARCHAR(10)      NULL DEFAULT 'en',
  is_active       TINYINT(1)       NULL DEFAULT 1,
  created_at      DATETIME         NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FULLTEXT KEY ft_faq (question, keywords)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS lca_lead_outbox (
  id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  session_id      BIGINT UNSIGNED  NULL,
  payload         TEXT             NULL,
  sent_at         DATETIME         NULL,
  created_at      DATETIME         NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_unsent (sent_at)
) ENGINE=InnoDB;

CREATE OR REPLACE VIEW v_lca_recent_sessions AS
SELECT s.id, s.token, s.lead_name, s.lead_email, s.lead_topic,
       s.message_count, s.total_tokens, s.status,
       s.started_at, s.last_message_at, s.page_url
FROM lca_sessions s
ORDER BY s.started_at DESC;

CREATE OR REPLACE VIEW v_lca_kb_status AS
SELECT d.id, d.title, d.source_url, d.source_type,
       d.word_count, d.is_active, d.ingested_at,
       COUNT(c.id) AS chunk_count
FROM lca_kb_documents d
LEFT JOIN lca_kb_chunks c ON c.document_id = d.id
GROUP BY d.id, d.title, d.source_url, d.source_type,
         d.word_count, d.is_active, d.ingested_at;