-- ================================================================
-- Telegram Referral & Reward Bot - Database Schema
-- Engine: MySQL 8.0+ / MariaDB 10.5+
-- Charset: utf8mb4
-- ================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------
-- Table: admins
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(64) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(128) DEFAULT NULL,
  `role` ENUM('super_admin','admin') NOT NULL DEFAULT 'admin',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_login_at` DATETIME DEFAULT NULL,
  `last_login_ip` VARCHAR(45) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_admins_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Table: users
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` BIGINT NOT NULL,
  `username` VARCHAR(64) DEFAULT NULL,
  `first_name` VARCHAR(128) DEFAULT NULL,
  `last_name` VARCHAR(128) DEFAULT NULL,
  `balance` DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
  `total_earned` DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
  `referred_by` BIGINT UNSIGNED DEFAULT NULL COMMENT 'users.id of referrer',
  `referral_reward_paid` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'whether referrer was already paid for THIS user',
  `is_verified` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'passed force-join check at least once',
  `is_banned` TINYINT(1) NOT NULL DEFAULT 0,
  `ban_reason` VARCHAR(255) DEFAULT NULL,
  `last_bonus_at` DATETIME DEFAULT NULL,
  `state` VARCHAR(64) DEFAULT NULL COMMENT 'conversation state machine (e.g. awaiting_withdraw_address)',
  `state_payload` TEXT DEFAULT NULL COMMENT 'JSON scratch data for the active conversation state',
  `joined_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_telegram_id` (`telegram_id`),
  KEY `idx_users_referred_by` (`referred_by`),
  CONSTRAINT `fk_users_referred_by` FOREIGN KEY (`referred_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Table: channels  (force-join channels/groups)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `channels` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` ENUM('channel','group') NOT NULL DEFAULT 'channel',
  `chat_id` VARCHAR(64) NOT NULL COMMENT 'Telegram numeric chat id, e.g. -1001234567890',
  `username` VARCHAR(64) DEFAULT NULL COMMENT '@username without @, nullable for private chats',
  `title` VARCHAR(128) DEFAULT NULL,
  `invite_link` VARCHAR(255) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_channels_chat_id` (`chat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Table: referrals
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `referrals` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `referrer_id` BIGINT UNSIGNED NOT NULL COMMENT 'users.id who owns the link',
  `referred_id` BIGINT UNSIGNED NOT NULL COMMENT 'users.id who was invited',
  `status` ENUM('pending','rewarded') NOT NULL DEFAULT 'pending',
  `reward_amount` DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
  `rewarded_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_referrals_referred` (`referred_id`) COMMENT 'a user can only ever be referred once',
  KEY `idx_referrals_referrer` (`referrer_id`),
  CONSTRAINT `fk_referrals_referrer` FOREIGN KEY (`referrer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_referrals_referred` FOREIGN KEY (`referred_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Table: tasks
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tasks` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` ENUM('join_channel','join_group','visit_website','custom') NOT NULL DEFAULT 'custom',
  `title` VARCHAR(128) NOT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `target` VARCHAR(255) DEFAULT NULL COMMENT 'channel username / URL depending on type',
  `reward_amount` DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Table: task_completions
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `task_completions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `task_id` INT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `status` ENUM('pending_review','approved','rejected') NOT NULL DEFAULT 'approved',
  `reward_amount` DECIMAL(20,4) NOT NULL DEFAULT 0.0000,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_task_user` (`task_id`,`user_id`),
  KEY `idx_tc_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;

-- ----------------------------------------------------------------
-- Table: withdraws
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `withdraws` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `wallet_address` VARCHAR(255) NOT NULL,
  `network` VARCHAR(64) NOT NULL,
  `amount` DECIMAL(20,4) NOT NULL,
  `status` ENUM('pending','approved','rejected','paid') NOT NULL DEFAULT 'pending',
  `admin_note` VARCHAR(255) DEFAULT NULL,
  `processed_by` INT UNSIGNED DEFAULT NULL COMMENT 'admins.id',
  `processed_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_withdraws_user` (`user_id`),
  KEY `idx_withdraws_status` (`status`),
  CONSTRAINT `fk_withdraws_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Table: bonus_claims
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bonus_claims` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `amount` DECIMAL(20,4) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_bonus_user` (`user_id`),
  CONSTRAINT `fk_bonus_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Table: transactions (single source of truth ledger for all balance changes)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `type` ENUM('referral','bonus','task','withdraw','admin_credit','admin_debit') NOT NULL,
  `amount` DECIMAL(20,4) NOT NULL COMMENT 'positive = credit, negative = debit',
  `balance_after` DECIMAL(20,4) NOT NULL,
  `reference_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'id in the related table (referrals, withdraws, etc)',
  `note` VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tx_user` (`user_id`),
  KEY `idx_tx_type` (`type`),
  CONSTRAINT `fk_tx_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Table: settings (key-value store)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `setting_key` VARCHAR(64) NOT NULL,
  `setting_value` TEXT DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Table: logs
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `level` ENUM('info','warning','error') NOT NULL DEFAULT 'info',
  `channel` VARCHAR(64) NOT NULL DEFAULT 'app' COMMENT 'e.g. webhook, admin, cron',
  `message` TEXT NOT NULL,
  `context` TEXT DEFAULT NULL COMMENT 'JSON',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_logs_channel` (`channel`),
  KEY `idx_logs_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Table: broadcasts
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `broadcasts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id` INT UNSIGNED DEFAULT NULL,
  `content_type` ENUM('text','photo','video','document') NOT NULL DEFAULT 'text',
  `text` TEXT DEFAULT NULL,
  `file_id` VARCHAR(255) DEFAULT NULL,
  `buttons` TEXT DEFAULT NULL COMMENT 'JSON array of {text,url}',
  `total_targets` INT UNSIGNED NOT NULL DEFAULT 0,
  `sent_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `failed_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `status` ENUM('queued','running','completed','failed') NOT NULL DEFAULT 'queued',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ================================================================
-- Seed data
-- ================================================================

INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('bot_name', 'SHIB Reward Bot'),
('bot_username', 'YourBotUsername'),
('currency_name', 'SHIB Coin'),
('currency_symbol', 'SHIB'),
('referral_reward', '1000'),
('daily_bonus_amount', '50'),
('min_withdraw', '5000'),
('force_join_enabled', '1'),
('support_username', 'YourSupportUsername'),
('webhook_secret', '')
ON DUPLICATE KEY UPDATE `setting_key` = `setting_key`;

-- No admin account is seeded here on purpose. Run public/install.php after
-- importing this file — it lets you set your own admin username/password
-- and writes a real bcrypt hash, so a known default credential is never
-- sitting in your database or in this repo.