-- CWPOS Online Store — SQLite schema for the two hot collections
-- (products/stock, transactions) plus the delivery-aware online_orders table.
-- Everything else in CWPOS stays as flat-file JSON.

PRAGMA foreign_keys = ON;

-- ── Products (system of record for catalogue + stock) ───────────────────────
CREATE TABLE IF NOT EXISTS products (
  id             TEXT PRIMARY KEY,
  sku            TEXT,
  name           TEXT NOT NULL,
  brand          TEXT,
  category_id    TEXT,
  price          REAL    NOT NULL DEFAULT 0,
  cost           REAL    NOT NULL DEFAULT 0,
  tax_rate       REAL    NOT NULL DEFAULT 0,
  stock          INTEGER NOT NULL DEFAULT 0,    -- discrete units only (v1)
  track_stock    INTEGER NOT NULL DEFAULT 1,    -- 0 = service / not inventoried
  reorder_point  INTEGER,
  item_type      TEXT    NOT NULL DEFAULT 'product',
  active         INTEGER NOT NULL DEFAULT 1,
  -- web layer (online store)
  publish_online INTEGER NOT NULL DEFAULT 0,
  web_slug       TEXT,
  online_price   REAL,                           -- NULL = use price
  weight_g       INTEGER,                        -- for delivery rating (grams)
  pickup_only    INTEGER NOT NULL DEFAULT 0,     -- bulky items that don't ship
  web_meta       TEXT,                           -- JSON: description, gallery[], dims{}
  -- flexible bits mirrored from the current JSON model
  variants       TEXT,                           -- JSON
  qty_breaks     TEXT,                           -- JSON
  -- full original record, so fields not promoted to columns (case_size,
  -- case_cost, is_bundle, bundle_components, image_url, description, ...)
  -- are never lost. On read the typed columns above override doc.
  doc            TEXT,                            -- JSON (source of truth = columns for promoted fields)
  updated_at     TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_products_slug ON products(web_slug) WHERE web_slug IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_products_sku ON products(sku);
CREATE INDEX IF NOT EXISTS idx_products_pub ON products(publish_online, active);

-- ── Transactions (one stream, both channels) ────────────────────────────────
CREATE TABLE IF NOT EXISTS transactions (
  id           TEXT PRIMARY KEY,
  channel      TEXT NOT NULL DEFAULT 'pos',       -- 'pos' | 'online'
  status       TEXT NOT NULL,                      -- paid | refunded | void
  customer_id  TEXT,
  subtotal     REAL, tax REAL, discount REAL, total REAL,
  currency     TEXT,
  payment      TEXT,                               -- JSON: method, cwpay_ref, gateway
  items        TEXT NOT NULL,                      -- JSON line items
  shift_id     TEXT,
  created_at   TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_txn_created ON transactions(created_at);
CREATE INDEX IF NOT EXISTS idx_txn_channel ON transactions(channel);

-- ── Online orders (fulfilment lifecycle the POS never needed) ───────────────
CREATE TABLE IF NOT EXISTS online_orders (
  id                TEXT PRIMARY KEY,
  customer_id       TEXT,
  status            TEXT NOT NULL DEFAULT 'pending_payment',
  -- pending_payment | paid | picking | ready_for_pickup | collected
  --                 | packed | shipped | delivered | cancelled | refunded
  fulfillment_type  TEXT NOT NULL DEFAULT 'delivery',   -- 'pickup' | 'delivery'
  ship_name         TEXT,
  ship_phone        TEXT,
  ship_address      TEXT,                               -- JSON: line1,line2,city,parish,notes
  items             TEXT NOT NULL,                      -- JSON line items (snapshot)
  subtotal          REAL, tax REAL, shipping REAL, discount REAL, total REAL,
  currency          TEXT,
  cwpay_ref         TEXT,                               -- == pos_txn_ref sent to CWPay
  cwpay_token       TEXT,
  txn_id            TEXT,                               -- links to transactions.id once paid
  created_at        TEXT NOT NULL,
  updated_at        TEXT
);
CREATE INDEX IF NOT EXISTS idx_oo_status  ON online_orders(status);
CREATE INDEX IF NOT EXISTS idx_oo_cwpay   ON online_orders(cwpay_ref);
CREATE INDEX IF NOT EXISTS idx_oo_created ON online_orders(created_at);