-- =============================================================================
-- Mavi11 Business — Veritabanı Şeması (PostgreSQL)
-- Sürüm: 1.0  |  Tarih: 2026-06-06
-- Kaynak: docs/mavi11-business-urun-analizi.md (Bölüm 14)
-- Motor: PostgreSQL 14+
--
-- Konvansiyonlar:
--   * Çok kiracılı (multi-tenant): işlemsel tablolar tenant_id taşır (row-level izolasyon).
--   * Soft-delete: deleted_at IS NULL = aktif kayıt.
--   * Zaman damgaları: created_at / updated_at her tabloda; updated_at otomatik trigger ile.
--   * PK: BIGINT IDENTITY (dahili); uuid (dışa açık API kimliği) ana varlıklarda.
--   * FK: tenant -> CASCADE; lead alt kayıtları -> CASCADE; kullanıcı referansları -> SET NULL.
-- =============================================================================

BEGIN;

-- -----------------------------------------------------------------------------
-- 0. Eklentiler
-- -----------------------------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS pgcrypto;   -- gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS pg_trgm;    -- firma adı bulanık arama (trigram)
CREATE EXTENSION IF NOT EXISTS citext;     -- büyük/küçük harf duyarsız e-posta

-- -----------------------------------------------------------------------------
-- 1. ENUM Tipleri
-- -----------------------------------------------------------------------------
CREATE TYPE tenant_plan          AS ENUM ('free', 'starter', 'pro', 'enterprise');
CREATE TYPE tenant_status        AS ENUM ('active', 'suspended', 'cancelled');
CREATE TYPE user_status          AS ENUM ('invited', 'active', 'inactive', 'suspended');
CREATE TYPE lead_status          AS ENUM ('new', 'enriched', 'scored', 'unassigned', 'assigned',
                                          'contacted', 'qualified', 'in_pipeline', 'won', 'lost', 'nurturing');
CREATE TYPE lead_priority        AS ENUM ('hot', 'warm', 'cold');
CREATE TYPE product_status       AS ENUM ('active', 'inactive');
CREATE TYPE import_status        AS ENUM ('pending', 'processing', 'completed', 'failed');
CREATE TYPE ai_analysis_type     AS ENUM ('customer_analysis', 'next_action', 'summary', 'objection_handling');
CREATE TYPE ai_request_status    AS ENUM ('success', 'failed', 'rate_limited');
CREATE TYPE assignment_strategy  AS ENUM ('round_robin', 'sector_based', 'score_based',
                                          'geographic', 'capacity_based', 'manual');
CREATE TYPE assignment_status    AS ENUM ('active', 'reassigned', 'released');
CREATE TYPE task_type            AS ENUM ('call', 'email', 'meeting', 'follow_up', 'other');
CREATE TYPE task_status          AS ENUM ('pending', 'in_progress', 'done', 'cancelled');
CREATE TYPE activity_type        AS ENUM ('call', 'meeting', 'email', 'note', 'file', 'system');
CREATE TYPE activity_direction   AS ENUM ('inbound', 'outbound');
CREATE TYPE reminder_status      AS ENUM ('pending', 'sent', 'dismissed');
CREATE TYPE deal_status          AS ENUM ('open', 'won', 'lost');
CREATE TYPE notification_channel AS ENUM ('in_app', 'email', 'sms', 'whatsapp', 'push');
CREATE TYPE notification_status  AS ENUM ('queued', 'sent', 'delivered', 'failed', 'read');
CREATE TYPE job_status           AS ENUM ('pending', 'processing', 'done', 'failed');
CREATE TYPE priority_level       AS ENUM ('low', 'normal', 'high', 'urgent');

-- -----------------------------------------------------------------------------
-- 2. Ortak yardımcı: updated_at otomatik güncelleme
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

CREATE TABLE tenants (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  uuid        UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  name        TEXT NOT NULL,
  slug        TEXT NOT NULL UNIQUE,
  plan        tenant_plan NOT NULL DEFAULT 'starter',
  status      tenant_status NOT NULL DEFAULT 'active',
  settings    JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at  TIMESTAMPTZ
);

-- İzin kataloğu (global)
CREATE TABLE permissions (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code        TEXT NOT NULL UNIQUE,          -- örn: 'leads.view.all', 'users.manage'
  category    TEXT NOT NULL,                 -- örn: 'leads', 'users', 'reports'
  description TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Roller: tenant_id NULL => sistem (global) rolü
CREATE TABLE roles (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   BIGINT REFERENCES tenants(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  slug        TEXT NOT NULL,                 -- super_admin, admin, sales_manager, sales_rep, analyst
  level       SMALLINT NOT NULL DEFAULT 0,   -- hiyerarşi: büyük = daha yetkili
  is_system   BOOLEAN NOT NULL DEFAULT false,
  description TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, slug)
);

CREATE TABLE role_permissions (
  role_id       BIGINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  permission_id BIGINT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE users (
  id                  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  tenant_id           BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  role_id             BIGINT REFERENCES roles(id) ON DELETE SET NULL,
  name                TEXT NOT NULL,
  email               CITEXT NOT NULL,
  password_hash       TEXT,
  phone               TEXT,
  status              user_status NOT NULL DEFAULT 'invited',
  capacity            INTEGER NOT NULL DEFAULT 50,   -- aynı anda taşınabilecek aktif lead
  timezone            TEXT NOT NULL DEFAULT 'Europe/Istanbul',
  locale              TEXT NOT NULL DEFAULT 'tr',
  avatar_url          TEXT,
  two_factor_enabled  BOOLEAN NOT NULL DEFAULT false,
  two_factor_secret   TEXT,
  email_verified_at   TIMESTAMPTZ,
  last_login_at       TIMESTAMPTZ,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at          TIMESTAMPTZ,
  UNIQUE (tenant_id, email)
);

CREATE TABLE teams (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  manager_id  BIGINT REFERENCES users(id) ON DELETE SET NULL,
  description TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at  TIMESTAMPTZ
);

CREATE TABLE team_members (
  team_id      BIGINT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  user_id      BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_in_team TEXT,
  joined_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (team_id, user_id)
);

CREATE TABLE sessions (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash  TEXT NOT NULL UNIQUE,
  ip_address  INET,
  user_agent  TEXT,
  expires_at  TIMESTAMPTZ NOT NULL,
  revoked_at  TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- =============================================================================
-- 14.7  ÜRÜN (lead/score'dan önce gerekli)
-- =============================================================================

CREATE TABLE products (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  slug        TEXT NOT NULL,
  description TEXT,
  icp         JSONB NOT NULL DEFAULT '{}'::jsonb,   -- ideal müşteri profili
  price_min   NUMERIC(12,2),
  price_max   NUMERIC(12,2),
  currency    CHAR(3) NOT NULL DEFAULT 'TRY',
  status      product_status NOT NULL DEFAULT 'active',
  sort_order  INTEGER NOT NULL DEFAULT 0,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at  TIMESTAMPTZ,
  UNIQUE (tenant_id, slug)
);

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

CREATE TABLE sectors (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   BIGINT REFERENCES tenants(id) ON DELETE CASCADE,  -- NULL => global taksonomi
  name        TEXT NOT NULL,
  slug        TEXT NOT NULL,
  parent_id   BIGINT REFERENCES sectors(id) ON DELETE SET NULL,
  description TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, slug)
);

CREATE TABLE product_sector_map (
  product_id  BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  sector_id   BIGINT NOT NULL REFERENCES sectors(id) ON DELETE CASCADE,
  fit_weight  NUMERIC(4,2) NOT NULL DEFAULT 1.0,   -- ürün-sektör uyum ağırlığı (skorlama girdisi)
  PRIMARY KEY (product_id, sector_id)
);

CREATE TABLE tags (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  color      TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, name)
);

CREATE TABLE leads (
  id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  uuid             UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  tenant_id        BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name             TEXT NOT NULL,                      -- firma adı
  sector_id        BIGINT REFERENCES sectors(id) ON DELETE SET NULL,
  owner_id         BIGINT REFERENCES users(id) ON DELETE SET NULL,
  status           lead_status NOT NULL DEFAULT 'new',
  source           TEXT,                               -- manual, import, form, campaign...
  website          TEXT,
  email            CITEXT,
  phone            TEXT,
  address          TEXT,
  city             TEXT,
  country          TEXT DEFAULT 'TR',
  company_size     TEXT,                               -- micro/small/medium/large
  employee_count   INTEGER,
  annual_revenue   NUMERIC(14,2),
  tax_number       TEXT,
  description      TEXT,
  social           JSONB NOT NULL DEFAULT '{}'::jsonb, -- {instagram, linkedin, ...}
  custom_fields    JSONB NOT NULL DEFAULT '{}'::jsonb,
  dedup_hash       TEXT,                               -- normalize edilmiş benzersizlik anahtarı
  last_activity_at TIMESTAMPTZ,
  created_by       BIGINT REFERENCES users(id) ON DELETE SET NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at       TIMESTAMPTZ,
  UNIQUE (tenant_id, dedup_hash)
);

CREATE TABLE lead_tags (
  lead_id BIGINT NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
  tag_id  BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (lead_id, tag_id)
);

CREATE TABLE lead_imports (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id     BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id       BIGINT REFERENCES users(id) ON DELETE SET NULL,
  file_name     TEXT NOT NULL,
  file_url      TEXT,
  status        import_status NOT NULL DEFAULT 'pending',
  total_rows    INTEGER NOT NULL DEFAULT 0,
  success_count INTEGER NOT NULL DEFAULT 0,
  failed_count  INTEGER NOT NULL DEFAULT 0,
  mapping       JSONB NOT NULL DEFAULT '{}'::jsonb,    -- sütun eşleme
  error_log     JSONB NOT NULL DEFAULT '[]'::jsonb,
  started_at    TIMESTAMPTZ,
  finished_at   TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

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

CREATE TABLE scoring_rules (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  criterion  TEXT NOT NULL,                  -- örn: 'sector_fit', 'has_website', 'company_size'
  operator   TEXT,                           -- '=', '>', 'in', 'exists'...
  value      JSONB,                          -- karşılaştırma değeri
  weight     NUMERIC(5,2) NOT NULL DEFAULT 1.0,
  threshold  NUMERIC(5,2),
  is_active  BOOLEAN NOT NULL DEFAULT true,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE lead_scores (
  id                    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id             BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  lead_id               BIGINT NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
  score                 NUMERIC(5,2) NOT NULL,         -- 0-100
  priority              lead_priority NOT NULL,
  recommended_product_id BIGINT REFERENCES products(id) ON DELETE SET NULL,
  reasoning             TEXT,                          -- insan-okur gerekçe
  confidence            NUMERIC(4,3),                  -- 0-1
  model                 TEXT,                          -- örn: claude-opus-4-8
  breakdown             JSONB NOT NULL DEFAULT '{}'::jsonb, -- sinyal bazlı puan kırılımı
  is_current            BOOLEAN NOT NULL DEFAULT true, -- en güncel skor
  scored_at             TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE ai_analyses (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id     BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  lead_id       BIGINT NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
  type          ai_analysis_type NOT NULL,
  summary       TEXT,
  next_action   TEXT,
  raw_output    JSONB,
  model         TEXT,
  tokens_input  INTEGER,
  tokens_output INTEGER,
  cost          NUMERIC(10,5),
  created_by    BIGINT REFERENCES users(id) ON DELETE SET NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE ai_usage_logs (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id     BIGINT REFERENCES tenants(id) ON DELETE CASCADE,
  user_id       BIGINT REFERENCES users(id) ON DELETE SET NULL,
  request_type  TEXT NOT NULL,                 -- 'scoring', 'analysis', 'summary'
  model         TEXT NOT NULL,
  tokens_input  INTEGER NOT NULL DEFAULT 0,
  tokens_output INTEGER NOT NULL DEFAULT 0,
  cost          NUMERIC(10,5) NOT NULL DEFAULT 0,
  status        ai_request_status NOT NULL DEFAULT 'success',
  latency_ms    INTEGER,
  error         TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

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

CREATE TABLE assignment_rules (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  strategy   assignment_strategy NOT NULL,
  conditions JSONB NOT NULL DEFAULT '{}'::jsonb,  -- sektör/skor/bölge koşulları
  priority   INTEGER NOT NULL DEFAULT 0,          -- kural değerlendirme sırası
  is_active  BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE assignments (
  id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id        BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  lead_id          BIGINT NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
  user_id          BIGINT REFERENCES users(id) ON DELETE SET NULL,
  assigned_by      BIGINT REFERENCES users(id) ON DELETE SET NULL,
  rule_id          BIGINT REFERENCES assignment_rules(id) ON DELETE SET NULL,
  strategy         assignment_strategy NOT NULL DEFAULT 'manual',
  status           assignment_status NOT NULL DEFAULT 'active',
  sla_due_at       TIMESTAMPTZ,                    -- ilk temas SLA son tarihi
  first_contact_at TIMESTAMPTZ,
  assigned_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  released_at      TIMESTAMPTZ
);

CREATE TABLE tasks (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id    BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  lead_id      BIGINT REFERENCES leads(id) ON DELETE CASCADE,
  user_id      BIGINT REFERENCES users(id) ON DELETE SET NULL,   -- atanan
  created_by   BIGINT REFERENCES users(id) ON DELETE SET NULL,
  type         task_type NOT NULL DEFAULT 'other',
  title        TEXT NOT NULL,
  description  TEXT,
  priority     priority_level NOT NULL DEFAULT 'normal',
  due_at       TIMESTAMPTZ,
  status       task_status NOT NULL DEFAULT 'pending',
  completed_at TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

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

CREATE TABLE activities (
  id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id        BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  lead_id          BIGINT NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
  user_id          BIGINT REFERENCES users(id) ON DELETE SET NULL,
  type             activity_type NOT NULL,
  direction        activity_direction,
  subject          TEXT,
  content          TEXT,
  outcome          TEXT,                          -- görüşme sonucu
  duration_seconds INTEGER,
  next_step        TEXT,                          -- sonraki adım (zorunlu kullanım iş kuralı)
  occurred_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE attachments (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  lead_id     BIGINT REFERENCES leads(id) ON DELETE CASCADE,
  activity_id BIGINT REFERENCES activities(id) ON DELETE CASCADE,
  uploaded_by BIGINT REFERENCES users(id) ON DELETE SET NULL,
  file_name   TEXT NOT NULL,
  file_url    TEXT NOT NULL,
  mime_type   TEXT,
  size_bytes  BIGINT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE reminders (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  lead_id    BIGINT REFERENCES leads(id) ON DELETE CASCADE,
  user_id    BIGINT REFERENCES users(id) ON DELETE CASCADE,
  title      TEXT NOT NULL,
  message    TEXT,
  remind_at  TIMESTAMPTZ NOT NULL,
  status     reminder_status NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

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

CREATE TABLE pipeline_stages (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  slug        TEXT NOT NULL,
  sort_order  INTEGER NOT NULL DEFAULT 0,
  probability NUMERIC(5,2) NOT NULL DEFAULT 0,   -- aşama kazanma olasılığı %
  is_won      BOOLEAN NOT NULL DEFAULT false,
  is_lost     BOOLEAN NOT NULL DEFAULT false,
  is_active   BOOLEAN NOT NULL DEFAULT true,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, slug)
);

CREATE TABLE lost_reasons (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  sort_order INTEGER NOT NULL DEFAULT 0,
  is_active  BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE deals (
  id                  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  uuid                UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  tenant_id           BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  lead_id             BIGINT NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
  product_id          BIGINT REFERENCES products(id) ON DELETE SET NULL,
  stage_id            BIGINT REFERENCES pipeline_stages(id) ON DELETE SET NULL,
  owner_id            BIGINT REFERENCES users(id) ON DELETE SET NULL,
  title               TEXT NOT NULL,
  value               NUMERIC(14,2) NOT NULL DEFAULT 0,
  currency            CHAR(3) NOT NULL DEFAULT 'TRY',
  probability         NUMERIC(5,2),
  expected_close_date DATE,
  status              deal_status NOT NULL DEFAULT 'open',
  lost_reason_id      BIGINT REFERENCES lost_reasons(id) ON DELETE SET NULL,
  won_at              TIMESTAMPTZ,
  lost_at             TIMESTAMPTZ,
  created_by          BIGINT REFERENCES users(id) ON DELETE SET NULL,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at          TIMESTAMPTZ
);

CREATE TABLE deal_stage_history (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id     BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  deal_id       BIGINT NOT NULL REFERENCES deals(id) ON DELETE CASCADE,
  from_stage_id BIGINT REFERENCES pipeline_stages(id) ON DELETE SET NULL,
  to_stage_id   BIGINT REFERENCES pipeline_stages(id) ON DELETE SET NULL,
  changed_by    BIGINT REFERENCES users(id) ON DELETE SET NULL,
  note          TEXT,
  changed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

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

CREATE TABLE notifications (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type        TEXT NOT NULL,                  -- 'lead.assigned', 'sla.breach', 'hot_lead'...
  title       TEXT NOT NULL,
  body        TEXT,
  channel     notification_channel NOT NULL DEFAULT 'in_app',
  priority    priority_level NOT NULL DEFAULT 'normal',
  entity_type TEXT,                           -- 'lead', 'deal', 'task'
  entity_id   BIGINT,
  data        JSONB NOT NULL DEFAULT '{}'::jsonb,
  read_at     TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE notification_preferences (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id    BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  channel    notification_channel NOT NULL,
  enabled    BOOLEAN NOT NULL DEFAULT true,
  UNIQUE (user_id, event_type, channel)
);

CREATE TABLE notification_logs (
  id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  notification_id   BIGINT NOT NULL REFERENCES notifications(id) ON DELETE CASCADE,
  channel           notification_channel NOT NULL,
  status            notification_status NOT NULL DEFAULT 'queued',
  provider_response JSONB,
  sent_at           TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

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

CREATE TABLE audit_logs (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   BIGINT REFERENCES tenants(id) ON DELETE CASCADE,
  user_id     BIGINT REFERENCES users(id) ON DELETE SET NULL,
  action      TEXT NOT NULL,                  -- 'create', 'update', 'delete', 'assign'...
  entity_type TEXT NOT NULL,
  entity_id   BIGINT,
  changes     JSONB,                          -- {before, after}
  ip_address  INET,
  user_agent  TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE report_snapshots (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id    BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  type         TEXT NOT NULL,
  params       JSONB NOT NULL DEFAULT '{}'::jsonb,
  data         JSONB NOT NULL DEFAULT '{}'::jsonb,
  generated_by BIGINT REFERENCES users(id) ON DELETE SET NULL,
  generated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE settings (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  key        TEXT NOT NULL,
  value      JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, key)
);

CREATE TABLE webhooks (
  id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id         BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  url               TEXT NOT NULL,
  events            TEXT[] NOT NULL DEFAULT '{}',   -- ['lead.created','deal.won']
  secret            TEXT,
  is_active         BOOLEAN NOT NULL DEFAULT true,
  last_triggered_at TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE jobs (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id    BIGINT REFERENCES tenants(id) ON DELETE CASCADE,
  type         TEXT NOT NULL,                  -- 'score_lead', 'send_notification', 'import'
  payload      JSONB NOT NULL DEFAULT '{}'::jsonb,
  status       job_status NOT NULL DEFAULT 'pending',
  attempts     INTEGER NOT NULL DEFAULT 0,
  max_attempts INTEGER NOT NULL DEFAULT 3,
  available_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  reserved_at  TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  error        TEXT,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- =============================================================================
-- 3. INDEX'LER
-- =============================================================================

-- Kimlik / Yetki
CREATE INDEX idx_users_tenant_status   ON users (tenant_id, status) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_role            ON users (role_id);
CREATE INDEX idx_teams_tenant          ON teams (tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_team_members_user     ON team_members (user_id);
CREATE INDEX idx_sessions_user         ON sessions (user_id);
CREATE INDEX idx_sessions_expires      ON sessions (expires_at);

-- Lead
CREATE INDEX idx_leads_tenant_status   ON leads (tenant_id, status) WHERE deleted_at IS NULL;
CREATE INDEX idx_leads_tenant_owner    ON leads (tenant_id, owner_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_leads_tenant_sector   ON leads (tenant_id, sector_id);
CREATE INDEX idx_leads_last_activity   ON leads (tenant_id, last_activity_at DESC);
CREATE INDEX idx_leads_name_trgm       ON leads USING gin (name gin_trgm_ops);
CREATE INDEX idx_leads_custom_fields   ON leads USING gin (custom_fields);
CREATE INDEX idx_lead_tags_tag         ON lead_tags (tag_id);
CREATE INDEX idx_lead_imports_tenant   ON lead_imports (tenant_id, status);

-- Skorlama
CREATE UNIQUE INDEX uq_lead_scores_current ON lead_scores (lead_id) WHERE is_current;
CREATE INDEX idx_lead_scores_priority  ON lead_scores (tenant_id, priority, score DESC);
CREATE INDEX idx_ai_analyses_lead      ON ai_analyses (lead_id, type);
CREATE INDEX idx_ai_usage_tenant_date  ON ai_usage_logs (tenant_id, created_at DESC);

-- Atama / Görev
CREATE UNIQUE INDEX uq_assignment_active ON assignments (lead_id) WHERE status = 'active';
CREATE INDEX idx_assignments_user       ON assignments (user_id, status);
CREATE INDEX idx_assignments_sla        ON assignments (sla_due_at) WHERE status = 'active' AND first_contact_at IS NULL;
CREATE INDEX idx_tasks_user_status      ON tasks (user_id, status, due_at);
CREATE INDEX idx_tasks_lead             ON tasks (lead_id);

-- CRM
CREATE INDEX idx_activities_lead        ON activities (lead_id, occurred_at DESC);
CREATE INDEX idx_activities_user        ON activities (user_id, occurred_at DESC);
CREATE INDEX idx_attachments_lead       ON attachments (lead_id);
CREATE INDEX idx_reminders_due          ON reminders (remind_at) WHERE status = 'pending';

-- Pipeline
CREATE INDEX idx_deals_tenant_stage     ON deals (tenant_id, stage_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_deals_owner            ON deals (owner_id, status);
CREATE INDEX idx_deals_lead             ON deals (lead_id);
CREATE INDEX idx_deals_close_date       ON deals (tenant_id, expected_close_date) WHERE status = 'open';
CREATE INDEX idx_deal_history_deal      ON deal_stage_history (deal_id, changed_at);

-- Bildirim
CREATE INDEX idx_notifications_user_unread ON notifications (user_id, created_at DESC) WHERE read_at IS NULL;
CREATE INDEX idx_notification_logs_notif   ON notification_logs (notification_id);

-- Sistem
CREATE INDEX idx_audit_tenant_entity    ON audit_logs (tenant_id, entity_type, entity_id);
CREATE INDEX idx_audit_user             ON audit_logs (user_id, created_at DESC);
CREATE INDEX idx_jobs_queue             ON jobs (status, available_at) WHERE status = 'pending';
CREATE INDEX idx_report_snapshots_type  ON report_snapshots (tenant_id, type, generated_at DESC);

-- =============================================================================
-- 4. updated_at TRIGGER'LARI (updated_at kolonu olan her tabloya otomatik)
-- =============================================================================
DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN
    SELECT table_name
    FROM information_schema.columns
    WHERE table_schema = 'public' AND column_name = 'updated_at'
  LOOP
    EXECUTE format(
      'CREATE TRIGGER trg_%1$s_updated_at BEFORE UPDATE ON %1$I
         FOR EACH ROW EXECUTE FUNCTION set_updated_at();', r.table_name);
  END LOOP;
END $$;

COMMIT;