-- =====================================================
-- Telegram Bot Admin Panel - Veritabanı Şeması
-- Versiyon: 1.0
-- Tarih: 2026-01-18
-- =====================================================

SET CHARACTER SET utf8mb4;

-- -----------------------------------------------------
-- Yetkili Admin Kullanıcıları (Telegram Nick Whitelist)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `admin_users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `telegram_user_id` BIGINT UNSIGNED NULL UNIQUE COMMENT 'İlk /start sonrası dolar',
    `telegram_username` VARCHAR(100) NOT NULL UNIQUE COMMENT 'Nick (@ olmadan)',
    `display_name` VARCHAR(255) NULL COMMENT 'Görünen isim',
    `is_active` TINYINT(1) DEFAULT 1 COMMENT '1=Aktif, 0=Pasif',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_username` (`telegram_username`),
    INDEX `idx_user_id` (`telegram_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Bot'un Eklendiği Kanallar
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `channels` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `chat_id` BIGINT NOT NULL UNIQUE COMMENT 'Telegram chat_id (negatif)',
    `title` VARCHAR(255) NOT NULL COMMENT 'Kanal başlığı',
    `username` VARCHAR(100) NULL COMMENT 'Kanal username (@ olmadan)',
    `type` ENUM('channel', 'supergroup', 'group') DEFAULT 'channel',
    `is_active` TINYINT(1) DEFAULT 1 COMMENT 'Bot hala admin mi?',
    `added_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_chat_id` (`chat_id`),
    INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Postlar
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `posts` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `channel_id` INT UNSIGNED NOT NULL COMMENT 'channels.id referans',
    `internal_title` VARCHAR(255) NOT NULL COMMENT 'Admin için dahili başlık',
    `media_type` ENUM('text', 'photo') DEFAULT 'photo',
    `media_path` VARCHAR(500) NULL COMMENT 'Lokal dosya yolu (storage/uploads/...)',
    `media_url` VARCHAR(1000) NULL COMMENT 'Harici medya URL',
    `media_file_id` VARCHAR(255) NULL COMMENT 'Telegram file_id (önbellekleme için)',
    `caption` TEXT NULL COMMENT 'Post metni (HTML)',
    `buttons_json` JSON NULL COMMENT 'Buton yapısı [[{text,url,id},...],...]',
    `status` ENUM('draft', 'previewed', 'approved', 'scheduled', 'sent', 'cancelled', 'failed') DEFAULT 'draft',
    `created_by_user_id` BIGINT UNSIGNED NOT NULL COMMENT 'Oluşturan admin telegram_user_id',
    `approved_by_user_id` BIGINT UNSIGNED NULL COMMENT 'Onaylayan admin',
    `preview_message_id` BIGINT NULL COMMENT 'Önizleme mesajının message_id',
    `preview_chat_id` BIGINT NULL COMMENT 'Önizleme gönderilen chat',
    `scheduled_at` DATETIME NULL COMMENT 'Planlanan gönderim zamanı',
    `sent_at` DATETIME NULL COMMENT 'Gerçek gönderim zamanı',
    `sent_message_id` BIGINT NULL COMMENT 'Kanala gönderilen mesajın ID',
    `error_message` TEXT NULL COMMENT 'Hata durumunda detay',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_channel` (`channel_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_scheduled` (`status`, `scheduled_at`),
    INDEX `idx_created_by` (`created_by_user_id`),
    FOREIGN KEY (`channel_id`) REFERENCES `channels`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Post İşlem Logları (Audit Trail)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `post_logs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT UNSIGNED NOT NULL,
    `action` ENUM('create', 'update', 'preview', 'approve', 'schedule', 'send', 'cancel', 'fail') NOT NULL,
    `info` TEXT NULL COMMENT 'Ek bilgi (JSON veya metin)',
    `telegram_user_id` BIGINT UNSIGNED NULL COMMENT 'İşlemi yapan admin',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_post` (`post_id`),
    INDEX `idx_action` (`action`),
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Buton Tıklama Logları
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `click_logs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `post_id` INT UNSIGNED NOT NULL,
    `button_id` VARCHAR(50) NOT NULL COMMENT 'Buton ID (buttons_json içindeki)',
    `button_text` VARCHAR(255) NULL,
    `target_url` VARCHAR(1000) NULL,
    `clicker_user_id` BIGINT UNSIGNED NOT NULL,
    `clicker_username` VARCHAR(100) NULL,
    `clicker_first_name` VARCHAR(255) NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_post` (`post_id`),
    INDEX `idx_button` (`button_id`),
    INDEX `idx_clicker` (`clicker_user_id`),
    INDEX `idx_date` (`created_at`),
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Kullanıcı State Yönetimi (Conversation State)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_states` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `telegram_user_id` BIGINT UNSIGNED NOT NULL UNIQUE,
    `state` VARCHAR(50) DEFAULT 'idle' COMMENT 'Mevcut durum',
    `state_data` JSON NULL COMMENT 'Durum ile ilgili veriler',
    `current_post_id` INT UNSIGNED NULL COMMENT 'Üzerinde çalışılan post',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_user` (`telegram_user_id`),
    INDEX `idx_state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Bekleyen Aksiyonlar (Schedule input vb.)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pending_actions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `telegram_user_id` BIGINT UNSIGNED NOT NULL,
    `action_type` VARCHAR(50) NOT NULL COMMENT 'schedule_post, add_button, edit_button vb.',
    `action_payload` JSON NULL COMMENT 'Aksiyon parametreleri',
    `expires_at` DATETIME NULL COMMENT 'Geçerlilik süresi',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user_action` (`telegram_user_id`, `action_type`),
    INDEX `idx_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------
-- Varsayılan Admin Kullanıcısı Ekle
-- Not: Kendi Telegram username'inizi buraya ekleyin
-- -----------------------------------------------------
-- INSERT INTO `admin_users` (`telegram_username`, `display_name`, `is_active`) VALUES
-- ('your_username', 'Admin', 1);

-- =====================================================
-- NOTLAR:
-- 1. Veritabanı oluşturulduktan sonra bu SQL'i çalıştırın
-- 2. admin_users tablosuna kendi Telegram username'inizi ekleyin
-- 3. UTF8MB4 charset emoji desteği için gereklidir
-- =====================================================
