# 10 — Veritabanı Şeması

## Bağlantı

```
Host:     localhost
Port:     3306
DB:       tekyerden_flovy
User:     tekyerden_flovyuser
Password: r~HQ3~SjF[WaQU;b
```

---

## Tablo Listesi (Öncelik Sırasına Göre)

```
Faz 1 — Çekirdek:
  tenants
  tenant_sessions               ← JWT revocation
  chat_widgets
  chat_sessions
  chat_messages
  flovy_visitors                ← Moat — ziyaretçi hafızası
  flovy_visitor_sessions
  products
  product_categories
  product_variants
  flovy_knowledge_sources
  flovy_knowledge_chunks
  flovy_tool_calls
  payment_links
  flovy_orders
  efatura_mukellef_cache

Faz 2 — SaaS:
  plans
  subscriptions
  flovy_leads                   ← collect_lead tool çıktısı
  flovy_proactive_rules
  flovy_visitor_profiles        ← Genişletilmiş ziyaretçi veri
  webhook_logs                  ← PayTR callback geçmişi
```

---

## Migration Stratejisi

Alembic kullanılır. Her migration `upgrade()` ve `downgrade()` içerir.

```bash
# İlk migration
alembic revision --autogenerate -m "create_core_tables"
alembic upgrade head

# Sonraki
alembic revision --autogenerate -m "add_visitor_memory"
alembic upgrade head

# Rollback
alembic downgrade -1
```

---

## Tam Şema (CREATE TABLE — MySQL)

```sql
-- ================================================================
-- TENANTS
-- ================================================================
CREATE TABLE tenants (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    email VARCHAR(200) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    plan ENUM('free','starter','pro','business') DEFAULT 'free',
    plan_expires_at DATETIME NULL,
    is_active TINYINT(1) DEFAULT 1,
    api_key VARCHAR(64) NOT NULL UNIQUE,
    webhook_secret VARCHAR(64) NOT NULL,
    monthly_message_quota INT DEFAULT 100,
    monthly_message_used INT DEFAULT 0,
    ai_budget_monthly DECIMAL(10,2) DEFAULT 0,
    ai_budget_used DECIMAL(10,2) DEFAULT 0,
    ai_budget_period VARCHAR(7) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ================================================================
-- CHAT WIDGETS
-- ================================================================
CREATE TABLE chat_widgets (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    name VARCHAR(200) NOT NULL,
    api_key VARCHAR(64) NOT NULL UNIQUE,
    allowed_domains JSON NULL,
    ai_enabled TINYINT(1) DEFAULT 1,
    is_online TINYINT(1) DEFAULT 1,
    persona JSON NULL,
    settings JSON NULL,
    ai_budget_monthly DECIMAL(10,2) DEFAULT 0,
    ai_budget_used DECIMAL(10,2) DEFAULT 0,
    ai_budget_period VARCHAR(7) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_api_key (api_key)
);

-- ================================================================
-- VISITORS (MOAT — Ziyaretçi Hafızası)
-- ================================================================
CREATE TABLE flovy_visitors (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    visitor_uuid VARCHAR(64) NOT NULL,
    fingerprint_hash VARCHAR(64) NULL,
    email VARCHAR(200) NULL,
    phone VARCHAR(30) NULL,
    name VARCHAR(200) NULL,
    ip_address VARCHAR(45) NULL,
    ip_history JSON NULL,
    device_type ENUM('mobile','desktop','tablet') NULL,
    browser VARCHAR(50) NULL,
    language VARCHAR(10) NULL,
    total_sessions INT DEFAULT 0,
    total_messages INT DEFAULT 0,
    last_session_at DATETIME NULL,
    last_intent VARCHAR(50) NULL,
    lifetime_value DECIMAL(12,2) DEFAULT 0,
    products_viewed JSON NULL,
    products_purchased JSON NULL,
    cart_abandoned JSON NULL,
    interests JSON NULL,
    preferred_time_of_day ENUM('morning','afternoon','evening','night') NULL,
    is_returning TINYINT(1) DEFAULT 0,
    kvkk_consent_at DATETIME NULL,
    anonymized_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    UNIQUE KEY uq_tenant_visitor (tenant_id, visitor_uuid),
    INDEX idx_fingerprint (tenant_id, fingerprint_hash),
    INDEX idx_email (tenant_id, email),
    INDEX idx_phone (tenant_id, phone)
);

-- ================================================================
-- CHAT SESSIONS
-- ================================================================
CREATE TABLE chat_sessions (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    widget_id CHAR(36) NOT NULL,
    visitor_id CHAR(36) NULL,
    session_token VARCHAR(64) NOT NULL UNIQUE,
    status ENUM('open','closed','escalated') DEFAULT 'open',
    visitor_name VARCHAR(200) NULL,
    visitor_email VARCHAR(200) NULL,
    visitor_phone VARCHAR(30) NULL,
    visitor_ip VARCHAR(45) NULL,
    visitor_browser VARCHAR(255) NULL,
    page_url VARCHAR(1000) NULL,
    referrer VARCHAR(500) NULL,
    kvkk_consent_at DATETIME NULL,
    intent VARCHAR(50) NULL,
    nps_score TINYINT NULL,
    nps_comment TEXT NULL,
    nps_submitted_at DATETIME NULL,
    escalated_at DATETIME NULL,
    idle_ping_sent_at DATETIME NULL,
    metadata JSON NULL,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    ended_at DATETIME NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (widget_id) REFERENCES chat_widgets(id),
    FOREIGN KEY (visitor_id) REFERENCES flovy_visitors(id) ON DELETE SET NULL,
    INDEX idx_token (session_token),
    INDEX idx_tenant_status (tenant_id, status)
);

-- ================================================================
-- CHAT MESSAGES
-- ================================================================
CREATE TABLE chat_messages (
    id CHAR(36) PRIMARY KEY,
    session_id CHAR(36) NOT NULL,
    tenant_id CHAR(36) NOT NULL,
    sender_type ENUM('visitor','ai','operator','system') NOT NULL,
    message TEXT NOT NULL,
    message_type ENUM('text','ai','system','tool_result') DEFAULT 'text',
    meta JSON NULL,
    model VARCHAR(100) NULL,
    latency_ms INT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE,
    INDEX idx_session (session_id),
    INDEX idx_tenant_created (tenant_id, created_at)
);

-- ================================================================
-- PRODUCTS
-- ================================================================
CREATE TABLE product_categories (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL,
    parent_id CHAR(36) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES product_categories(id) ON DELETE SET NULL,
    UNIQUE KEY uq_tenant_slug (tenant_id, slug)
);

CREATE TABLE products (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    category_id CHAR(36) NULL,
    title VARCHAR(500) NOT NULL,
    description TEXT NULL,
    bullet_points JSON NULL,
    sku VARCHAR(100) NULL,
    barcode VARCHAR(100) NULL,
    price DECIMAL(10,2) NOT NULL,
    compare_at_price DECIMAL(10,2) NULL,
    currency CHAR(3) DEFAULT 'TRY',
    stock_quantity INT DEFAULT 0,
    stock_status ENUM('in_stock','low','out_of_stock') DEFAULT 'in_stock',
    low_stock_threshold INT DEFAULT 5,
    is_active TINYINT(1) DEFAULT 1,
    images JSON NULL,
    embedding_status ENUM('pending','indexed','failed') DEFAULT 'pending',
    embedding_updated_at DATETIME NULL,
    meta JSON NULL,
    deleted_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES product_categories(id) ON DELETE SET NULL,
    INDEX idx_tenant_active (tenant_id, is_active),
    INDEX idx_tenant_sku (tenant_id, sku)
);

CREATE TABLE product_variants (
    id CHAR(36) PRIMARY KEY,
    product_id CHAR(36) NOT NULL,
    title VARCHAR(300) NOT NULL,
    sku VARCHAR(100) NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    attributes JSON NULL,
    is_active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- ================================================================
-- KNOWLEDGE BASE (RAG)
-- ================================================================
CREATE TABLE flovy_knowledge_sources (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    source_type ENUM('product','faq','url','pdf','manual') NOT NULL,
    title VARCHAR(500) NULL,
    url VARCHAR(1000) NULL,
    content LONGTEXT NULL,
    status ENUM('pending','indexing','indexed','failed') DEFAULT 'pending',
    chunk_count INT DEFAULT 0,
    last_indexed_at DATETIME NULL,
    error_message TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

CREATE TABLE flovy_knowledge_chunks (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    source_id CHAR(36) NOT NULL,
    source_type VARCHAR(20) NOT NULL,
    content TEXT NOT NULL,
    embedding JSON NULL,
    embedding_model VARCHAR(100) NULL,
    meta JSON NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (source_id) REFERENCES flovy_knowledge_sources(id) ON DELETE CASCADE,
    INDEX idx_tenant_source_type (tenant_id, source_type)
);

-- ================================================================
-- TOOL CALLS (Confirmation flow + audit)
-- ================================================================
CREATE TABLE flovy_tool_calls (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    session_id CHAR(36) NOT NULL,
    message_id CHAR(36) NULL,
    tool_name VARCHAR(100) NOT NULL,
    args JSON NULL,
    result JSON NULL,
    status ENUM('unconfirmed','executing','success','error','denied') NOT NULL,
    error TEXT NULL,
    latency_ms INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES chat_sessions(id) ON DELETE CASCADE
);

-- ================================================================
-- ORDERS + PAYMENTS
-- ================================================================
CREATE TABLE flovy_orders (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    session_id CHAR(36) NULL,
    visitor_id CHAR(36) NULL,
    order_number VARCHAR(30) NOT NULL UNIQUE,
    status ENUM('pending','paid','cancelled','refunded') DEFAULT 'pending',
    payment_status ENUM('pending','paid','failed') DEFAULT 'pending',
    items JSON NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL,
    tax_total DECIMAL(12,2) DEFAULT 0,
    total DECIMAL(12,2) NOT NULL,
    currency CHAR(3) DEFAULT 'TRY',
    customer_name VARCHAR(200) NULL,
    customer_email VARCHAR(200) NULL,
    customer_phone VARCHAR(30) NULL,
    customer_address TEXT NULL,
    customer_tax_number VARCHAR(20) NULL,
    customer_tax_office VARCHAR(200) NULL,
    payment_link_id CHAR(36) NULL,
    paytr_merchant_oid VARCHAR(100) NULL,
    paid_at DATETIME NULL,
    efatura_status ENUM('pending','sent','failed','not_required') DEFAULT 'pending',
    efatura_ettn VARCHAR(100) NULL,
    efatura_type ENUM('efatura','earsiv') NULL,
    efatura_number VARCHAR(50) NULL,
    efatura_sent_at DATETIME NULL,
    efatura_error TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_tenant_status (tenant_id, status)
);

CREATE TABLE payment_links (
    id CHAR(36) PRIMARY KEY,
    tenant_id CHAR(36) NOT NULL,
    session_id CHAR(36) NULL,
    order_id CHAR(36) NULL,
    paytr_link_id VARCHAR(100) NULL,
    paytr_link_url VARCHAR(500) NOT NULL,
    callback_id VARCHAR(100) NOT NULL UNIQUE,
    amount DECIMAL(10,2) NOT NULL,
    currency CHAR(3) DEFAULT 'TRY',
    status ENUM('created','paid','expired','cancelled') DEFAULT 'created',
    customer_email VARCHAR(200) NULL,
    expires_at DATETIME NULL,
    paid_at DATETIME NULL,
    paytr_merchant_oid VARCHAR(100) NULL,
    payment_type VARCHAR(50) NULL,
    callback_response JSON NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_callback_id (callback_id)
);

-- ================================================================
-- GIB MÜKELLEF CACHE
-- ================================================================
CREATE TABLE efatura_mukellef_cache (
    vkn_tckn VARCHAR(20) PRIMARY KEY,
    unvan VARCHAR(500) NULL,
    is_mukellef TINYINT(1) DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## Index Stratejisi

| Sorgulama Deseni | Index |
|---|---|
| `tenant_id + visitor_uuid` | `UNIQUE (tenant_id, visitor_uuid)` |
| `tenant_id + email` | `INDEX (tenant_id, email)` |
| `tenant_id + fingerprint` | `INDEX (tenant_id, fingerprint_hash)` |
| `callback_id` lookup | `UNIQUE (callback_id)` |
| `session_id` message çekme | `INDEX (session_id)` |
| `tenant_id + is_active` ürün | `INDEX (tenant_id, is_active)` |
| knowledge chunk retrieval | `INDEX (tenant_id, source_type)` |