-- =====================================================================
-- Migration 002: conversational commerce upgrade
-- ADDITIVE ONLY. Safe to run more than once (IF NOT EXISTS everywhere).
-- Column additions to existing tables live in 002_columns.php (guarded
-- through information_schema because MySQL 5.7 lacks ADD COLUMN IF NOT EXISTS).
-- Rollback guidance: every object created here is new; to roll back, DROP the
-- new tables listed in database/migrations/README.txt. Nothing existing is altered
-- destructively.
-- =====================================================================
SET NAMES utf8mb4;

-- ---- Conversation state: the draft order / flow step the AI is in -------------
CREATE TABLE IF NOT EXISTS conversation_state (
  conversation_id INT UNSIGNED NOT NULL PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  flow ENUM('idle','discovery','checkout','booking','tracking') NOT NULL DEFAULT 'idle',
  step VARCHAR(40) NULL,
  draft JSON NULL,                       -- selected product/variant/qty/name/phone/address/payment
  last_products JSON NULL,               -- ids shown most recently (for "eta", "first one")
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cs_ws (workspace_id),
  CONSTRAINT fk_cs_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Rich message payload (cards, quick replies, buttons) stored with messages --
CREATE TABLE IF NOT EXISTS message_payloads (
  message_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  payload JSON NOT NULL,                 -- {cards:[],quick_replies:[],buttons:[]}
  CONSTRAINT fk_mp_msg FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Product views (for "most viewed", recently viewed) -----------------------
CREATE TABLE IF NOT EXISTS product_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NULL,
  event ENUM('shown','viewed','inquired','ordered') NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_pe (workspace_id, event, created_at),
  KEY idx_pe_prod (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Checkout sessions (link checkout, expiring, tamper-proof) ----------------
CREATE TABLE IF NOT EXISTS checkout_sessions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  token CHAR(48) NOT NULL UNIQUE,        -- unguessable public token
  customer_id INT UNSIGNED NULL,
  conversation_id INT UNSIGNED NULL,
  items JSON NOT NULL,                   -- [{product_id,variant_id,qty}] ids only; prices re-read server-side
  status ENUM('open','completed','expired') NOT NULL DEFAULT 'open',
  order_id INT UNSIGNED NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_chk_ws (workspace_id, status),
  CONSTRAINT fk_chk_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Order history ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS order_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  order_id INT UNSIGNED NOT NULL,
  event VARCHAR(40) NOT NULL,            -- created, status:<x>, shipment:<x>, receipt_sent, payment:<x>
  detail VARCHAR(255) NULL,
  actor ENUM('customer','ai','staff','system','courier') NOT NULL DEFAULT 'system',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_oe (order_id, created_at),
  CONSTRAINT fk_oe_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Workspace commerce settings (delivery, payment methods, handover, credits) --
CREATE TABLE IF NOT EXISTS workspace_settings (
  workspace_id INT UNSIGNED NOT NULL PRIMARY KEY,
  delivery_charge_inside DECIMAL(10,2) NOT NULL DEFAULT 60,
  delivery_charge_outside DECIMAL(10,2) NOT NULL DEFAULT 120,
  free_delivery_over DECIMAL(12,2) NULL,
  inside_keywords VARCHAR(255) NOT NULL DEFAULT 'dhaka,ঢাকা',
  payment_methods JSON NULL,             -- [{"code":"cod","label":"Cash on Delivery","instructions":""},...]
  receipt_footer VARCHAR(255) NULL,
  logo_path VARCHAR(255) NULL,
  response_length ENUM('short','medium','detailed') NOT NULL DEFAULT 'short',
  sales_style ENUM('soft','balanced','proactive') NOT NULL DEFAULT 'balanced',
  takeover_minutes INT UNSIGNED NOT NULL DEFAULT 0,       -- 0 = never auto-resume
  credit_policy ENUM('stop_ai','handover') NOT NULL DEFAULT 'handover',
  restricted_topics TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_wss_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Billing: plans, subscriptions, credit wallet (immutable ledger) ------------
CREATE TABLE IF NOT EXISTS plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  description VARCHAR(255) NULL,
  price_monthly DECIMAL(12,2) NOT NULL DEFAULT 0,
  price_annual DECIMAL(12,2) NOT NULL DEFAULT 0,
  included_credits INT UNSIGNED NOT NULL DEFAULT 0,
  limits JSON NULL,                      -- {channels,staff,products,conversations}
  features JSON NULL,                    -- {voice:true,image_match:true,booking:true,...}
  is_public TINYINT(1) NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  archived_at DATETIME NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NOT NULL,
  status ENUM('trial','active','past_due','cancelled','expired') NOT NULL DEFAULT 'active',
  billing_cycle ENUM('monthly','annual') NOT NULL DEFAULT 'monthly',
  current_period_start DATETIME NOT NULL,
  current_period_end DATETIME NOT NULL,
  cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sub_ws (workspace_id, status),
  CONSTRAINT fk_sub_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_wallets (
  workspace_id INT UNSIGNED NOT NULL PRIMARY KEY,
  balance INT NOT NULL DEFAULT 0,        -- cached; ledger is the source of truth
  low_balance_notified_at DATETIME NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_cw_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT chk_cw_nonneg CHECK (balance >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_ledger (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  delta INT NOT NULL,                    -- + grant/purchase, - usage. Rows are never updated or deleted.
  balance_after INT NOT NULL,
  kind ENUM('plan_grant','purchase','usage','admin_adjust','refund') NOT NULL,
  reference VARCHAR(80) NULL,            -- e.g. ai_usage_log id, payment id
  idempotency_key VARCHAR(100) NULL,
  note VARCHAR(255) NULL,
  actor_user_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ledger_idem (workspace_id, idempotency_key),
  KEY idx_ledger_ws (workspace_id, created_at),
  CONSTRAINT fk_cl_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  number VARCHAR(30) NOT NULL UNIQUE,
  kind ENUM('subscription','credits') NOT NULL,
  description VARCHAR(255) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status ENUM('pending','paid','failed','void') NOT NULL DEFAULT 'pending',
  payment_method VARCHAR(40) NULL,
  payment_reference VARCHAR(120) NULL,
  paid_at DATETIME NULL,
  meta JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_inv_ws (workspace_id, created_at),
  CONSTRAINT fk_inv2_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Training: corrections + config history + knowledge indexing -----------------
CREATE TABLE IF NOT EXISTS ai_corrections (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  message_id BIGINT UNSIGNED NULL,
  customer_question TEXT NULL,
  bad_answer TEXT NULL,
  correct_answer TEXT NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_corr (workspace_id, enabled),
  CONSTRAINT fk_corr_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS agent_config_versions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  snapshot JSON NOT NULL,
  saved_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_acv (workspace_id, created_at),
  CONSTRAINT fk_acv_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Leads + follow-ups ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS lead_signals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  signal VARCHAR(40) NOT NULL,           -- asked_price, viewed_product, asked_delivery, started_checkout, gave_contact, abandoned_checkout, purchased
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ls (workspace_id, customer_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS followup_rules (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  lead_temperature ENUM('hot','warm','cold','any') NOT NULL DEFAULT 'warm',
  trigger_signal VARCHAR(40) NULL,       -- e.g. abandoned_checkout
  delay_minutes INT UNSIGNED NOT NULL DEFAULT 120,
  max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 2,
  message_template TEXT NOT NULL,
  business_hours_only TINYINT(1) NOT NULL DEFAULT 1,
  hours_start TIME NOT NULL DEFAULT '09:00:00',
  hours_end TIME NOT NULL DEFAULT '21:00:00',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_fr_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS followup_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  rule_id INT UNSIGNED NULL,
  customer_id INT UNSIGNED NOT NULL,
  conversation_id INT UNSIGNED NULL,
  channel VARCHAR(32) NULL,
  status ENUM('sent','skipped','failed','replied','converted') NOT NULL,
  reason VARCHAR(160) NULL,
  attempt TINYINT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_fl (workspace_id, customer_id, created_at),
  KEY idx_fl_rule (rule_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Bookings ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS booking_services (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  duration_minutes INT UNSIGNED NOT NULL DEFAULT 30,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  advance_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  questions JSON NULL,                   -- extra fields to collect
  schedule JSON NULL,                    -- {"days":[0..6],"start":"10:00","end":"18:00"}
  cancellation_policy VARCHAR(255) NULL,
  reminder_hours INT UNSIGNED NOT NULL DEFAULT 24,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bs (workspace_id, active),
  CONSTRAINT fk_bs_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bookings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  service_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NULL,
  conversation_id INT UNSIGNED NULL,
  reference VARCHAR(24) NOT NULL UNIQUE,
  customer_name VARCHAR(150) NOT NULL,
  phone VARCHAR(32) NOT NULL,
  starts_at DATETIME NOT NULL,
  answers JSON NULL,
  status ENUM('pending','confirmed','rejected','cancelled','completed') NOT NULL DEFAULT 'pending',
  reminder_sent_at DATETIME NULL,
  idempotency_key VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_booking_idem (workspace_id, idempotency_key),
  KEY idx_bk (workspace_id, status, starts_at),
  CONSTRAINT fk_bk_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_bk_service FOREIGN KEY (service_id) REFERENCES booking_services(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Broadcast campaigns -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS campaigns (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  channel ENUM('email','messenger','whatsapp','instagram','web') NOT NULL DEFAULT 'email',
  message TEXT NOT NULL,
  audience JSON NULL,                    -- {"temperature":"hot","tag":"vip","ordered":true}
  template_name VARCHAR(120) NULL,       -- approved WhatsApp template when outside 24h window
  status ENUM('draft','scheduled','sending','done','cancelled') NOT NULL DEFAULT 'draft',
  scheduled_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_camp (workspace_id, status),
  CONSTRAINT fk_camp_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaign_recipients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT UNSIGNED NOT NULL,
  workspace_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  status ENUM('queued','sent','skipped','failed') NOT NULL DEFAULT 'queued',
  reason VARCHAR(160) NULL,
  sent_at DATETIME NULL,
  UNIQUE KEY uq_cr (campaign_id, customer_id),
  CONSTRAINT fk_cr_camp FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Messaging consent --------------------------------------------------------------
CREATE TABLE IF NOT EXISTS customer_consent (
  customer_id INT UNSIGNED NOT NULL,
  workspace_id INT UNSIGNED NOT NULL,
  channel VARCHAR(32) NOT NULL,
  marketing_opt_out TINYINT(1) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (customer_id, channel),
  CONSTRAINT fk_cc2_cust FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---- Fixed-window rate limiter (public endpoints; no Redis on shared hosting) ------
CREATE TABLE IF NOT EXISTS rate_limits (
  bucket_key CHAR(40) NOT NULL PRIMARY KEY,
  hits INT UNSIGNED NOT NULL DEFAULT 0,
  window_start DATETIME NOT NULL,
  KEY idx_rl_window (window_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO schema_migrations (version) VALUES ('002_commerce_upgrade');