-- ─── Rasid SQLite Schema ─────────────────────────────────────────────────────
-- Automatically applied by db/database.js on first run
-- ─────────────────────────────────────────────────────────────────────────────
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

-- ── Users (merchants) ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  email         TEXT    NOT NULL UNIQUE,
  password_hash TEXT    NOT NULL,
  name          TEXT    NOT NULL,
  business_name TEXT    NOT NULL,
  phone         TEXT,
  city          TEXT,
  sector        TEXT,
  plan          TEXT    NOT NULL DEFAULT 'starter',  -- starter | growth | scale
  plan_expires  TEXT,                                -- ISO date or NULL = forever
  approved      INTEGER NOT NULL DEFAULT 1,          -- 1=active, 0=pending
  created_at    TEXT    NOT NULL DEFAULT (datetime('now')),
  last_login    TEXT
);

-- ── Shops (one user can have multiple shops on Growth+) ──────────────────────
CREATE TABLE IF NOT EXISTS shops (
  id                INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id           INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name              TEXT    NOT NULL,
  sector            TEXT,
  city              TEXT,
  phone             TEXT,
  address           TEXT,
  logo_url          TEXT,
  points_per_visit  INTEGER NOT NULL DEFAULT 10,
  stamp_goal        INTEGER NOT NULL DEFAULT 10,
  reward            TEXT    NOT NULL DEFAULT '1 café offert',
  welcome_points    INTEGER NOT NULL DEFAULT 50,
  expiry_days       INTEGER NOT NULL DEFAULT 365,
  latitude          REAL,
  longitude         REAL,
  proximity_message TEXT DEFAULT 'Vous êtes à proximité ! Venez cumuler vos points.',
  geofence_radius   INTEGER NOT NULL DEFAULT 100,
  active            INTEGER NOT NULL DEFAULT 1,
  created_at        TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ── Customers ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS customers (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  shop_id     INTEGER NOT NULL REFERENCES shops(id) ON DELETE CASCADE,
  phone       TEXT    NOT NULL,
  name        TEXT,
  email       TEXT,
  points      INTEGER NOT NULL DEFAULT 0,
  tier        TEXT    NOT NULL DEFAULT 'new', -- new | bronze | silver | gold
  notes       TEXT,
  created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
  last_visit  TEXT,
  UNIQUE(shop_id, phone)
);

-- ── Points transactions ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS point_transactions (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  shop_id     INTEGER NOT NULL REFERENCES shops(id) ON DELETE CASCADE,
  delta       INTEGER NOT NULL,  -- positive = earn, negative = redeem
  reason      TEXT,              -- 'visit' | 'stamp_reward' | 'campaign' | 'manual'
  created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ── Stamp programs ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS stamp_programs (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  shop_id      INTEGER NOT NULL REFERENCES shops(id) ON DELETE CASCADE,
  name         TEXT    NOT NULL,
  description  TEXT,
  emoji        TEXT    NOT NULL DEFAULT '☕',
  total_stamps INTEGER NOT NULL DEFAULT 10,
  reward       TEXT    NOT NULL DEFAULT 'Café offert',
  validity_days INTEGER NOT NULL DEFAULT 90,
  active       INTEGER NOT NULL DEFAULT 1,
  created_at   TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ── Stamp cards (one per customer per program) ────────────────────────────────
CREATE TABLE IF NOT EXISTS stamp_cards (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  program_id  INTEGER NOT NULL REFERENCES stamp_programs(id) ON DELETE CASCADE,
  stamps_used INTEGER NOT NULL DEFAULT 0,
  completed   INTEGER NOT NULL DEFAULT 0,
  started_at  TEXT    NOT NULL DEFAULT (datetime('now')),
  expires_at  TEXT,
  UNIQUE(customer_id, program_id)
);

-- ── Stamp transactions ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS stamp_transactions (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  card_id    INTEGER NOT NULL REFERENCES stamp_cards(id) ON DELETE CASCADE,
  stamped_at TEXT    NOT NULL DEFAULT (datetime('now')),
  note       TEXT
);

-- ── Loyalty cards (wallet) ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS loyalty_cards (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  shop_id     INTEGER NOT NULL REFERENCES shops(id) ON DELETE CASCADE,
  customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
  name        TEXT    NOT NULL,
  type        TEXT    NOT NULL DEFAULT 'points',  -- points | wallet | vip
  theme       TEXT    NOT NULL DEFAULT 'theme-orange',
  number      TEXT    NOT NULL,  -- e.g. RSID-8421
  active      INTEGER NOT NULL DEFAULT 1,
  created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ── Campaigns ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS campaigns (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  shop_id        INTEGER NOT NULL REFERENCES shops(id) ON DELETE CASCADE,
  name           TEXT    NOT NULL,
  type           TEXT    NOT NULL DEFAULT 'sms',  -- sms | double_points | flash | birthday
  description    TEXT,
  target_segment TEXT    NOT NULL DEFAULT 'all',  -- all | inactive | vip | new
  message        TEXT,
  bonus_points   INTEGER,  -- for double_points type
  start_date     TEXT,
  end_date       TEXT,
  status         TEXT    NOT NULL DEFAULT 'draft',  -- draft | active | paused | completed
  sent_count     INTEGER NOT NULL DEFAULT 0,
  open_count     INTEGER NOT NULL DEFAULT 0,
  redemption_count INTEGER NOT NULL DEFAULT 0,
  created_at     TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ── Devis / Leads (before account creation) ───────────────────────────────────
CREATE TABLE IF NOT EXISTS leads (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  business_name TEXT    NOT NULL,
  name          TEXT    NOT NULL,
  email         TEXT    NOT NULL,
  phone         TEXT,
  city          TEXT,
  sector        TEXT,
  plan          TEXT,
  message       TEXT,
  status        TEXT    NOT NULL DEFAULT 'pending',  -- pending | contacted | converted | lost
  created_at    TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ── Indexes ───────────────────────────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_customers_shop ON customers(shop_id);
CREATE INDEX IF NOT EXISTS idx_customers_phone ON customers(phone);
CREATE INDEX IF NOT EXISTS idx_point_tx_customer ON point_transactions(customer_id);
CREATE INDEX IF NOT EXISTS idx_stamp_cards_customer ON stamp_cards(customer_id);
CREATE INDEX IF NOT EXISTS idx_campaigns_shop ON campaigns(shop_id);