-- ============================================================
-- borsayazilim.com V2 — Telegram Bot Migration
-- Production DB: borsayazilim_borsa
-- 
-- Bu migration production DB'sine bot tablolarını ekler.
-- Mevcut production tablolarına DOKUNMAZ.
-- ============================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ============================================================
-- 1. TELEGRAM USERS (Bot'un takip ettiği Telegram kullanıcıları)
--    Production'daki `users` tablosuyla karışmaması için
--    `telegram_users` olarak adlandırıldı.
-- ============================================================
CREATE TABLE IF NOT EXISTS `telegram_users` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `telegram_id`   BIGINT NOT NULL UNIQUE,
    `username`      VARCHAR(255) DEFAULT NULL,
    `first_name`    VARCHAR(255) DEFAULT NULL,
    `last_name`     VARCHAR(255) DEFAULT NULL,
    `is_premium`    TINYINT(1) DEFAULT 0,
    `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. GROUPS (Telegram Grupları)
-- ============================================================
CREATE TABLE IF NOT EXISTS `groups` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `chat_id`       BIGINT NOT NULL UNIQUE,
    `title`         VARCHAR(255) NOT NULL,
    `type`          ENUM('group','supergroup','channel') DEFAULT 'supergroup',
    `is_active`     TINYINT(1) DEFAULT 1,
    `group_role`    ENUM('saha','site') DEFAULT 'site',
    `group_key`     VARCHAR(100) DEFAULT NULL,
    `komisyon_orani` DECIMAL(5,2) DEFAULT 0.00,
    `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. USER_ROLES (Telegram Kullanıcı - Grup Rolleri)
-- ============================================================
CREATE TABLE IF NOT EXISTS `user_roles` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`       INT UNSIGNED NOT NULL,
    `group_id`      INT UNSIGNED NOT NULL,
    `role`          ENUM('super_admin','admin','analyst','viewer','blocked') DEFAULT 'viewer',
    `assigned_by`   INT UNSIGNED DEFAULT NULL,
    `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_user_group` (`user_id`, `group_id`),
    FOREIGN KEY (`user_id`) REFERENCES `telegram_users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. MESSAGES (Gelen Mesajlar)
-- ============================================================
CREATE TABLE IF NOT EXISTS `messages` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `message_id`        BIGINT NOT NULL,
    `chat_id`           BIGINT NOT NULL,
    `user_id`           INT UNSIGNED DEFAULT NULL,
    `text`              TEXT DEFAULT NULL,
    `message_type`      ENUM('text','command','query','response') DEFAULT 'text',
    `is_processed`      TINYINT(1) DEFAULT 0,
    `received_at`       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_chat_id` (`chat_id`),
    INDEX `idx_user_id` (`user_id`),
    FOREIGN KEY (`user_id`) REFERENCES `telegram_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. SQL_QUERIES
-- ============================================================
CREATE TABLE IF NOT EXISTS `sql_queries` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `message_id`    INT UNSIGNED DEFAULT NULL,
    `user_id`       INT UNSIGNED DEFAULT NULL,
    `chat_id`       BIGINT NOT NULL,
    `query_text`    TEXT NOT NULL,
    `sql_executed`  TEXT DEFAULT NULL,
    `result_rows`   INT DEFAULT 0,
    `result_data`   LONGTEXT DEFAULT NULL,
    `status`        ENUM('pending','success','error','denied') DEFAULT 'pending',
    `error_message` TEXT DEFAULT NULL,
    `executed_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_chat_id` (`chat_id`),
    INDEX `idx_user_id` (`user_id`),
    FOREIGN KEY (`message_id`) REFERENCES `messages`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`user_id`) REFERENCES `telegram_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. BOT_RESPONSES
-- ============================================================
CREATE TABLE IF NOT EXISTS `bot_responses` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `query_id`          INT UNSIGNED DEFAULT NULL,
    `chat_id`           BIGINT NOT NULL,
    `response_message_id` BIGINT DEFAULT NULL,
    `response_text`     TEXT NOT NULL,
    `sent_at`           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`query_id`) REFERENCES `sql_queries`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. BOT_COMMANDS
-- ============================================================
CREATE TABLE IF NOT EXISTS `bot_commands` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `command`       VARCHAR(100) NOT NULL UNIQUE,
    `description`   VARCHAR(255) NOT NULL,
    `min_role`      ENUM('super_admin','admin','analyst','viewer') DEFAULT 'viewer',
    `is_active`     TINYINT(1) DEFAULT 1,
    `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `bot_commands` (`command`, `description`, `min_role`) VALUES
    ('/start',    'Botu başlat ve yardım al',           'viewer'),
    ('/help',     'Komutları listele',                   'viewer'),
    ('/iban',     'Tutara göre IBAN al ve ödeme yap',    'viewer'),
    ('/myrole',   'Kendi rolünü öğren',                  'viewer'),
    ('/groups',   'Bot gruplarını listele (admin)',      'admin'),
    ('/ozet',     'Bugünkü kasa özeti',                  'viewer'),
    ('/ciro',     'Ciro özeti (gelir, komisyon, alacak)','viewer'),
    ('/gunsonu',  'Gün sonu raporu',                     'admin'),
    ('/rapor',    'Gün sonu raporu',                     'admin');

-- ============================================================
-- 8. BLOCKED_USERS
-- ============================================================
CREATE TABLE IF NOT EXISTS `blocked_users` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `telegram_id`   BIGINT NOT NULL,
    `chat_id`       BIGINT NOT NULL,
    `blocked_by`    INT UNSIGNED DEFAULT NULL,
    `reason`        VARCHAR(255) DEFAULT NULL,
    `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_blocked` (`telegram_id`, `chat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. IBANS (Eski IBAN havuzu - uyumluluk için)
-- ============================================================
CREATE TABLE IF NOT EXISTS `ibans` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `bank_name`       VARCHAR(100) NOT NULL,
    `account_name`    VARCHAR(255) NOT NULL,
    `iban_number`     VARCHAR(34) NOT NULL UNIQUE,
    `min_limit`       DECIMAL(15,2) DEFAULT 0.00,
    `max_limit`       DECIMAL(15,2) DEFAULT 999999.00,
    `daily_limit`     DECIMAL(15,2) DEFAULT NULL,
    `total_received`  DECIMAL(15,2) DEFAULT 0.00,
    `daily_received`  DECIMAL(15,2) DEFAULT 0.00,
    `last_reset_date` DATE DEFAULT NULL,
    `is_active`       TINYINT(1) DEFAULT 1,
    `notes`           TEXT DEFAULT NULL,
    `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_active_limits` (`is_active`, `min_limit`, `max_limit`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. IBAN_TRANSACTIONS (Ödeme kayıtları)
-- ============================================================
CREATE TABLE IF NOT EXISTS `iban_transactions` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `iban_id`        INT UNSIGNED DEFAULT NULL,
    `pool_iban_id`   INT UNSIGNED DEFAULT NULL,
    `borsa_user_id`  INT UNSIGNED DEFAULT NULL,
    `user_id`        INT UNSIGNED DEFAULT NULL,
    `chat_id`        BIGINT NOT NULL,
    `bot_message_id` BIGINT DEFAULT NULL,
    `saha_chat_id`   BIGINT DEFAULT NULL,
    `saha_message_id` BIGINT DEFAULT NULL,
    `amount`         DECIMAL(15,2) NOT NULL,
    `status`         ENUM('pending','confirmed','cancelled','expired','claimed','rejected') DEFAULT 'pending',
    `approver_id`    BIGINT DEFAULT NULL,
    `notes`          TEXT DEFAULT NULL,
    `requested_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `approved_at`    TIMESTAMP NULL DEFAULT NULL,
    INDEX `idx_status` (`status`),
    INDEX `idx_chat`   (`chat_id`),
    INDEX `idx_pool`   (`pool_iban_id`),
    INDEX `idx_borsa`  (`borsa_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 11. POOL_IBANS (Borsa kullanıcılarının IBAN havuzu)
-- ============================================================
CREATE TABLE IF NOT EXISTS `pool_ibans` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `borsa_user_id`   INT UNSIGNED NOT NULL,
    `bank_name`       VARCHAR(100) NOT NULL,
    `account_holder`  VARCHAR(255) NOT NULL,
    `iban_number`     VARCHAR(34) NOT NULL,
    `min_limit`       DECIMAL(15,2) DEFAULT 0.00,
    `max_limit`       DECIMAL(15,2) DEFAULT 999999.00,
    `daily_limit`     DECIMAL(15,2) DEFAULT 0.00,
    `daily_received`  DECIMAL(15,2) DEFAULT 0.00,
    `total_received`  DECIMAL(15,2) DEFAULT 0.00,
    `last_reset_date` DATE DEFAULT NULL,
    `is_active`       TINYINT(1) DEFAULT 1,
    `notes`           TEXT DEFAULT NULL,
    `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_borsa_user` (`borsa_user_id`),
    INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 12. POOL_USER_GROUPS (Borsa kullanıcısı - Grup ataması)
-- ============================================================
CREATE TABLE IF NOT EXISTS `pool_user_groups` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `borsa_user_id`   INT UNSIGNED NOT NULL,
    `group_id`        INT UNSIGNED NOT NULL,
    `commission_rate` DECIMAL(5,2) DEFAULT 0.00,
    `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_borsa_group` (`borsa_user_id`, `group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 13. GROUP_TRANSACTIONS (Grup bazlı işlem kayıtları)
-- ============================================================
CREATE TABLE IF NOT EXISTS `group_transactions` (
    `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `borsa_user_id`       INT UNSIGNED NOT NULL,
    `group_id`            INT UNSIGNED NOT NULL,
    `iban_transaction_id` INT UNSIGNED DEFAULT NULL,
    `description`         VARCHAR(255) DEFAULT NULL,
    `gross_amount`        DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `commission_rate`     DECIMAL(5,2) DEFAULT 0.00,
    `commission_amount`   DECIMAL(15,2) DEFAULT 0.00,
    `net_amount`          DECIMAL(15,2) DEFAULT 0.00,
    `status`              ENUM('completed','pending','cancelled') DEFAULT 'completed',
    `created_at`          DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_group`     (`group_id`),
    INDEX `idx_borsa`     (`borsa_user_id`),
    INDEX `idx_date`      (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 14. CARI_KAYITLARI (Cari hesap kayıtları)
-- ============================================================
CREATE TABLE IF NOT EXISTS `cari_kayitlari` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `borsa_user_id`   INT UNSIGNED NOT NULL,
    `group_id`        INT UNSIGNED NOT NULL,
    `tutar`           DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `tip`             ENUM('borc','alacak','odeme') NOT NULL DEFAULT 'borc',
    `aciklama`        VARCHAR(255) DEFAULT NULL,
    `tarih`           DATE DEFAULT NULL,
    `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 15. REMINDERS (Bekleyen işlem hatırlatmaları)
-- ============================================================
CREATE TABLE IF NOT EXISTS `reminders` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `transaction_id`  INT UNSIGNED NOT NULL,
    `chat_id`         BIGINT NOT NULL,
    `message_id`      BIGINT DEFAULT NULL,
    `reminder_count`  INT DEFAULT 0,
    `last_sent_at`    TIMESTAMP NULL DEFAULT NULL,
    `created_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 16. WITHDRAWALS (Çekim talepleri)
-- ============================================================
CREATE TABLE IF NOT EXISTS `withdrawals` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`         INT UNSIGNED DEFAULT NULL,
    `chat_id`         BIGINT NOT NULL,
    `amount`          DECIMAL(15,2) NOT NULL,
    `status`          ENUM('pending','processing','completed','failed','cancelled') DEFAULT 'pending',
    `bank_name`       VARCHAR(100) DEFAULT NULL,
    `iban_number`     VARCHAR(34) DEFAULT NULL,
    `account_holder`  VARCHAR(255) DEFAULT NULL,
    `tx_hash`         VARCHAR(200) DEFAULT NULL,
    `notes`           TEXT DEFAULT NULL,
    `requested_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `completed_at`    TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;

-- ============================================================
-- NOT: Production `users` tablosu zaten mevcut.
-- Bot kodundaki `borsa_users` referansları production'da
-- `users` tablosuna yönlendirilecektir (Database.php'de).
-- ============================================================

-- ============================================================
-- 17. BOT TOKEN AYARI
-- ============================================================
INSERT IGNORE INTO system_settings (setting_key, setting_value, description) VALUES
('telegram_bot_token', '8998113346:AAG6WrBg17TQnb-JvBEv6mwDqD8QI9s_0cE', 'V2 Bot token');