-- =====================================================================
-- AI Commerce Agent Platform — MySQL schema (5.7+ / MariaDB 10.2+)
-- Import into a clean database. All timestamps are UTC DATETIME.
-- Monetary values use DECIMAL(12,2). Engine InnoDB, charset utf8mb4.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------- users
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  is_platform_admin TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('active','suspended') NOT NULL DEFAULT 'active',
  phone VARCHAR(32) NULL,
  timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Dhaka',
  locale VARCHAR(8) NOT NULL DEFAULT 'en',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL,
  ip VARCHAR(45) NOT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_email_time (email, attempted_at),
  KEY idx_ip_time (ip, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS password_resets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_reset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------------------------------------- workspaces
CREATE TABLE IF NOT EXISTS workspaces (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(150) NOT NULL UNIQUE,
  business_type VARCHAR(80) NULL,
  status ENUM('active','suspended') NOT NULL DEFAULT 'active',
  suspended_reason VARCHAR(255) NULL,
  widget_key CHAR(32) NOT NULL UNIQUE,          -- public widget identifier (not a secret)
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_ws_owner FOREIGN KEY (owner_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workspace_members (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  role ENUM('owner','admin','support') NOT NULL DEFAULT 'support',
  status ENUM('active','invited','removed') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ws_user (workspace_id, user_id),
  CONSTRAINT fk_wm_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_wm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS business_profiles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL UNIQUE,
  brand_name VARCHAR(150) NULL,
  description TEXT NULL,
  website VARCHAR(190) NULL,
  country VARCHAR(2) NOT NULL DEFAULT 'BD',
  timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Dhaka',
  default_language ENUM('bn','en','auto') NOT NULL DEFAULT 'auto',
  contact_email VARCHAR(190) NULL,
  contact_phone VARCHAR(32) NULL,
  address VARCHAR(255) NULL,
  onboarding_step TINYINT UNSIGNED NOT NULL DEFAULT 1,  -- 1..7 (0 = complete)
  onboarding_completed_at DATETIME NULL,
  UNIQUE KEY uq_bp_ws (workspace_id),
  CONSTRAINT fk_bp_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_settings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL UNIQUE,
  agent_name VARCHAR(120) NOT NULL DEFAULT 'Assistant',
  avatar_path VARCHAR(255) NULL,
  welcome_message TEXT NULL,
  language ENUM('bn','en','auto') NOT NULL DEFAULT 'auto',
  tone VARCHAR(60) NULL,                          -- friendly / formal / casual
  instructions TEXT NULL,                         -- business-specific system prompt
  handover_rules TEXT NULL,                       -- when to hand to a human
  ai_enabled TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_ag_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 knowledge_documents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  kind ENUM('policy','faq','hours','delivery','payment','returns','other') NOT NULL DEFAULT 'other',
  content MEDIUMTEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_kd_ws (workspace_id),
  CONSTRAINT fk_kd_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ channels
CREATE TABLE IF NOT EXISTS channels (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  type ENUM('messenger','whatsapp','instagram','web','fb_comments','ig_comments') NOT NULL,
  external_id VARCHAR(190) NULL,                 -- page id / phone number id / ig user id
  name VARCHAR(190) NULL,
  status ENUM('connected','disconnected','error') NOT NULL DEFAULT 'disconnected',
  connected_at DATETIME NULL,
  last_error TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ch (workspace_id, type, external_id),
  KEY idx_ch_ws (workspace_id),
  CONSTRAINT fk_ch_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 channel_tokens (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  channel_id INT UNSIGNED NOT NULL UNIQUE,
  workspace_id INT UNSIGNED NOT NULL,
  access_token_enc TEXT NULL,                   -- encrypted at rest
  refresh_token_enc TEXT NULL,
  token_expires_at DATETIME NULL,
  health ENUM('ok','expiring','invalid','unknown') NOT NULL DEFAULT 'unknown',
  last_checked_at DATETIME NULL,
  extra JSON NULL,                              -- provider-specific (waba id, phone, verify token …)
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_ct_ch FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE,
  CONSTRAINT fk_ct_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ customers
CREATE TABLE IF NOT EXISTS customers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NULL,
  phone VARCHAR(32) NULL,
  email VARCHAR(190) NULL,
  channel VARCHAR(32) NULL,
  external_ref VARCHAR(190) NOT NULL,            -- provider-scoped id (psid, wa id, widget visitor id)
  avatar_url VARCHAR(500) NULL,
  email_opt_out TINYINT(1) NOT NULL DEFAULT 0,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cust (workspace_id, external_ref),
  KEY idx_cust_ws (workspace_id),
  CONSTRAINT fk_cu_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 conversations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  channel VARCHAR(32) NOT NULL,
  channel_id INT UNSIGNED NULL,
  status ENUM('open','pending','closed') NOT NULL DEFAULT 'open',
  assigned_to INT UNSIGNED NULL,
  ai_paused TINYINT(1) NOT NULL DEFAULT 0,       -- human takeover mode
  unread_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_message_preview VARCHAR(255) NULL,
  last_message_at DATETIME NULL,
  first_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_conv_ws (workspace_id, last_message_at),
  KEY idx_conv_unread (workspace_id, unread_count),
  CONSTRAINT fk_conv_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_conv_cust FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  conversation_id INT UNSIGNED NOT NULL,
  direction ENUM('in','out') NOT NULL,
  sender ENUM('customer','ai','human','system') NOT NULL,
  body MEDIUMTEXT NULL,
  attachment_path VARCHAR(500) NULL,             -- relative to storage/uploads/ws_{id}/
  attachment_kind ENUM('image','audio','file') NULL,
  external_id VARCHAR(190) NULL,                 -- provider message id (idempotency)
  delivery_status ENUM('received','sent','delivered','read','failed') NOT NULL DEFAULT 'sent',
  failure_reason VARCHAR(500) NULL,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_msg_ext (workspace_id, external_id),
  KEY idx_msg_conv (conversation_id, created_at),
  CONSTRAINT fk_msg_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_msg_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS internal_notes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  conversation_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_note_conv (conversation_id),
  CONSTRAINT fk_note_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ catalog
CREATE TABLE IF NOT EXISTS products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  sku VARCHAR(80) NOT NULL,
  description TEXT NULL,
  category VARCHAR(80) NULL,
  tags VARCHAR(255) NULL,
  regular_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  offer_price DECIMAL(12,2) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  stock_threshold INT UNSIGNED NOT NULL DEFAULT 5,
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_prod_sku (workspace_id, sku),
  KEY idx_prod_ws (workspace_id, status, category),
  CONSTRAINT fk_prod_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 product_variants (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,                   -- e.g. "M / Black"
  attributes JSON NULL,                          -- {"size":"M","color":"Black"}
  price_delta DECIMAL(12,2) NOT NULL DEFAULT 0,
  stock INT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_var (product_id, name),
  CONSTRAINT fk_var_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_var_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_images (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  path VARCHAR(500) NOT NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_pimg (product_id),
  CONSTRAINT fk_pimg_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_pimg_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inventory_movements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  variant_id INT UNSIGNED NULL,
  delta INT NOT NULL,                            -- +restock / -sale
  reason ENUM('sale','restock','adjustment','cancel') NOT NULL,
  order_id INT UNSIGNED NULL,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_inv_ws (workspace_id, product_id, created_at),
  CONSTRAINT fk_inv_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_inv_prod FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ orders
CREATE TABLE IF NOT EXISTS orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NULL,
  conversation_id INT UNSIGNED NULL,
  reference VARCHAR(24) NOT NULL UNIQUE,        -- e.g. ORD-20261019-0001
  status ENUM('pending','confirmed','processing','ready_to_ship','shipped','in_transit','delivered','cancelled','returned') NOT NULL DEFAULT 'pending',
  customer_name VARCHAR(150) NOT NULL,
  phone VARCHAR(32) NOT NULL,
  address TEXT NOT NULL,
  delivery_method ENUM('courier','pickup') NOT NULL DEFAULT 'courier',
  payment_method VARCHAR(60) NULL,               -- cod / bKash / bank etc.
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount DECIMAL(12,2) NOT NULL DEFAULT 0,
  delivery_charge DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  source_channel VARCHAR(32) NULL,
  idempotency_key VARCHAR(64) NULL,
  confirmed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_order_idem (workspace_id, idempotency_key),
  KEY idx_order_ws (workspace_id, status, created_at),
  CONSTRAINT fk_ord_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ord_cust FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_ord_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  order_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  variant_id INT UNSIGNED NULL,
  product_name VARCHAR(190) NOT NULL,           -- frozen at purchase time
  variant_name VARCHAR(120) NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  line_total DECIMAL(12,2) NOT NULL,
  KEY idx_oi_order (order_id),
  CONSTRAINT fk_oi_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ courier
CREATE TABLE IF NOT EXISTS courier_shipments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  order_id INT UNSIGNED NOT NULL,
  provider ENUM('pathao','steadfast','redx') NOT NULL,
  consignment_id VARCHAR(120) NULL,
  status VARCHAR(64) NOT NULL DEFAULT 'pending',
  last_status_at DATETIME NULL,
  request_payload JSON NULL,
  response_payload JSON NULL,
  error_message TEXT NULL,
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ship (order_id, provider),
  KEY idx_ship_ws (workspace_id),
  CONSTRAINT fk_ship_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_ship_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS courier_credentials (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  provider ENUM('pathao','steadfast','redx') NOT NULL,
  credentials_enc TEXT NOT NULL,                -- encrypted merchant credentials
  status ENUM('unverified','verified','invalid') NOT NULL DEFAULT 'unverified',
  last_error TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_creds (workspace_id, provider),
  CONSTRAINT fk_cc_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ email / automation
CREATE TABLE IF NOT EXISTS email_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  to_email VARCHAR(190) NOT NULL,
  template VARCHAR(64) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  body MEDIUMTEXT NOT NULL,
  status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  error_message VARCHAR(500) NULL,
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  sent_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_email_ws (workspace_id, status),
  CONSTRAINT fk_el_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 automation_rules (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  event ENUM('order_confirmed','order_pending_followup','order_delivered','ticket_updated','staff_notify') NOT NULL,
  conditions JSON NULL,
  action VARCHAR(64) NOT NULL,                  -- send_email / followup
  delay_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  max_repeats TINYINT UNSIGNED NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_rule (workspace_id, name),
  CONSTRAINT fk_rule_ws FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ queue
CREATE TABLE IF NOT EXISTS jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  queue VARCHAR(32) NOT NULL DEFAULT 'default',
  workspace_id INT UNSIGNED NULL,
  handler VARCHAR(120) NOT NULL,
  payload JSON NOT NULL,
  status ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  max_attempts TINYINT UNSIGNED NOT NULL DEFAULT 3,
  available_at DATETIME NOT NULL,
  reserved_at DATETIME NULL,
  reserved_by VARCHAR(64) NULL,
  last_error VARCHAR(500) NULL,
  dedupe_key VARCHAR(120) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_job_dedupe (dedupe_key),
  KEY idx_job_pick (status, available_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ platform admin
CREATE TABLE IF NOT EXISTS ai_providers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  kind ENUM('openai_compatible','other') NOT NULL DEFAULT 'openai_compatible',
  base_url VARCHAR(255) NOT NULL,               -- e.g. https://api.openai.com/v1
  api_key_enc TEXT NOT NULL,
  default_model VARCHAR(120) NOT NULL,
  vision_model VARCHAR(120) NULL,               -- model verified to accept image inputs
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  fallback_provider_id INT UNSIGNED NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  last_health_at DATETIME NULL,
  last_health_ok TINYINT(1) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS platform_settings (
  setting_key VARCHAR(80) PRIMARY KEY,
  setting_value TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------ logs / webhook dedup
CREATE TABLE IF NOT EXISTS ai_usage_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NULL,
  provider_id INT UNSIGNED NULL,
  model VARCHAR(120) NOT NULL,
  task VARCHAR(64) NOT NULL,                     -- reply / product_match / transcribe
  prompt_tokens INT UNSIGNED NULL,
  completion_tokens INT UNSIGNED NULL,
  latency_ms INT UNSIGNED NULL,
  status ENUM('ok','error','rate_limited','timeout') NOT NULL DEFAULT 'ok',
  error_message VARCHAR(500) NULL,
  conversation_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ai_ws (workspace_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NULL,
  user_id INT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  details JSON NULL,
  ip VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_act_ws (workspace_id, created_at),
  KEY idx_act_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS webhook_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(32) NOT NULL,
  event_id VARCHAR(190) NOT NULL UNIQUE,
  payload_hash CHAR(64) NOT NULL,
  processed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_we_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS social_comments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  channel_id INT UNSIGNED NULL,
  platform ENUM('facebook','instagram') NOT NULL,
  external_id VARCHAR(190) NOT NULL,
  parent_external_id VARCHAR(190) NULL,
  customer_id INT UNSIGNED NULL,
  post_id VARCHAR(190) NULL,
  body TEXT NULL,
  private_reply_status ENUM('none','sent','unsupported','failed') NOT NULL DEFAULT 'none',
  status ENUM('open','handled','ignored') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_comment (workspace_id, external_id),
  CONSTRAINT fk_sc_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 notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL,
  kind VARCHAR(64) NOT NULL,
  title VARCHAR(190) NOT NULL,
  body VARCHAR(500) NULL,
  link VARCHAR(255) NULL,
  read_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_notif (workspace_id, read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS schema_migrations (
  version VARCHAR(32) PRIMARY KEY,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO schema_migrations (version) VALUES ('001_initial');

SET FOREIGN_KEY_CHECKS = 1;