-- =====================================================
-- Migration v3: Channel/Group Promotion Network
-- চ্যানেল/গ্রুপ owner রা তাদের চ্যানেল রেজিস্টার করবে (bot + owner দুজনেই admin
-- থাকতে হবে ভেরিফিকেশনের জন্য), অ্যাডমিন promo message পাঠালে random
-- নির্বাচিত চ্যানেলে পোস্ট যাবে, owner কয়েন পাবে + নোটিফিকেশন পাবে।
-- =====================================================

CREATE TABLE IF NOT EXISTS `promo_channels` (
    `id`             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `owner_user_id`  BIGINT UNSIGNED NOT NULL,
    `chat_id`        VARCHAR(64) NOT NULL,
    `title`          VARCHAR(128) NOT NULL,
    `type`           ENUM('channel','group') NOT NULL,
    `coin_per_post`  DECIMAL(18,4) NOT NULL DEFAULT 0,
    `status`         ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    `is_active`      TINYINT(1) NOT NULL DEFAULT 1,
    `added_at`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uniq_chat (`chat_id`),
    INDEX idx_owner (`owner_user_id`),
    INDEX idx_status_active (`status`, `is_active`),
    CONSTRAINT fk_promo_owner FOREIGN KEY (`owner_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `promo_logs` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `admin_id`         INT UNSIGNED NOT NULL,
    `message`          TEXT NOT NULL,
    `requested_count`  INT UNSIGNED NOT NULL,
    `sent_count`       INT UNSIGNED NOT NULL DEFAULT 0,
    `failed_count`     INT UNSIGNED NOT NULL DEFAULT 0,
    `total_coin_paid`  DECIMAL(18,4) NOT NULL DEFAULT 0,
    `sent_at`          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_promolog_admin FOREIGN KEY (`admin_id`) REFERENCES `admins`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `promo_log_channels` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `promo_log_id`     INT UNSIGNED NOT NULL,
    `promo_channel_id` INT UNSIGNED NOT NULL,
    `status`           ENUM('sent','failed') NOT NULL,
    `coin_paid`        DECIMAL(18,4) NOT NULL DEFAULT 0,

    CONSTRAINT fk_plc_log FOREIGN KEY (`promo_log_id`) REFERENCES `promo_logs`(`id`) ON DELETE CASCADE,
    CONSTRAINT fk_plc_channel FOREIGN KEY (`promo_channel_id`) REFERENCES `promo_channels`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- transactions.type এ নতুন টাইপ যোগ (চ্যানেল owner এর promo আয়)
ALTER TABLE `transactions`
    MODIFY COLUMN `type` ENUM('daily_reward','task_reward','referral_bonus','ads_reward',
                               'withdraw','admin_adjust','refund','promo_income') NOT NULL;

INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('promo_coin_per_post', '20')   -- ডিফল্ট রেট, প্রতি চ্যানেলে পরে আলাদাও এডিট করা যাবে
ON DUPLICATE KEY UPDATE `setting_key`=`setting_key`;