-- =====================================================================
-- CareMeet — marketing site schema
-- Apply after caremeet_schema.sql
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE site_leads (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(120) NOT NULL,
  organisation    VARCHAR(190) NULL,
  email           VARCHAR(190) NOT NULL,
  phone           VARCHAR(20)  NULL,
  city            VARCHAR(80)  NULL,
  bed_count       VARCHAR(40)  NULL,
  message         TEXT NULL,
  interest        VARCHAR(60)  NULL,          -- demo | pricing | integration | partner
  -- Attribution. Without these you cannot tell which channel actually
  -- produces hospitals rather than traffic.
  source          VARCHAR(60)  NULL,          -- organic | ai_assistant | referral | direct
  utm_source      VARCHAR(80)  NULL,
  utm_medium      VARCHAR(80)  NULL,
  utm_campaign    VARCHAR(120) NULL,
  referrer        VARCHAR(500) NULL,
  landing_page    VARCHAR(255) NULL,
  ip              VARBINARY(16) NULL,
  user_agent      VARCHAR(255) NULL,
  status          ENUM('new','contacted','qualified','demo_booked','won','lost') NOT NULL DEFAULT 'new',
  notes           TEXT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY ix_lead_status (status, created_at),
  KEY ix_lead_source (source, created_at),
  KEY ix_lead_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Simple rate-limit ledger for the enquiry form. A public POST endpoint on a
-- healthcare site attracts bots within days of going live.
CREATE TABLE site_form_throttle (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ip          VARBINARY(16) NOT NULL,
  submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_throttle (ip, submitted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;