CREATE DATABASE IF NOT EXISTS jantri_db;
USE jantri_db;

-- Each bet belongs to exactly ONE type:
--   jodi  -- a two-digit number, written as "45"
--   ank   -- a single digit, written as a triple: "111" = ank 1, "777" = ank 7
--   dahai -- a single digit, written as a quad or in brackets: "2222" or "(2)" = dahai 2
-- jodi / ank_digit / dahai_digit are mutually exclusive, so a jodi bet never
-- contributes to the ank or dahai totals.
CREATE TABLE IF NOT EXISTS entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sender VARCHAR(255) DEFAULT 'Manual/System',
  raw_number VARCHAR(50) NOT NULL,
  bet_type ENUM('jodi','ank','dahai') NOT NULL,
  jodi CHAR(2) NULL,
  ank_digit CHAR(1) NULL,
  dahai_digit CHAR(1) NULL,
  amount DECIMAL(12, 2) NOT NULL,
  entry_date DATE NOT NULL,
  raw_message TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_date (entry_date),
  INDEX idx_type (bet_type),
  INDEX idx_jodi (jodi),
  INDEX idx_ank (ank_digit),
  INDEX idx_dahai (dahai_digit)
);

-- Idempotency ledger: WhatsApp redelivers webhooks, and a redelivery would
-- otherwise re-insert the same bets and double-count the money.
CREATE TABLE IF NOT EXISTS processed_messages (
  wa_message_id VARCHAR(128) PRIMARY KEY,
  processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);