-- =====================================================
-- WhatsApp Toplu Mesajlaşma Platformu - Veritabanı Şeması
-- Multi-tenant yapı ile müşteri yönetimi
-- =====================================================

-- Kullanıcılar tablosu (Admin + Müşteriler)
CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    role ENUM('admin', 'customer') NOT NULL DEFAULT 'customer',
    status ENUM('active', 'suspended', 'deleted') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uk_users_email (email),
    KEY idx_users_role (role),
    KEY idx_users_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Müşteri hesapları (Tenant bilgileri)
CREATE TABLE IF NOT EXISTS tenants (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    company_name VARCHAR(255) NULL,
    service_type ENUM('panel_only', 'full_service') NOT NULL DEFAULT 'panel_only',
    max_numbers INT UNSIGNED NOT NULL DEFAULT 10 COMMENT 'İzin verilen maksimum numara sayısı',
    max_daily_messages INT UNSIGNED NOT NULL DEFAULT 1000 COMMENT 'Günlük mesaj limiti',
    status ENUM('active', 'suspended', 'trial') NOT NULL DEFAULT 'trial',
    trial_ends_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    KEY idx_tenants_service_type (service_type),
    KEY idx_tenants_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- WhatsApp numaraları
CREATE TABLE IF NOT EXISTS whatsapp_numbers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    phone_number VARCHAR(20) NOT NULL COMMENT 'E.164 format: +905321234567',
    display_name VARCHAR(255) NULL,
    qr_code TEXT NULL COMMENT 'QR kod verisi',
    session_data MEDIUMTEXT NULL COMMENT 'WhatsApp session bilgisi (encrypted)',
    connection_status ENUM('disconnected', 'connecting', 'connected', 'failed') NOT NULL DEFAULT 'disconnected',
    is_warming BOOLEAN NOT NULL DEFAULT TRUE COMMENT 'Otomatik warming aktif mi?',
    warming_frequency ENUM('high', 'medium', 'low', 'none') NOT NULL DEFAULT 'high' COMMENT 'Yeni=high, Eski=low',
    added_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_warming_at TIMESTAMP NULL DEFAULT NULL,
    last_connected_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    UNIQUE KEY uk_whatsapp_numbers_phone (phone_number),
    KEY idx_whatsapp_numbers_tenant (tenant_id),
    KEY idx_whatsapp_numbers_status (connection_status),
    KEY idx_whatsapp_numbers_warming (is_warming, warming_frequency)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mesaj şablonları (warming için kullanılacak)
CREATE TABLE IF NOT EXISTS warming_message_templates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(64) NOT NULL,
    category VARCHAR(64) NOT NULL DEFAULT 'general',
    outbound_pattern TEXT NOT NULL,
    reply_pattern TEXT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_warming_templates_slug (slug),
    KEY idx_warming_templates_category (category),
    KEY idx_warming_templates_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Warming mesaj geçmişi (hangi numara hangi şablonu kullandı)
CREATE TABLE IF NOT EXISTS warming_history (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    whatsapp_number_id BIGINT UNSIGNED NOT NULL,
    peer_number_id BIGINT UNSIGNED NOT NULL COMMENT 'Sohbet edilen diğer numara',
    template_id BIGINT UNSIGNED NOT NULL,
    message_direction ENUM('outbound', 'reply') NOT NULL,
    message_text TEXT NOT NULL,
    sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'sent', 'delivered', 'read', 'failed') NOT NULL DEFAULT 'pending',
    FOREIGN KEY (whatsapp_number_id) REFERENCES whatsapp_numbers(id) ON DELETE CASCADE,
    FOREIGN KEY (peer_number_id) REFERENCES whatsapp_numbers(id) ON DELETE CASCADE,
    FOREIGN KEY (template_id) REFERENCES warming_message_templates(id) ON DELETE CASCADE,
    KEY idx_warming_history_number (whatsapp_number_id),
    KEY idx_warming_history_sent (sent_at),
    KEY idx_warming_history_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kişiler/Alıcılar listesi
CREATE TABLE IF NOT EXISTS contacts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    phone_number VARCHAR(20) NOT NULL COMMENT 'E.164 format',
    name VARCHAR(255) NULL,
    tags JSON NULL COMMENT 'Etiketler: ["müşteri", "vip", vb]',
    custom_fields JSON NULL COMMENT 'Özel alanlar',
    is_blocked BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    UNIQUE KEY uk_contacts_tenant_phone (tenant_id, phone_number),
    KEY idx_contacts_tenant (tenant_id),
    KEY idx_contacts_blocked (is_blocked)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mesaj kampanyaları (toplu mesaj gönderimi)
CREATE TABLE IF NOT EXISTS campaigns (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    message_text TEXT NOT NULL,
    media_type ENUM('none', 'image', 'video', 'audio', 'document') NOT NULL DEFAULT 'none',
    media_url VARCHAR(512) NULL,
    media_caption TEXT NULL,
    scheduled_at TIMESTAMP NULL DEFAULT NULL COMMENT 'Zamanlanmış gönderim',
    status ENUM('draft', 'scheduled', 'sending', 'completed', 'paused', 'cancelled') NOT NULL DEFAULT 'draft',
    total_recipients INT UNSIGNED NOT NULL DEFAULT 0,
    sent_count INT UNSIGNED NOT NULL DEFAULT 0,
    delivered_count INT UNSIGNED NOT NULL DEFAULT 0,
    read_count INT UNSIGNED NOT NULL DEFAULT 0,
    failed_count INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    started_at TIMESTAMP NULL DEFAULT NULL,
    completed_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    KEY idx_campaigns_tenant (tenant_id),
    KEY idx_campaigns_status (status),
    KEY idx_campaigns_scheduled (scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kampanya alıcıları
CREATE TABLE IF NOT EXISTS campaign_recipients (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NOT NULL,
    whatsapp_number_id BIGINT UNSIGNED NOT NULL COMMENT 'Hangi numaradan gönderilecek',
    status ENUM('pending', 'sent', 'delivered', 'read', 'failed') NOT NULL DEFAULT 'pending',
    sent_at TIMESTAMP NULL DEFAULT NULL,
    delivered_at TIMESTAMP NULL DEFAULT NULL,
    read_at TIMESTAMP NULL DEFAULT NULL,
    error_message TEXT NULL,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    FOREIGN KEY (whatsapp_number_id) REFERENCES whatsapp_numbers(id) ON DELETE CASCADE,
    KEY idx_campaign_recipients_campaign (campaign_id),
    KEY idx_campaign_recipients_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gelen/Giden tüm mesajlar (inbox)
CREATE TABLE IF NOT EXISTS messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    whatsapp_number_id BIGINT UNSIGNED NOT NULL,
    contact_phone VARCHAR(20) NOT NULL,
    direction ENUM('inbound', 'outbound') NOT NULL,
    message_type ENUM('text', 'image', 'video', 'audio', 'document', 'location', 'contact') NOT NULL DEFAULT 'text',
    message_text TEXT NULL,
    media_url VARCHAR(512) NULL,
    media_mime_type VARCHAR(100) NULL,
    message_id VARCHAR(255) NULL COMMENT 'WhatsApp message ID',
    campaign_id BIGINT UNSIGNED NULL COMMENT 'Kampanya ile ilişkili ise',
    status ENUM('pending', 'sent', 'delivered', 'read', 'failed') NOT NULL DEFAULT 'pending',
    is_warming_message BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at TIMESTAMP NULL DEFAULT NULL,
    delivered_at TIMESTAMP NULL DEFAULT NULL,
    read_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (whatsapp_number_id) REFERENCES whatsapp_numbers(id) ON DELETE CASCADE,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
    KEY idx_messages_tenant (tenant_id),
    KEY idx_messages_whatsapp_number (whatsapp_number_id),
    KEY idx_messages_direction (direction),
    KEY idx_messages_created (created_at),
    KEY idx_messages_contact (contact_phone),
    KEY idx_messages_warming (is_warming_message)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Otomatik cevaplar
CREATE TABLE IF NOT EXISTS auto_replies (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    whatsapp_number_id BIGINT UNSIGNED NULL COMMENT 'Belirli numara için, NULL ise tüm numaralar',
    trigger_type ENUM('keyword', 'all', 'first_message', 'business_hours') NOT NULL DEFAULT 'keyword',
    trigger_keywords JSON NULL COMMENT 'Anahtar kelimeler: ["merhaba", "bilgi", vb]',
    reply_text TEXT NOT NULL,
    reply_media_url VARCHAR(512) NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    priority INT NOT NULL DEFAULT 0 COMMENT 'Öncelik sırası (yüksek önce)',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (whatsapp_number_id) REFERENCES whatsapp_numbers(id) ON DELETE CASCADE,
    KEY idx_auto_replies_tenant (tenant_id),
    KEY idx_auto_replies_active (is_active),
    KEY idx_auto_replies_priority (priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Otomatik cevap geçmişi
CREATE TABLE IF NOT EXISTS auto_reply_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    auto_reply_id BIGINT UNSIGNED NOT NULL,
    message_id BIGINT UNSIGNED NOT NULL COMMENT 'Tetikleyen mesaj',
    sent_message_id BIGINT UNSIGNED NULL COMMENT 'Gönderilen cevap mesajı',
    triggered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (auto_reply_id) REFERENCES auto_replies(id) ON DELETE CASCADE,
    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    FOREIGN KEY (sent_message_id) REFERENCES messages(id) ON DELETE SET NULL,
    KEY idx_auto_reply_logs_triggered (triggered_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sistem ayarları
CREATE TABLE IF NOT EXISTS system_settings (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NOT NULL,
    description TEXT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_system_settings_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Warming scheduler durumu
CREATE TABLE IF NOT EXISTS warming_scheduler_state (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    whatsapp_number_id BIGINT UNSIGNED NOT NULL,
    next_scheduled_at TIMESTAMP NOT NULL,
    last_executed_at TIMESTAMP NULL DEFAULT NULL,
    execution_count INT UNSIGNED NOT NULL DEFAULT 0,
    is_paused BOOLEAN NOT NULL DEFAULT FALSE,
    FOREIGN KEY (whatsapp_number_id) REFERENCES whatsapp_numbers(id) ON DELETE CASCADE,
    UNIQUE KEY uk_warming_scheduler_number (whatsapp_number_id),
    KEY idx_warming_scheduler_next (next_scheduled_at, is_paused)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- API anahtarları (webhook ve dış entegrasyonlar için)
CREATE TABLE IF NOT EXISTS api_keys (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    key_name VARCHAR(100) NOT NULL,
    api_key VARCHAR(64) NOT NULL,
    permissions JSON NULL COMMENT 'İzinler: ["send_message", "read_messages", vb]',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    last_used_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    UNIQUE KEY uk_api_keys_key (api_key),
    KEY idx_api_keys_tenant (tenant_id),
    KEY idx_api_keys_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Aktivite logları
CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    tenant_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL COMMENT 'login, send_message, add_number, vb',
    entity_type VARCHAR(50) NULL COMMENT 'campaign, contact, number, vb',
    entity_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    details JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL,
    KEY idx_activity_logs_user (user_id),
    KEY idx_activity_logs_tenant (tenant_id),
    KEY idx_activity_logs_action (action),
    KEY idx_activity_logs_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;