-- =============================================================================
-- Mavi11 Business — Veritabanı Şeması (MariaDB / MySQL)
-- Sürüm: 1.0  |  Tarih: 2026-06-06
-- Motor: MariaDB 10.11 (InnoDB, utf8mb4)
-- Kaynak: docs/mavi11-business-urun-analizi.md (Bölüm 14)
--
-- Notlar (PostgreSQL sürümünden farklar):
--   * ENUM tipleri kolon içinde tanımlı (CREATE TYPE yok).
--   * uuid: CHAR(36) DEFAULT (uuid()); citext yerine utf8mb4_unicode_ci (harf-duyarsız).
--   * JSONB -> JSON; TIMESTAMPTZ -> DATETIME; INET -> VARCHAR(45); TEXT[] -> JSON.
--   * updated_at: ON UPDATE CURRENT_TIMESTAMP (trigger gerekmez).
--   * Kısmi (partial) unique index yok -> sanal (VIRTUAL) generated kolon ile çözüldü.
--   * Trigram GIN yerine FULLTEXT (firma adı arama).
-- =============================================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- =============================================================================
-- 14.1  KİMLİK & YETKİ
-- =============================================================================

CREATE TABLE tenants (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid       CHAR(36) NOT NULL DEFAULT (uuid()),
  name       VARCHAR(255) NOT NULL,
  slug       VARCHAR(191) NOT NULL,
  plan       ENUM('free','starter','pro','enterprise') NOT NULL DEFAULT 'starter',
  status     ENUM('active','suspended','cancelled') NOT NULL DEFAULT 'active',
  settings   JSON DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tenants_uuid (uuid),
  UNIQUE KEY uq_tenants_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code        VARCHAR(150) NOT NULL,
  category    VARCHAR(100) NOT NULL,
  description VARCHAR(255) DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_permissions_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE roles (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED DEFAULT NULL,           -- NULL => sistem (global) rolü
  name        VARCHAR(100) NOT NULL,
  slug        VARCHAR(100) NOT NULL,
  level       SMALLINT NOT NULL DEFAULT 0,
  is_system   BOOLEAN NOT NULL DEFAULT 0,
  description VARCHAR(255) DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_roles_tenant_slug (tenant_id, slug),
  CONSTRAINT fk_roles_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_permissions (
  role_id       BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  KEY idx_rp_permission (permission_id),
  CONSTRAINT fk_rp_role       FOREIGN KEY (role_id)       REFERENCES roles(id)       ON DELETE CASCADE,
  CONSTRAINT fk_rp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
  id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid               CHAR(36) NOT NULL DEFAULT (uuid()),
  tenant_id          BIGINT UNSIGNED NOT NULL,
  role_id            BIGINT UNSIGNED DEFAULT NULL,
  name               VARCHAR(150) NOT NULL,
  email              VARCHAR(255) NOT NULL,            -- utf8mb4_unicode_ci => harf-duyarsız
  password_hash      VARCHAR(255) DEFAULT NULL,
  phone              VARCHAR(30) DEFAULT NULL,
  status             ENUM('invited','active','inactive','suspended') NOT NULL DEFAULT 'invited',
  capacity           INT NOT NULL DEFAULT 50,
  timezone           VARCHAR(64) NOT NULL DEFAULT 'Europe/Istanbul',
  locale             VARCHAR(10) NOT NULL DEFAULT 'tr',
  avatar_url         VARCHAR(500) DEFAULT NULL,
  two_factor_enabled BOOLEAN NOT NULL DEFAULT 0,
  two_factor_secret  VARCHAR(255) DEFAULT NULL,
  email_verified_at  DATETIME DEFAULT NULL,
  last_login_at      DATETIME DEFAULT NULL,
  created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at         DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_uuid (uuid),
  UNIQUE KEY uq_users_tenant_email (tenant_id, email),
  KEY idx_users_tenant_status (tenant_id, status),
  KEY idx_users_role (role_id),
  CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_users_role   FOREIGN KEY (role_id)   REFERENCES roles(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE teams (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  name        VARCHAR(150) NOT NULL,
  manager_id  BIGINT UNSIGNED DEFAULT NULL,
  description VARCHAR(500) DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at  DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_teams_tenant (tenant_id),
  KEY idx_teams_manager (manager_id),
  CONSTRAINT fk_teams_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_teams_manager FOREIGN KEY (manager_id) REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE team_members (
  team_id      BIGINT UNSIGNED NOT NULL,
  user_id      BIGINT UNSIGNED NOT NULL,
  role_in_team VARCHAR(50) DEFAULT NULL,
  joined_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (team_id, user_id),
  KEY idx_tm_user (user_id),
  CONSTRAINT fk_tm_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  CONSTRAINT fk_tm_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sessions (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    BIGINT UNSIGNED NOT NULL,
  token_hash VARCHAR(255) NOT NULL,
  ip_address VARCHAR(45) DEFAULT NULL,
  user_agent VARCHAR(500) DEFAULT NULL,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sessions_token (token_hash),
  KEY idx_sessions_user (user_id),
  KEY idx_sessions_expires (expires_at),
  CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 14.7  ÜRÜN
-- =============================================================================

CREATE TABLE products (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  name        VARCHAR(150) NOT NULL,
  slug        VARCHAR(150) NOT NULL,
  description TEXT DEFAULT NULL,
  icp         JSON DEFAULT NULL,
  price_min   DECIMAL(12,2) DEFAULT NULL,
  price_max   DECIMAL(12,2) DEFAULT NULL,
  currency    CHAR(3) NOT NULL DEFAULT 'TRY',
  status      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  sort_order  INT NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at  DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_products_tenant_slug (tenant_id, slug),
  CONSTRAINT fk_products_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 14.2  LEAD & FİRMA
-- =============================================================================

CREATE TABLE sectors (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED DEFAULT NULL,           -- NULL => global taksonomi
  name        VARCHAR(150) NOT NULL,
  slug        VARCHAR(150) NOT NULL,
  parent_id   BIGINT UNSIGNED DEFAULT NULL,
  description VARCHAR(500) DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sectors_tenant_slug (tenant_id, slug),
  KEY idx_sectors_parent (parent_id),
  CONSTRAINT fk_sectors_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_sectors_parent FOREIGN KEY (parent_id) REFERENCES sectors(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_sector_map (
  product_id BIGINT UNSIGNED NOT NULL,
  sector_id  BIGINT UNSIGNED NOT NULL,
  fit_weight DECIMAL(4,2) NOT NULL DEFAULT 1.00,
  PRIMARY KEY (product_id, sector_id),
  KEY idx_psm_sector (sector_id),
  CONSTRAINT fk_psm_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_psm_sector  FOREIGN KEY (sector_id)  REFERENCES sectors(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tags (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id  BIGINT UNSIGNED NOT NULL,
  name       VARCHAR(100) NOT NULL,
  color      VARCHAR(20) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tags_tenant_name (tenant_id, name),
  CONSTRAINT fk_tags_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE leads (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid             CHAR(36) NOT NULL DEFAULT (uuid()),
  tenant_id        BIGINT UNSIGNED NOT NULL,
  name             VARCHAR(255) NOT NULL,
  sector_id        BIGINT UNSIGNED DEFAULT NULL,
  owner_id         BIGINT UNSIGNED DEFAULT NULL,
  status           ENUM('new','enriched','scored','unassigned','assigned','contacted',
                        'qualified','in_pipeline','won','lost','nurturing') NOT NULL DEFAULT 'new',
  source           VARCHAR(50) DEFAULT NULL,
  website          VARCHAR(255) DEFAULT NULL,
  email            VARCHAR(255) DEFAULT NULL,
  phone            VARCHAR(30) DEFAULT NULL,
  address          VARCHAR(500) DEFAULT NULL,
  city             VARCHAR(100) DEFAULT NULL,
  country          VARCHAR(2) DEFAULT 'TR',
  company_size     VARCHAR(20) DEFAULT NULL,
  employee_count   INT DEFAULT NULL,
  annual_revenue   DECIMAL(14,2) DEFAULT NULL,
  tax_number       VARCHAR(30) DEFAULT NULL,
  description      TEXT DEFAULT NULL,
  social           JSON DEFAULT NULL,
  custom_fields    JSON DEFAULT NULL,
  dedup_hash       VARCHAR(191) DEFAULT NULL,
  last_activity_at DATETIME DEFAULT NULL,
  created_by       BIGINT UNSIGNED DEFAULT NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at       DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_leads_uuid (uuid),
  UNIQUE KEY uq_leads_dedup (tenant_id, dedup_hash),
  KEY idx_leads_tenant_status (tenant_id, status),
  KEY idx_leads_tenant_owner (tenant_id, owner_id),
  KEY idx_leads_tenant_sector (tenant_id, sector_id),
  KEY idx_leads_last_activity (tenant_id, last_activity_at),
  FULLTEXT KEY ft_leads_name (name),
  CONSTRAINT fk_leads_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_leads_sector  FOREIGN KEY (sector_id)  REFERENCES sectors(id) ON DELETE SET NULL,
  CONSTRAINT fk_leads_owner   FOREIGN KEY (owner_id)   REFERENCES users(id)   ON DELETE SET NULL,
  CONSTRAINT fk_leads_creator FOREIGN KEY (created_by) REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_tags (
  lead_id BIGINT UNSIGNED NOT NULL,
  tag_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (lead_id, tag_id),
  KEY idx_lt_tag (tag_id),
  CONSTRAINT fk_lt_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
  CONSTRAINT fk_lt_tag  FOREIGN KEY (tag_id)  REFERENCES tags(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_imports (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id     BIGINT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED DEFAULT NULL,
  file_name     VARCHAR(255) NOT NULL,
  file_url      VARCHAR(500) DEFAULT NULL,
  status        ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
  total_rows    INT NOT NULL DEFAULT 0,
  success_count INT NOT NULL DEFAULT 0,
  failed_count  INT NOT NULL DEFAULT 0,
  mapping       JSON DEFAULT NULL,
  error_log     JSON DEFAULT NULL,
  started_at    DATETIME DEFAULT NULL,
  finished_at   DATETIME DEFAULT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_imports_tenant (tenant_id, status),
  CONSTRAINT fk_imports_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_imports_user   FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 14.3  AI SKORLAMA & ANALİZ
-- =============================================================================

CREATE TABLE scoring_rules (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id  BIGINT UNSIGNED NOT NULL,
  name       VARCHAR(150) NOT NULL,
  criterion  VARCHAR(100) NOT NULL,
  operator   VARCHAR(20) DEFAULT NULL,
  value      JSON DEFAULT NULL,
  weight     DECIMAL(5,2) NOT NULL DEFAULT 1.00,
  threshold  DECIMAL(5,2) DEFAULT NULL,
  is_active  BOOLEAN NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_scoring_tenant (tenant_id, is_active),
  CONSTRAINT fk_scoring_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lead_scores (
  id                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id              BIGINT UNSIGNED NOT NULL,
  lead_id                BIGINT UNSIGNED NOT NULL,
  score                  DECIMAL(5,2) NOT NULL,
  priority               ENUM('hot','warm','cold') NOT NULL,
  recommended_product_id BIGINT UNSIGNED DEFAULT NULL,
  reasoning              TEXT DEFAULT NULL,
  confidence             DECIMAL(4,3) DEFAULT NULL,
  model                  VARCHAR(64) DEFAULT NULL,
  breakdown              JSON DEFAULT NULL,
  is_current             BOOLEAN NOT NULL DEFAULT 1,
  scored_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- Sanal kolon: lead başına yalnızca tek "current" skor (kısmi unique taklidi)
  current_key            BIGINT UNSIGNED AS (IF(is_current = 1, lead_id, NULL)) VIRTUAL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_lead_scores_current (current_key),
  KEY idx_lead_scores_priority (tenant_id, priority, score),
  KEY idx_lead_scores_lead (lead_id),
  CONSTRAINT fk_scores_tenant  FOREIGN KEY (tenant_id)              REFERENCES tenants(id)  ON DELETE CASCADE,
  CONSTRAINT fk_scores_lead    FOREIGN KEY (lead_id)               REFERENCES leads(id)    ON DELETE CASCADE,
  CONSTRAINT fk_scores_product FOREIGN KEY (recommended_product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_analyses (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id     BIGINT UNSIGNED NOT NULL,
  lead_id       BIGINT UNSIGNED NOT NULL,
  type          ENUM('customer_analysis','next_action','summary','objection_handling') NOT NULL,
  summary       TEXT DEFAULT NULL,
  next_action   TEXT DEFAULT NULL,
  raw_output    JSON DEFAULT NULL,
  model         VARCHAR(64) DEFAULT NULL,
  tokens_input  INT DEFAULT NULL,
  tokens_output INT DEFAULT NULL,
  cost          DECIMAL(10,5) DEFAULT NULL,
  created_by    BIGINT UNSIGNED DEFAULT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ai_analyses_lead (lead_id, type),
  CONSTRAINT fk_ai_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_ai_lead    FOREIGN KEY (lead_id)    REFERENCES leads(id)   ON DELETE CASCADE,
  CONSTRAINT fk_ai_creator FOREIGN KEY (created_by) REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_usage_logs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id     BIGINT UNSIGNED DEFAULT NULL,
  user_id       BIGINT UNSIGNED DEFAULT NULL,
  request_type  VARCHAR(50) NOT NULL,
  model         VARCHAR(64) NOT NULL,
  tokens_input  INT NOT NULL DEFAULT 0,
  tokens_output INT NOT NULL DEFAULT 0,
  cost          DECIMAL(10,5) NOT NULL DEFAULT 0,
  status        ENUM('success','failed','rate_limited') NOT NULL DEFAULT 'success',
  latency_ms    INT DEFAULT NULL,
  error         TEXT DEFAULT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ai_usage_tenant_date (tenant_id, created_at),
  CONSTRAINT fk_aiusage_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_aiusage_user   FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 14.4  ATAMA & GÖREV
-- =============================================================================

CREATE TABLE assignment_rules (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id  BIGINT UNSIGNED NOT NULL,
  name       VARCHAR(150) NOT NULL,
  strategy   ENUM('round_robin','sector_based','score_based','geographic','capacity_based','manual') NOT NULL,
  conditions JSON DEFAULT NULL,
  priority   INT NOT NULL DEFAULT 0,
  is_active  BOOLEAN NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_arules_tenant (tenant_id, is_active, priority),
  CONSTRAINT fk_arules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE assignments (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id        BIGINT UNSIGNED NOT NULL,
  lead_id          BIGINT UNSIGNED NOT NULL,
  user_id          BIGINT UNSIGNED DEFAULT NULL,
  assigned_by      BIGINT UNSIGNED DEFAULT NULL,
  rule_id          BIGINT UNSIGNED DEFAULT NULL,
  strategy         ENUM('round_robin','sector_based','score_based','geographic','capacity_based','manual') NOT NULL DEFAULT 'manual',
  status           ENUM('active','reassigned','released') NOT NULL DEFAULT 'active',
  sla_due_at       DATETIME DEFAULT NULL,
  first_contact_at DATETIME DEFAULT NULL,
  assigned_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  released_at      DATETIME DEFAULT NULL,
  -- Sanal kolon: bir lead'in aynı anda yalnızca tek aktif ataması
  active_key       BIGINT UNSIGNED AS (IF(status = 'active', lead_id, NULL)) VIRTUAL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_assignment_active (active_key),
  KEY idx_assignments_user (user_id, status),
  KEY idx_assignments_sla (sla_due_at),
  CONSTRAINT fk_assign_tenant   FOREIGN KEY (tenant_id)   REFERENCES tenants(id)          ON DELETE CASCADE,
  CONSTRAINT fk_assign_lead     FOREIGN KEY (lead_id)     REFERENCES leads(id)            ON DELETE CASCADE,
  CONSTRAINT fk_assign_user     FOREIGN KEY (user_id)     REFERENCES users(id)            ON DELETE SET NULL,
  CONSTRAINT fk_assign_by       FOREIGN KEY (assigned_by) REFERENCES users(id)            ON DELETE SET NULL,
  CONSTRAINT fk_assign_rule     FOREIGN KEY (rule_id)     REFERENCES assignment_rules(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tasks (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id    BIGINT UNSIGNED NOT NULL,
  lead_id      BIGINT UNSIGNED DEFAULT NULL,
  user_id      BIGINT UNSIGNED DEFAULT NULL,
  created_by   BIGINT UNSIGNED DEFAULT NULL,
  type         ENUM('call','email','meeting','follow_up','other') NOT NULL DEFAULT 'other',
  title        VARCHAR(255) NOT NULL,
  description  TEXT DEFAULT NULL,
  priority     ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  due_at       DATETIME DEFAULT NULL,
  status       ENUM('pending','in_progress','done','cancelled') NOT NULL DEFAULT 'pending',
  completed_at DATETIME DEFAULT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tasks_user_status (user_id, status, due_at),
  KEY idx_tasks_lead (lead_id),
  CONSTRAINT fk_tasks_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_tasks_lead    FOREIGN KEY (lead_id)    REFERENCES leads(id)   ON DELETE CASCADE,
  CONSTRAINT fk_tasks_user    FOREIGN KEY (user_id)    REFERENCES users(id)   ON DELETE SET NULL,
  CONSTRAINT fk_tasks_creator FOREIGN KEY (created_by) REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 14.5  CRM / ETKİLEŞİM
-- =============================================================================

CREATE TABLE activities (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id        BIGINT UNSIGNED NOT NULL,
  lead_id          BIGINT UNSIGNED NOT NULL,
  user_id          BIGINT UNSIGNED DEFAULT NULL,
  type             ENUM('call','meeting','email','note','file','system') NOT NULL,
  direction        ENUM('inbound','outbound') DEFAULT NULL,
  subject          VARCHAR(255) DEFAULT NULL,
  content          TEXT DEFAULT NULL,
  outcome          VARCHAR(255) DEFAULT NULL,
  duration_seconds INT DEFAULT NULL,
  next_step        VARCHAR(500) DEFAULT NULL,
  occurred_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_activities_lead (lead_id, occurred_at),
  KEY idx_activities_user (user_id, occurred_at),
  CONSTRAINT fk_act_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_act_lead   FOREIGN KEY (lead_id)   REFERENCES leads(id)   ON DELETE CASCADE,
  CONSTRAINT fk_act_user   FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attachments (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  lead_id     BIGINT UNSIGNED DEFAULT NULL,
  activity_id BIGINT UNSIGNED DEFAULT NULL,
  uploaded_by BIGINT UNSIGNED DEFAULT NULL,
  file_name   VARCHAR(255) NOT NULL,
  file_url    VARCHAR(500) NOT NULL,
  mime_type   VARCHAR(100) DEFAULT NULL,
  size_bytes  BIGINT DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_attachments_lead (lead_id),
  KEY idx_attachments_activity (activity_id),
  CONSTRAINT fk_att_tenant   FOREIGN KEY (tenant_id)   REFERENCES tenants(id)    ON DELETE CASCADE,
  CONSTRAINT fk_att_lead     FOREIGN KEY (lead_id)     REFERENCES leads(id)      ON DELETE CASCADE,
  CONSTRAINT fk_att_activity FOREIGN KEY (activity_id) REFERENCES activities(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_user     FOREIGN KEY (uploaded_by) REFERENCES users(id)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reminders (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id  BIGINT UNSIGNED NOT NULL,
  lead_id    BIGINT UNSIGNED DEFAULT NULL,
  user_id    BIGINT UNSIGNED NOT NULL,
  title      VARCHAR(255) NOT NULL,
  message    VARCHAR(500) DEFAULT NULL,
  remind_at  DATETIME NOT NULL,
  status     ENUM('pending','sent','dismissed') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_reminders_due (status, remind_at),
  KEY idx_reminders_user (user_id),
  CONSTRAINT fk_rem_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_rem_lead   FOREIGN KEY (lead_id)   REFERENCES leads(id)   ON DELETE CASCADE,
  CONSTRAINT fk_rem_user   FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 14.6  PIPELINE / SATIŞ
-- =============================================================================

CREATE TABLE pipeline_stages (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  name        VARCHAR(100) NOT NULL,
  slug        VARCHAR(100) NOT NULL,
  sort_order  INT NOT NULL DEFAULT 0,
  probability DECIMAL(5,2) NOT NULL DEFAULT 0,
  is_won      BOOLEAN NOT NULL DEFAULT 0,
  is_lost     BOOLEAN NOT NULL DEFAULT 0,
  is_active   BOOLEAN NOT NULL DEFAULT 1,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_stages_tenant_slug (tenant_id, slug),
  CONSTRAINT fk_stages_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lost_reasons (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id  BIGINT UNSIGNED NOT NULL,
  name       VARCHAR(150) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active  BOOLEAN NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_lost_tenant (tenant_id),
  CONSTRAINT fk_lost_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE deals (
  id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid                CHAR(36) NOT NULL DEFAULT (uuid()),
  tenant_id           BIGINT UNSIGNED NOT NULL,
  lead_id             BIGINT UNSIGNED NOT NULL,
  product_id          BIGINT UNSIGNED DEFAULT NULL,
  stage_id            BIGINT UNSIGNED DEFAULT NULL,
  owner_id            BIGINT UNSIGNED DEFAULT NULL,
  title               VARCHAR(255) NOT NULL,
  value               DECIMAL(14,2) NOT NULL DEFAULT 0,
  currency            CHAR(3) NOT NULL DEFAULT 'TRY',
  probability         DECIMAL(5,2) DEFAULT NULL,
  expected_close_date DATE DEFAULT NULL,
  status              ENUM('open','won','lost') NOT NULL DEFAULT 'open',
  lost_reason_id      BIGINT UNSIGNED DEFAULT NULL,
  won_at              DATETIME DEFAULT NULL,
  lost_at             DATETIME DEFAULT NULL,
  created_by          BIGINT UNSIGNED DEFAULT NULL,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at          DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_deals_uuid (uuid),
  KEY idx_deals_tenant_stage (tenant_id, stage_id),
  KEY idx_deals_owner (owner_id, status),
  KEY idx_deals_lead (lead_id),
  KEY idx_deals_close (tenant_id, expected_close_date),
  CONSTRAINT fk_deals_tenant  FOREIGN KEY (tenant_id)      REFERENCES tenants(id)         ON DELETE CASCADE,
  CONSTRAINT fk_deals_lead    FOREIGN KEY (lead_id)        REFERENCES leads(id)           ON DELETE CASCADE,
  CONSTRAINT fk_deals_product FOREIGN KEY (product_id)     REFERENCES products(id)        ON DELETE SET NULL,
  CONSTRAINT fk_deals_stage   FOREIGN KEY (stage_id)       REFERENCES pipeline_stages(id) ON DELETE SET NULL,
  CONSTRAINT fk_deals_owner   FOREIGN KEY (owner_id)       REFERENCES users(id)           ON DELETE SET NULL,
  CONSTRAINT fk_deals_lost    FOREIGN KEY (lost_reason_id) REFERENCES lost_reasons(id)    ON DELETE SET NULL,
  CONSTRAINT fk_deals_creator FOREIGN KEY (created_by)     REFERENCES users(id)           ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE deal_stage_history (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id     BIGINT UNSIGNED NOT NULL,
  deal_id       BIGINT UNSIGNED NOT NULL,
  from_stage_id BIGINT UNSIGNED DEFAULT NULL,
  to_stage_id   BIGINT UNSIGNED DEFAULT NULL,
  changed_by    BIGINT UNSIGNED DEFAULT NULL,
  note          VARCHAR(500) DEFAULT NULL,
  changed_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_dsh_deal (deal_id, changed_at),
  CONSTRAINT fk_dsh_tenant FOREIGN KEY (tenant_id)     REFERENCES tenants(id)         ON DELETE CASCADE,
  CONSTRAINT fk_dsh_deal   FOREIGN KEY (deal_id)       REFERENCES deals(id)           ON DELETE CASCADE,
  CONSTRAINT fk_dsh_from   FOREIGN KEY (from_stage_id) REFERENCES pipeline_stages(id) ON DELETE SET NULL,
  CONSTRAINT fk_dsh_to     FOREIGN KEY (to_stage_id)   REFERENCES pipeline_stages(id) ON DELETE SET NULL,
  CONSTRAINT fk_dsh_user   FOREIGN KEY (changed_by)    REFERENCES users(id)           ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 14.8  BİLDİRİM
-- =============================================================================

CREATE TABLE notifications (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED NOT NULL,
  user_id     BIGINT UNSIGNED NOT NULL,
  type        VARCHAR(100) NOT NULL,
  title       VARCHAR(255) NOT NULL,
  body        TEXT DEFAULT NULL,
  channel     ENUM('in_app','email','sms','whatsapp','push') NOT NULL DEFAULT 'in_app',
  priority    ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  entity_type VARCHAR(50) DEFAULT NULL,
  entity_id   BIGINT UNSIGNED DEFAULT NULL,
  data        JSON DEFAULT NULL,
  read_at     DATETIME DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_notif_user_unread (user_id, read_at, created_at),
  CONSTRAINT fk_notif_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_notif_user   FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notification_preferences (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id  BIGINT UNSIGNED NOT NULL,
  user_id    BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(100) NOT NULL,
  channel    ENUM('in_app','email','sms','whatsapp','push') NOT NULL,
  enabled    BOOLEAN NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_notif_pref (user_id, event_type, channel),
  CONSTRAINT fk_np_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_np_user   FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notification_logs (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  notification_id   BIGINT UNSIGNED NOT NULL,
  channel           ENUM('in_app','email','sms','whatsapp','push') NOT NULL,
  status            ENUM('queued','sent','delivered','failed','read') NOT NULL DEFAULT 'queued',
  provider_response JSON DEFAULT NULL,
  sent_at           DATETIME DEFAULT NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_notiflog_notif (notification_id),
  CONSTRAINT fk_nl_notif FOREIGN KEY (notification_id) REFERENCES notifications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- 14.9  RAPORLAMA & SİSTEM
-- =============================================================================

CREATE TABLE audit_logs (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id   BIGINT UNSIGNED DEFAULT NULL,
  user_id     BIGINT UNSIGNED DEFAULT NULL,
  action      VARCHAR(50) NOT NULL,
  entity_type VARCHAR(50) NOT NULL,
  entity_id   BIGINT UNSIGNED DEFAULT NULL,
  changes     JSON DEFAULT NULL,
  ip_address  VARCHAR(45) DEFAULT NULL,
  user_agent  VARCHAR(500) DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_entity (tenant_id, entity_type, entity_id),
  KEY idx_audit_user (user_id, created_at),
  CONSTRAINT fk_audit_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_audit_user   FOREIGN KEY (user_id)   REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_snapshots (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id    BIGINT UNSIGNED NOT NULL,
  type         VARCHAR(100) NOT NULL,
  params       JSON DEFAULT NULL,
  data         JSON DEFAULT NULL,
  generated_by BIGINT UNSIGNED DEFAULT NULL,
  generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_report_type (tenant_id, type, generated_at),
  CONSTRAINT fk_report_tenant FOREIGN KEY (tenant_id)    REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_report_user   FOREIGN KEY (generated_by) REFERENCES users(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id  BIGINT UNSIGNED NOT NULL,
  `key`      VARCHAR(191) NOT NULL,
  value      JSON DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_settings_tenant_key (tenant_id, `key`),
  CONSTRAINT fk_settings_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE webhooks (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id         BIGINT UNSIGNED NOT NULL,
  url               VARCHAR(500) NOT NULL,
  events            JSON DEFAULT NULL,                -- ["lead.created","deal.won"]
  secret            VARCHAR(255) DEFAULT NULL,
  is_active         BOOLEAN NOT NULL DEFAULT 1,
  last_triggered_at DATETIME DEFAULT NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_webhooks_tenant (tenant_id, is_active),
  CONSTRAINT fk_webhooks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE jobs (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id    BIGINT UNSIGNED DEFAULT NULL,
  type         VARCHAR(100) NOT NULL,
  payload      JSON DEFAULT NULL,
  status       ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
  attempts     INT NOT NULL DEFAULT 0,
  max_attempts INT NOT NULL DEFAULT 3,
  available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reserved_at  DATETIME DEFAULT NULL,
  completed_at DATETIME DEFAULT NULL,
  error        TEXT DEFAULT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_jobs_queue (status, available_at),
  CONSTRAINT fk_jobs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;