-- D1 schema FULL — メール配信システム（新規セットアップ用 統合スキーマ）
-- schema.sql + v2(points) + v3(steps) + v4(sms) + v5(channels) を統合（2026-08-03）。
-- 冪等再実行可（IF NOT EXISTS）。既存DBに実行しても既存テーブルは変更されない。
-- 新規構築はこのファイル1本でOK: npm run db:init / db:init:remote
-- ※ 既存DBへのSMS機能追加は schema_v4_sms.sql（ALTER TABLE）を実行すること。
-- ※ 分割版（schema.sql / schema_v2_points.sql / schema_v3_steps.sql / schema_v4_sms.sql）は履歴として保持。

-- ============ 基本（購読者・リスト） ============

CREATE TABLE IF NOT EXISTS subscribers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  name TEXT,
  status TEXT NOT NULL DEFAULT 'active', -- active | unsubscribed | bounced
  source TEXT,
  unsub_token TEXT NOT NULL UNIQUE,
  created_at INTEGER NOT NULL,
  -- v4: SMS（番号の保有 ≠ 送信同意。特電法のオプトイン原則のため分離管理）
  phone TEXT,                                -- E.164 (+819012345678)
  sms_status TEXT NOT NULL DEFAULT 'none',   -- none | active | unsubscribed
  sms_consent_at INTEGER,                    -- 同意日時（記録保存義務）
  sms_consent_source TEXT                    -- 同意の取得経路
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_sub_phone ON subscribers (phone) WHERE phone IS NOT NULL;

CREATE TABLE IF NOT EXISTS lists (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS list_subscribers (
  list_id INTEGER NOT NULL,
  subscriber_id INTEGER NOT NULL,
  PRIMARY KEY (list_id, subscriber_id)
);

-- ============ 一斉配信 ============

CREATE TABLE IF NOT EXISTS broadcasts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  subject TEXT NOT NULL,                 -- SMS時は管理画面上の表示名として使う
  body_html TEXT NOT NULL,               -- SMS時は空文字
  list_id INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft', -- draft|scheduled|sending|sent|canceled
  scheduled_at INTEGER,
  approved INTEGER NOT NULL DEFAULT 0,
  sent_at INTEGER,
  created_at INTEGER NOT NULL,
  channel TEXT NOT NULL DEFAULT 'email', -- v5: email|sms（単発配信のチャネル）
  body_text TEXT                         -- v5: SMS本文（プレーンテキスト）
);

CREATE TABLE IF NOT EXISTS broadcast_sends (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  broadcast_id INTEGER NOT NULL,
  subscriber_id INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending', -- pending|sent|failed|skipped
  resend_id TEXT,
  error TEXT,
  sent_at INTEGER,
  UNIQUE (broadcast_id, subscriber_id)
);

-- ============ ステップメール ============

CREATE TABLE IF NOT EXISTS step_campaigns (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  list_id INTEGER NOT NULL,
  trigger TEXT NOT NULL DEFAULT 'on_enroll',
  status TEXT NOT NULL DEFAULT 'active', -- active|paused
  created_at INTEGER NOT NULL,
  -- v5: シナリオの主チャネル。新規ステップの既定値と管理画面の振り分けに使う。
  --     ステップ単位の channel は別にあるので、1シナリオ内の混在は引き続き可能。
  channel TEXT NOT NULL DEFAULT 'email'  -- email|sms
);

-- v3: 配信タイミング3方式（immediate|relative|day_time）/ v4: チャネル（email|sms）を統合済み
CREATE TABLE IF NOT EXISTS step_emails (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  campaign_id INTEGER NOT NULL,
  step_order INTEGER NOT NULL,
  delay_minutes INTEGER NOT NULL DEFAULT 0,
  subject TEXT NOT NULL,                        -- SMS時は管理画面上のステップ名として使う
  body_html TEXT NOT NULL,                      -- SMS時は空文字
  timing_type TEXT NOT NULL DEFAULT 'relative', -- immediate|relative|day_time
  delay_days INTEGER NOT NULL DEFAULT 0,        -- day_time: 基準からの日数
  send_time TEXT,                               -- day_time: 'HH:MM'（JST）
  channel TEXT NOT NULL DEFAULT 'email',        -- v4: email|sms
  body_text TEXT,                               -- v4: SMS本文（プレーンテキスト）
  UNIQUE (campaign_id, step_order)
);

CREATE TABLE IF NOT EXISTS step_enrollments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  campaign_id INTEGER NOT NULL,
  subscriber_id INTEGER NOT NULL,
  current_step INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active', -- active|completed|canceled
  next_send_at INTEGER NOT NULL,
  enrolled_at INTEGER NOT NULL,
  UNIQUE (campaign_id, subscriber_id)
);

CREATE TABLE IF NOT EXISTS step_sends (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  enrollment_id INTEGER NOT NULL,
  step_email_id INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'sent', -- sent|failed|skipped
  resend_id TEXT,
  sent_at INTEGER,
  UNIQUE (enrollment_id, step_email_id)
);

-- ============ 外部連携・運用 ============

CREATE TABLE IF NOT EXISTS hooks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  token TEXT NOT NULL UNIQUE,
  list_id INTEGER NOT NULL,
  campaign_id INTEGER,
  name TEXT,
  created_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS audit_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  ts INTEGER NOT NULL,
  action TEXT NOT NULL,
  detail TEXT
);

CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_bc_status ON broadcasts (status, scheduled_at);
CREATE INDEX IF NOT EXISTS idx_enroll_due ON step_enrollments (status, next_send_at);
CREATE INDEX IF NOT EXISTS idx_bcsend_pending ON broadcast_sends (broadcast_id, status);

-- ============ v2: ポイント & 抽選（クリックブースター相当） ============

-- ポイント台帳（増減はすべてこのテーブルに追記。残高 = SUM(points)）
CREATE TABLE IF NOT EXISTS point_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  subscriber_id INTEGER NOT NULL,
  points INTEGER NOT NULL,              -- 正=付与 / 負=消費
  kind TEXT NOT NULL,                   -- open|click|signup|manual|lottery|exchange
  dedupe_key TEXT,                      -- 冪等キー（open:bc:1:sub:2 等）。NULLは重複許可
  note TEXT,
  created_at INTEGER NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_pe_dedupe ON point_events (dedupe_key) WHERE dedupe_key IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_pe_sub ON point_events (subscriber_id, created_at);

-- クリック計測リンク（送信時に本文URLを /t/c/:id/:token へ書き換え）
CREATE TABLE IF NOT EXISTS tracked_links (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  scope TEXT NOT NULL,                  -- bc|step
  ref_id INTEGER NOT NULL,              -- broadcast_id または step_email_id
  url TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  UNIQUE (scope, ref_id, url)
);

-- 抽選会（くじ）
CREATE TABLE IF NOT EXISTS lotteries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  description TEXT,
  cost_points INTEGER NOT NULL DEFAULT 100,  -- くじ1回の消費ポイント
  status TEXT NOT NULL DEFAULT 'open',       -- open|closed
  created_at INTEGER NOT NULL
);

-- 景品（当選確率%・当選本数上限つき。確率合計の残りは「はずれ」）
CREATE TABLE IF NOT EXISTS lottery_prizes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lottery_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  probability REAL NOT NULL,            -- 当選確率（%）
  max_winners INTEGER NOT NULL DEFAULT 0, -- 当選本数上限（0=無制限）
  active INTEGER NOT NULL DEFAULT 1,
  created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_prize_lottery ON lottery_prizes (lottery_id);

-- 抽選履歴
CREATE TABLE IF NOT EXISTS lottery_draws (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lottery_id INTEGER NOT NULL,
  subscriber_id INTEGER NOT NULL,
  prize_id INTEGER,                     -- NULL=はずれ
  points_spent INTEGER NOT NULL,
  fulfilled INTEGER NOT NULL DEFAULT 0, -- 景品の発送/送付が済んだか
  drawn_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_draw_lottery ON lottery_draws (lottery_id, drawn_at);
CREATE INDEX IF NOT EXISTS idx_draw_sub ON lottery_draws (subscriber_id);

-- 交換景品（貯めたポイントと直接交換）
CREATE TABLE IF NOT EXISTS exchange_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  description TEXT,
  cost_points INTEGER NOT NULL,
  stock INTEGER NOT NULL DEFAULT -1,    -- -1=無制限
  active INTEGER NOT NULL DEFAULT 1,
  created_at INTEGER NOT NULL
);

-- 交換申請
CREATE TABLE IF NOT EXISTS exchange_redemptions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  item_id INTEGER NOT NULL,
  subscriber_id INTEGER NOT NULL,
  points_spent INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending', -- pending|done|canceled
  created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_redeem_item ON exchange_redemptions (item_id);
CREATE INDEX IF NOT EXISTS idx_redeem_sub ON exchange_redemptions (subscriber_id);
