-- ============================================================
--  CareBill — WhatsApp consent, session windows and delivery status.
--  Existing database:  mysql carebill < sql/whatsapp.sql
-- ============================================================

-- One row per number we may write to. Consent and opt-out live here, and the
-- opt-out is honoured absolutely: WhatsApp rates a sender on how often people
-- block it, and a poor rating ends messaging altogether.
CREATE TABLE wa_contacts (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  phone           VARCHAR(20) NOT NULL,             -- E.164 without the plus
  client_id       INT NULL,
  contact_id      INT NULL,
  consent         ENUM('unknown','opted_in','opted_out') DEFAULT 'unknown',
  consent_source  VARCHAR(60),
  consent_at      DATETIME NULL,
  last_inbound_at DATETIME NULL,                    -- opens the 24 hour window
  last_outbound_at DATETIME NULL,
  blocked_count   INT DEFAULT 0,
  note            VARCHAR(200),
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_wa_phone (phone),
  INDEX ix_wa_client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE wa_messages (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  phone           VARCHAR(20) NOT NULL,
  direction       ENUM('out','in') DEFAULT 'out',
  wa_message_id   VARCHAR(120) NULL,
  outbox_id       BIGINT NULL,
  client_id       INT NULL,
  invoice_id      INT NULL,
  template_code   VARCHAR(40) NULL,
  body            TEXT,
  status          ENUM('queued','sent','delivered','read','failed','received') DEFAULT 'queued',
  error_code      VARCHAR(20) NULL,
  error_detail    VARCHAR(255) NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_wa_msg (wa_message_id),
  INDEX ix_wam_phone (phone, created_at), INDEX ix_wam_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (k,v) VALUES
 ('wa_verify_token',''), ('wa_app_secret',''), ('wa_hourly_cap','60'),
 ('wa_optout_words','STOP,UNSUBSCRIBE,OPT OUT,OPTOUT,BAND KARO,BAND KARO MSG,ROKO'),
 ('wa_optin_words','START,YES,SUBSCRIBE,HAAN')
ON DUPLICATE KEY UPDATE k=k;