-- ==========================================================
-- Earning Bot — Fresh Install SQL
-- ==========================================================
-- This is a clean, consolidated rebuild of the original dump.
-- Changes made vs. the original faucetpanda_trx_final.sql:
--   1. Merged every later "ALTER TABLE ... ADD COLUMN" patch
--      straight into the CREATE TABLE statements below, so this
--      is a single-pass fresh install (no migrate-then-patch).
--   2. Removed leftover developer test data (real Telegram user
--      rows, tap logs, transactions, two personal channel
--      usernames). A fresh install should start empty.
--   3. Dropped three legacy tables that nothing in the PHP code
--      reads or writes any more: `tasks`, `task_attempts`,
--      `earn_more_items` (superseded by `earning_tasks` /
--      `earning_task_attempts`; the "Earn More" bot button only
--      uses the `earn_more_message` setting, not a table). Also
--      dropped `user_sessions`, which nothing references.
--   4. Removed the unused `reward` column from `earning_tasks`
--      and `daily_bonuses` — only `coin_reward` is used anywhere
--      in the code.
--   5. Added `weight` to spin_segments (was already used by the
--      spin logic) with sane starting values/odds.
--   6. faucetpay_currency is now a dropdown of FaucetPay's supported
--      coins instead of free text, and the "smallest-unit multiplier"
--      setting is gone — FaucetPay uses one fixed internal unit
--      (100,000,000, same convention as satoshis) for every coin, so it
--      was never actually a per-installation setting.
--   7. Added `spin_results.spin_date` (used to enforce the new daily
--      spin limit) and the `spin_daily_limit` / `faucetpay_referral_link`
--      settings.
--
-- Import this into an EMPTY database. Then set DB_NAME/DB_USER/
-- DB_PASS in config.php (and the matching values in database.php)
-- and log into /earningbot/admin/ with admin / admin123 — change
-- that password immediately from Admin -> Settings.
-- ==========================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- --------------------------------------------------------
-- admins
-- --------------------------------------------------------
CREATE TABLE `admins` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin login: admin / admin123 — change this after first login.
INSERT INTO `admins` (`username`, `password_hash`, `active`) VALUES
('admin', '$2y$12$Ks7BSYIGwIoMFhRq4BL/juZPAbum5ZHm.vXZHqgE2Xen8ks7zluDS', 1);

-- --------------------------------------------------------
-- users
-- --------------------------------------------------------
CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` bigint(20) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `faucetpay_email` varchar(255) DEFAULT NULL,
  `balance` decimal(18,8) NOT NULL DEFAULT 0.00000000,
  `total_earned` decimal(18,8) NOT NULL DEFAULT 0.00000000,
  `total_withdrawn` decimal(18,8) NOT NULL DEFAULT 0.00000000,
  `referred_by` bigint(20) DEFAULT NULL,
  `referral_earned` decimal(18,8) NOT NULL DEFAULT 0.00000000,
  `tap_count_today` int(11) NOT NULL DEFAULT 0,
  `tap_date` date DEFAULT NULL,
  `tap_coins_total` decimal(30,8) NOT NULL DEFAULT 0,
  `tap_clicks_total` bigint(20) UNSIGNED NOT NULL DEFAULT 0,
  `tap_energy` decimal(20,8) NOT NULL DEFAULT 1000.00000000,
  `tap_energy_updated_at` datetime DEFAULT NULL,
  `last_bonus_date` date DEFAULT NULL,
  `bonus_streak` int(11) NOT NULL DEFAULT 0,
  `status` enum('active','banned') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `telegram_id` (`telegram_id`),
  KEY `idx_referred_by` (`referred_by`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- settings (key/value store read by getSetting()/setSetting())
-- --------------------------------------------------------
CREATE TABLE `settings` (
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('bot_name', 'Earning Bot'),
('currency', 'USDT'),
('maintenance_mode', '0'),
('site_accent_color', '#ff3b30'),

('tap_enabled', '1'),
('daily_tap_limit', '0'),
('max_taps_per_request', '5'),
('tap_energy_max', '1000'),
('tap_energy_refill', '2'),
('tap_energy_refill_interval', '1'),
('tap_coin_name', 'SHIB'),
('tap_coin_symbol', 'SHIB'),
('tap_coin_logo', ''),
('tap_image_url', ''),
('tap_reward_per_coin', '0.000001'),

('referral_enabled', '1'),
('referral_coin_reward', '100'),

('daily_bonus_enabled', '1'),
('daily_ad_required', '1'),

('spin_enabled', '1'),
('spin_ad_required', '1'),
('spin_daily_limit', '0'),

('monetag_zone_id', ''),
('monetag_sdk_tag', ''),
('auto_ad_interval_seconds', '0'),

('leaderboard_total_ranks', '10'),
('rank_rewards_enabled', '1'),
('rank_reward_date', ''),

('withdrawal_enabled', '1'),
('withdrawal_method', 'faucetpay'),
('minimum_withdrawal', '10000'),
('withdrawal_fee', '0'),

('faucetpay_enabled', '1'),
('faucetpay_api_key', ''),
('faucetpay_currency', 'USDT'),
('faucetpay_referral_link', ''),

('oxapay_enabled', '0'),
('oxapay_payout_api_key', ''),
('oxapay_currency', 'SHIB'),
('oxapay_network', 'BSC'),
('oxapay_callback_url', ''),

('earn_more_message', '<b>Earn More</b>\n\nWatch our latest content and follow our updates. More opportunities will be posted here.');

-- --------------------------------------------------------
-- daily streak
-- --------------------------------------------------------
CREATE TABLE `daily_bonuses` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `day_number` int(10) UNSIGNED NOT NULL,
  `coin_reward` decimal(30,8) NOT NULL DEFAULT 0,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `day_number` (`day_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `daily_bonuses` (`day_number`, `coin_reward`, `active`) VALUES
(1, 1000, 1),
(2, 2000, 1),
(3, 3000, 1),
(4, 5000, 1),
(5, 7500, 1),
(6, 10000, 1),
(7, 20000, 1);

CREATE TABLE `bonus_claims` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `day_number` int(10) UNSIGNED NOT NULL,
  `reward` decimal(18,8) NOT NULL,
  `claimed_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_daily_bonus` (`user_id`,`claimed_date`),
  KEY `idx_user` (`user_id`),
  CONSTRAINT `bonus_claims_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- spin wheel
-- --------------------------------------------------------
CREATE TABLE `spin_segments` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` varchar(100) NOT NULL,
  `coin_reward` decimal(30,12) NOT NULL DEFAULT 0,
  `weight` decimal(12,4) NOT NULL DEFAULT 1,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- weight = odds: higher weight relative to the others wins more often.
INSERT INTO `spin_segments` (`label`, `coin_reward`, `weight`, `active`) VALUES
('100 Coins', 100, 30, 1),
('250 Coins', 250, 22, 1),
('500 Coins', 500, 16, 1),
('1K Coins', 1000, 12, 1),
('2.5K Coins', 2500, 8, 1),
('5K Coins', 5000, 5, 1),
('10K Coins', 10000, 3, 1),
('25K Coins', 25000, 1, 1);

CREATE TABLE `spin_results` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `segment_id` int(10) UNSIGNED NOT NULL,
  `coin_reward` decimal(30,12) NOT NULL,
  `reward_amount` decimal(30,12) NOT NULL,
  `spin_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_spin_user` (`user_id`,`created_at`),
  KEY `idx_spin_user_date` (`user_id`,`spin_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `ad_claim_tokens` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `kind` varchar(20) NOT NULL,
  `token` varchar(64) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ad_token` (`token`),
  KEY `idx_ad_user_kind` (`user_id`,`kind`,`used_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- tasks (video + coupon)
-- --------------------------------------------------------
CREATE TABLE `earning_tasks` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `video_url` text DEFAULT NULL,
  `coupon_code` varchar(100) NOT NULL,
  `coin_reward` decimal(30,12) NOT NULL DEFAULT 1000,
  `timer_seconds` int(10) UNSIGNED NOT NULL DEFAULT 30,
  `daily_limit` int(10) UNSIGNED NOT NULL DEFAULT 1,
  `max_redemptions` int(10) UNSIGNED DEFAULT NULL,
  `total_redemptions` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_status_sort` (`status`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Placeholder tasks — edit the coupon codes/videos in Admin -> Tasks.
INSERT INTO `earning_tasks`
(`title`,`description`,`video_url`,`coupon_code`,`coin_reward`,`timer_seconds`,`daily_limit`,`max_redemptions`,`total_redemptions`,`status`,`sort_order`)
VALUES
('Watch Video #1','Watch the complete video and find the coupon code shown inside it.','','CHANGE001',1000,30,1,NULL,0,1,1),
('Watch Video #2','Watch the complete video and find the coupon code shown inside it.','','CHANGE002',1000,30,1,NULL,0,1,2),
('Watch Video #3','Watch the complete video and find the coupon code shown inside it.','','CHANGE003',1000,30,1,NULL,0,1,3),
('Watch Video #4','Watch the complete video and find the coupon code shown inside it.','','CHANGE004',1000,30,1,NULL,0,1,4),
('Watch Video #5','Watch the complete video and find the coupon code shown inside it.','','CHANGE005',1000,30,1,NULL,0,1,5);

CREATE TABLE `earning_task_attempts` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `task_id` int(10) UNSIGNED NOT NULL,
  `started_at` datetime NOT NULL,
  `completed` tinyint(1) NOT NULL DEFAULT 0,
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_task` (`user_id`,`task_id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_task` (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- referrals
-- --------------------------------------------------------
CREATE TABLE `referrals` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `referrer_id` bigint(20) UNSIGNED NOT NULL,
  `referred_id` bigint(20) UNSIGNED NOT NULL,
  `commission_earned` decimal(18,8) NOT NULL DEFAULT 0.00000000,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `referred_id` (`referred_id`),
  KEY `idx_referrer` (`referrer_id`),
  CONSTRAINT `referrals_ibfk_1` FOREIGN KEY (`referrer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `referrals_ibfk_2` FOREIGN KEY (`referred_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- required channels (join-to-use gate)
-- --------------------------------------------------------
CREATE TABLE `required_channels` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `channel_id` varchar(255) NOT NULL,
  `channel_username` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `invite_url` text DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- No default channels — add yours in Admin -> Channels.

-- --------------------------------------------------------
-- tap log + ledger
-- --------------------------------------------------------
CREATE TABLE `tap_transactions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `taps` int(10) UNSIGNED NOT NULL,
  `reward` decimal(18,8) NOT NULL,
  `tap_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_date` (`user_id`,`tap_date`),
  CONSTRAINT `tap_transactions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `transactions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `type` enum('task','tap','daily_bonus','referral','withdrawal','withdrawal_fee','admin_credit','admin_debit','spin','rank_reward') NOT NULL,
  `amount` decimal(18,8) NOT NULL,
  `balance_before` decimal(18,8) NOT NULL,
  `balance_after` decimal(18,8) NOT NULL,
  `reference_id` varchar(255) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_created` (`user_id`,`created_at`),
  KEY `idx_type` (`type`),
  CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- withdrawals
-- --------------------------------------------------------
CREATE TABLE `withdrawals` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `faucetpay_email` varchar(255) NOT NULL,
  `withdrawal_method` varchar(20) NOT NULL DEFAULT 'faucetpay',
  `amount` decimal(18,8) NOT NULL,
  `fee` decimal(18,8) NOT NULL DEFAULT 0.00000000,
  `net_amount` decimal(18,8) NOT NULL,
  `currency` varchar(20) NOT NULL DEFAULT 'USDT',
  `status` enum('pending','processing','paid','rejected','failed') NOT NULL DEFAULT 'pending',
  `payout_id` varchar(255) DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `processed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_payout` (`payout_id`),
  CONSTRAINT `withdrawals_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- leaderboard rank rewards
-- --------------------------------------------------------
CREATE TABLE `rank_rewards` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `rank_number` int(10) UNSIGNED NOT NULL,
  `reward_amount` decimal(30,12) NOT NULL DEFAULT 0,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rank_number` (`rank_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `rank_rewards` (`rank_number`, `reward_amount`, `active`) VALUES
(1,0,1),(2,0,1),(3,0,1),(4,0,1),(5,0,1),
(6,0,1),(7,0,1),(8,0,1),(9,0,1),(10,0,1);

CREATE TABLE `leaderboard_distributions` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `distribution_date` date NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'running',
  `error_message` text DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_distribution_date` (`distribution_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `leaderboard_reward_claims` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `distribution_date` date NOT NULL,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `rank_number` int(10) UNSIGNED NOT NULL,
  `reward_amount` decimal(30,12) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_rank_claim` (`distribution_date`,`user_id`),
  KEY `idx_rank_date` (`distribution_date`,`rank_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

-- ==========================================================
-- UPGRADING AN EXISTING INSTALLATION (already imported an
-- earlier version of this SQL)? Don't re-run the whole file —
-- it would try to recreate tables that already exist. Just run
-- this block instead; it only adds what's new since the last
-- update (spin daily limit, FaucetPay referral link, auto ad
-- interval). Safe to run more than once.
-- ==========================================================
ALTER TABLE spin_results
    ADD COLUMN IF NOT EXISTS spin_date DATE NOT NULL DEFAULT (CURDATE()) AFTER reward_amount,
    ADD KEY IF NOT EXISTS idx_spin_user_date (user_id, spin_date);

INSERT INTO settings (setting_key, setting_value) VALUES
('spin_daily_limit', '0'),
('faucetpay_referral_link', ''),
('auto_ad_interval_seconds', '0')
ON DUPLICATE KEY UPDATE setting_key = VALUES(setting_key);