-- ============================================================================
--  Health Therapies Clinics — Website Chatbot
--  MySQL 8.x / MariaDB 10.4+ schema
--  Two audiences: patients (treatment Q&A) + practitioners (room rental).
--  Every column nullable so partial saves never fail.
-- ============================================================================

-- Shared database — also hosts the intake form (intake_*) and the Rainbow
-- Medicine bot (chatbot_*). Our tables use the `htc_` prefix.
CREATE DATABASE IF NOT EXISTS clinics_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE clinics_db;

-- ----------------------------------------------------------------------------
--  Conversations / sessions — one row per browser visitor.
--  `intent` lets us route the conversation: patient | practitioner | unknown
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS htc_sessions (
  id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  token           CHAR(36)         NULL,
  intent          VARCHAR(20)      NULL DEFAULT 'unknown',  -- patient | practitioner | unknown
  page_url        VARCHAR(500)     NULL,
  referrer        VARCHAR(500)     NULL,
  ip_address      VARCHAR(64)      NULL,
  user_agent      VARCHAR(255)     NULL,
  language        VARCHAR(10)      NULL,
  -- Lead capture (patients)
  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_intent (intent),
  KEY idx_lead_email (lead_email)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
--  Messages — every turn for transcript / audit.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS htc_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_htcmsg_session FOREIGN KEY (session_id)
    REFERENCES htc_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
--  Knowledge base — source documents (site pages or curated PDFs)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS htc_kb_documents (
  id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  source_type     VARCHAR(40)      NULL,       -- 'site' | 'pdf' | 'docx' | 'manual'
  source_url      VARCHAR(500)     NULL,
  title           VARCHAR(255)     NULL,
  language        VARCHAR(10)      NULL DEFAULT 'en',
  audience        VARCHAR(20)      NULL DEFAULT 'all',  -- patient | practitioner | all
  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),
  KEY idx_audience (audience)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS htc_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_htcchunk_doc FOREIGN KEY (document_id)
    REFERENCES htc_kb_documents(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
--  Curated FAQ that wins over generated answers.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS htc_faq (
  id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  question        VARCHAR(500)     NULL,
  answer          TEXT             NULL,
  keywords        VARCHAR(500)     NULL,
  audience        VARCHAR(20)      NULL DEFAULT 'all',
  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),
  KEY idx_audience (audience)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
--  Patient lead outbox (mirror of rainbow chatbot)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS htc_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;

-- ----------------------------------------------------------------------------
--  ROOM RENTAL APPLICATIONS — practitioner side of the bot.
--  All fields nullable so partial saves work; status drives the workflow.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS htc_rental_applications (
  id                      BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  session_id              BIGINT UNSIGNED  NULL,
  -- Identity
  practitioner_name       VARCHAR(160)     NULL,
  email                   VARCHAR(160)     NULL,
  phone                   VARCHAR(40)      NULL,
  business_name           VARCHAR(160)     NULL,
  abn                     VARCHAR(40)      NULL,
  -- Practice profile
  primary_modality        VARCHAR(120)     NULL,        -- e.g. "Naturopathy"
  years_experience        INT UNSIGNED     NULL,
  qualifications          TEXT             NULL,        -- free text
  registration_body       VARCHAR(160)     NULL,        -- AHPRA, ATMS, ANTA, etc.
  registration_number     VARCHAR(80)      NULL,
  insurance_provider      VARCHAR(160)     NULL,
  insurance_expiry        DATE             NULL,
  hicaps_provider_no      VARCHAR(40)      NULL,
  has_hicaps              TINYINT(1)       NULL,        -- 0/1
  -- Rental needs
  preferred_location      VARCHAR(80)      NULL,        -- 'Lane Cove' | 'Freshwater' | 'Either'
  rental_type             VARCHAR(40)      NULL,        -- 'casual' | 'monthly' | 'either'
  preferred_days          VARCHAR(160)     NULL,        -- e.g. "Mon AM, Wed PM"
  estimated_hours_week    INT UNSIGNED     NULL,
  start_date              DATE             NULL,
  sells_retail_products   TINYINT(1)       NULL,
  bring_own_equipment     TINYINT(1)       NULL,
  needs_storage           TINYINT(1)       NULL,
  references_available    TINYINT(1)       NULL,
  tour_requested          TINYINT(1)       NULL,
  preferred_tour_time     VARCHAR(120)     NULL,        -- "Tue afternoon, Thu morning"
  -- Marketing
  how_heard               VARCHAR(120)     NULL,
  notes                   TEXT             NULL,
  -- Workflow
  status                  VARCHAR(30)      NULL DEFAULT 'submitted',
                                    -- submitted | reviewing | tour_booked | offered | declined | active
  internal_notes          TEXT             NULL,
  created_at              DATETIME         NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at              DATETIME         NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  reviewed_at             DATETIME         NULL,
  PRIMARY KEY (id),
  KEY idx_session (session_id),
  KEY idx_status (status),
  KEY idx_email (email),
  CONSTRAINT fk_app_session FOREIGN KEY (session_id)
    REFERENCES htc_sessions(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
--  Multi-select for additional modalities the practitioner offers.
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS htc_rental_app_modalities (
  id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  application_id  BIGINT UNSIGNED  NULL,
  modality        VARCHAR(80)      NULL,
                  -- Bowen | Chiropractic | Diet | Kinesiology | Naturopathy
                  -- Massage | Nutrition | Reiki | Psychology | Counselling | Other
  modality_other  VARCHAR(120)     NULL,
  created_at      DATETIME         NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_app (application_id),
  CONSTRAINT fk_appmod_app FOREIGN KEY (application_id)
    REFERENCES htc_rental_applications(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
--  Rental application notification queue (cron picks these up to email)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS htc_rental_app_outbox (
  id              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
  application_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;

-- ----------------------------------------------------------------------------
--  Helpful views
-- ----------------------------------------------------------------------------
CREATE OR REPLACE VIEW v_htc_recent_sessions AS
SELECT s.id, s.token, s.intent, 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 htc_sessions s
ORDER BY s.started_at DESC;

CREATE OR REPLACE VIEW v_htc_open_applications AS
SELECT a.id, a.practitioner_name, a.email, a.phone,
       a.primary_modality, a.preferred_location, a.rental_type,
       a.tour_requested, a.status, a.created_at,
       (SELECT GROUP_CONCAT(m.modality SEPARATOR ', ')
        FROM htc_rental_app_modalities m WHERE m.application_id = a.id) AS modalities
FROM htc_rental_applications a
WHERE a.status NOT IN ('declined','active')
ORDER BY a.created_at DESC;

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