-- =====================================================
-- Telegram Earning Bot - Database Schema
-- PHP 8.1 + MySQL (cPanel shared hosting compatible)
-- =====================================================
-- ইঞ্জিনঃ InnoDB (transaction support + foreign keys দরকার
-- কয়েন movement সব সময় atomic হতে হবে)

SET FOREIGN_KEY_CHECKS = 0;

-- -----------------------------------------------------
-- 1. USERS
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id`                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `telegram_id`       BIGINT UNSIGNED NOT NULL UNIQUE,
    `username`          VARCHAR(64)  DEFAULT NULL,
    `first_name`        VARCHAR(128) DEFAULT NULL,
    `last_name`         VARCHAR(128) DEFAULT NULL,
    `language_code`     VARCHAR(8)   DEFAULT NULL,

    `balance`           DECIMAL(18,4) NOT NULL DEFAULT 0.0000,   -- current coin balance
    `total_earned`      DECIMAL(18,4) NOT NULL DEFAULT 0.0000,   -- lifetime earning (stats)
    `total_withdrawn`   DECIMAL(18,4) NOT NULL DEFAULT 0.0000,

    `referrer_id`       BIGINT UNSIGNED DEFAULT NULL,             -- users.id (who invited this user)
    `referral_code`     VARCHAR(32) NOT NULL UNIQUE,              -- own share code
    `is_referral_active`TINYINT(1) NOT NULL DEFAULT 0,            -- becomes 1 only after force-join verified

    `is_joined_channels` TINYINT(1) NOT NULL DEFAULT 0,           -- cached force-join check result
    `is_bot_started`     TINYINT(1) NOT NULL DEFAULT 0,           -- manual "start" button pressed

    `daily_streak`      INT UNSIGNED NOT NULL DEFAULT 0,
    `last_daily_claim`  DATETIME DEFAULT NULL,

    `level`             VARCHAR(32) NOT NULL DEFAULT 'bronze',    -- bronze/silver/gold etc

    `device_hash`       VARCHAR(64) DEFAULT NULL,                 -- multi-account fraud detection
    `ip_address`         VARCHAR(45) DEFAULT NULL,

    `status`            ENUM('active','banned','suspended') NOT NULL DEFAULT 'active',
    `ban_reason`        VARCHAR(255) DEFAULT NULL,

    `created_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_referrer (`referrer_id`),
    INDEX idx_device (`device_hash`),
    INDEX idx_status (`status`),
    CONSTRAINT fk_users_referrer FOREIGN KEY (`referrer_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
-- 2. FORCE-JOIN CHANNELS / GROUPS (admin editable)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `force_join_channels` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `type`          ENUM('channel','group') NOT NULL,
    `title`         VARCHAR(128) NOT NULL,
    `chat_id`       VARCHAR(64)  NOT NULL,          -- telegram chat_id or @username
    `invite_link`   VARCHAR(255) NOT NULL,
    `is_active`     TINYINT(1) NOT NULL DEFAULT 1,
    `sort_order`    INT NOT NULL DEFAULT 0,
    `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
-- 3. GLOBAL SETTINGS (key-value, admin editable, cached in app)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
    `setting_key`   VARCHAR(64) PRIMARY KEY,
    `setting_value` TEXT NOT NULL,
    `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- default rows (উদাহরণ, app এ config হিসেবে ইউজ হবে)
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('coin_to_currency_rate', '1000'),        -- e.g. 1000 coin = 1 USD/BDT (admin editable)
('currency_symbol', '৳'),
('min_withdraw_coin', '5000'),
('daily_reward_base_coin', '10'),
('daily_reward_enabled', '1'),
('referral_bonus_coin', '50'),
('referral_min_task_required', '1'),      -- referred user কে কমপক্ষে কয়টা টাস্ক করতে হবে
('ads_earning_enabled', '0'),
('ads_coin_per_view', '2'),
('withdraw_methods', 'bkash,nagad,rocket')
ON DUPLICATE KEY UPDATE `setting_key`=`setting_key`;

-- -----------------------------------------------------
-- 4. TASKS
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `tasks` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `title`         VARCHAR(128) NOT NULL,
    `description`   TEXT DEFAULT NULL,
    `type`          ENUM('join_channel','visit_link','app_install','custom') NOT NULL DEFAULT 'custom',
    `target_url`    VARCHAR(255) DEFAULT NULL,
    `reward_coin`   DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
    `max_completions` INT UNSIGNED DEFAULT NULL,      -- NULL = unlimited (total across all users)
    `is_active`     TINYINT(1) NOT NULL DEFAULT 1,
    `expires_at`    DATETIME DEFAULT NULL,
    `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `task_completions` (
    `id`            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `task_id`       INT UNSIGNED NOT NULL,
    `user_id`       BIGINT UNSIGNED NOT NULL,
    `reward_coin`   DECIMAL(18,4) NOT NULL,
    `status`        ENUM('pending','approved','rejected') NOT NULL DEFAULT 'approved',
    `completed_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uniq_task_user (`task_id`, `user_id`),   -- এক ইউজার এক টাস্ক একবারই
    INDEX idx_user (`user_id`),
    CONSTRAINT fk_tc_task FOREIGN KEY (`task_id`) REFERENCES `tasks`(`id`) ON DELETE CASCADE,
    CONSTRAINT fk_tc_user FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
-- 5. REFERRALS (relationship + verification tracking)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `referrals` (
    `id`                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `referrer_id`       BIGINT UNSIGNED NOT NULL,
    `referred_id`       BIGINT UNSIGNED NOT NULL UNIQUE,   -- একজন referred শুধু একবারই কারো referral
    `is_verified`       TINYINT(1) NOT NULL DEFAULT 0,     -- force-join + min task complete হলে 1
    `bonus_coin`        DECIMAL(18,4) DEFAULT NULL,
    `bonus_credited`    TINYINT(1) NOT NULL DEFAULT 0,
    `created_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `verified_at`       DATETIME DEFAULT NULL,

    INDEX idx_referrer (`referrer_id`),
    CONSTRAINT fk_ref_referrer FOREIGN KEY (`referrer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT fk_ref_referred FOREIGN KEY (`referred_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
-- 6. DAILY REWARD CLAIMS (history/log)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `daily_reward_claims` (
    `id`            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`       BIGINT UNSIGNED NOT NULL,
    `coin_awarded`  DECIMAL(18,4) NOT NULL,
    `streak_day`    INT UNSIGNED NOT NULL DEFAULT 1,
    `claimed_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_user_date (`user_id`, `claimed_at`),
    CONSTRAINT fk_drc_user FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
-- 7. ADS VIEWS (optional earning source)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ads_views` (
    `id`            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`       BIGINT UNSIGNED NOT NULL,
    `coin_awarded`  DECIMAL(18,4) NOT NULL,
    `viewed_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_user_date (`user_id`, `viewed_at`),
    CONSTRAINT fk_av_user FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
-- 8. TRANSACTIONS (master audit log for EVERY coin movement)
-- -----------------------------------------------------
-- Balance-related সব কাজ (daily reward, task, referral bonus,
-- ads, withdraw deduction, admin manual adjust) এই টেবিলে লগ হবে।
-- এতে balance mismatch হলে ট্রেস করা সহজ হয়।
CREATE TABLE IF NOT EXISTS `transactions` (
    `id`            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`       BIGINT UNSIGNED NOT NULL,
    `type`          ENUM('daily_reward','task_reward','referral_bonus','ads_reward',
                          'withdraw','admin_adjust','refund') NOT NULL,
    `amount`        DECIMAL(18,4) NOT NULL,           -- +ve credit, -ve debit
    `balance_after` DECIMAL(18,4) NOT NULL,
    `reference_id`  BIGINT UNSIGNED DEFAULT NULL,     -- related withdraw/task/referral id
    `note`          VARCHAR(255) DEFAULT NULL,
    `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_user_date (`user_id`, `created_at`),
    INDEX idx_type (`type`),
    CONSTRAINT fk_txn_user FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
-- 9. WITHDRAWALS
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `withdrawals` (
    `id`                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`           BIGINT UNSIGNED NOT NULL,
    `coin_amount`       DECIMAL(18,4) NOT NULL,
    `currency_amount`   DECIMAL(18,4) NOT NULL,       -- coin_amount / rate at request time
    `method`            ENUM('bkash','nagad','rocket') NOT NULL,
    `account_number`    VARCHAR(32) NOT NULL,
    `status`            ENUM('pending','approved','rejected','paid') NOT NULL DEFAULT 'pending',
    `admin_note`        VARCHAR(255) DEFAULT NULL,
    `processed_by`      INT UNSIGNED DEFAULT NULL,    -- admins.id
    `requested_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `processed_at`      DATETIME DEFAULT NULL,

    INDEX idx_user (`user_id`),
    INDEX idx_status (`status`),
    CONSTRAINT fk_wd_user FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
-- 10. ADMINS
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `admins` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username`      VARCHAR(64) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `role`          ENUM('super_admin','moderator') NOT NULL DEFAULT 'moderator',
    `last_login`    DATETIME DEFAULT NULL,
    `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
-- 11. BROADCAST LOGS (mass message history)
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `broadcast_logs` (
    `id`            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `admin_id`      INT UNSIGNED NOT NULL,
    `message`       TEXT NOT NULL,
    `total_sent`    INT UNSIGNED NOT NULL DEFAULT 0,
    `total_failed`  INT UNSIGNED NOT NULL DEFAULT 0,
    `sent_at`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

SET FOREIGN_KEY_CHECKS = 1;