-- abomaxpay terawulf db fix
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE user_milestones CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
UPDATE users SET username = CONVERT(0xd8a1d8a8d98ad984d8a8d8a8 USING utf8mb4) WHERE id = 353;
UPDATE users SET username = CONVERT(0x487562627920f09f988d USING utf8mb4) WHERE id = 1689;
UPDATE users SET username = CONVERT(0x43414e41424f59554720f09f8c9f USING utf8mb4) WHERE id = 1786;
UPDATE users SET username = CONVERT(0xf09d91b5f09d91bbf09d91a8f09d91b9f09d91ac20f09d91baf09d91a8f09d91abf09d91abf09d91a8f09d91b4 USING utf8mb4) WHERE id = 1792;
UPDATE users SET username = CONVERT(0x70794c6f726420f09f928b USING utf8mb4) WHERE id = 2053;
UPDATE users SET username = CONVERT(0xf09d97a0f09d97aef09d97bff09d988620f09d97a1f09d97aef09d9881f09d97bcf09d97bcf09d97b9f09d97b6 USING utf8mb4) WHERE id = 2196;
UPDATE users SET username = CONVERT(0xf09d9193f09d918ef09d91a3f09d919cf09d919f USING utf8mb4) WHERE id = 2239;
UPDATE users SET username = CONVERT(0x766f74c3aa78 USING utf8mb4) WHERE id = 2916;
UPDATE users SET username = CONVERT(0xf09d9581f09d9586f09d94b8f09d94bb USING utf8mb4) WHERE id = 3308;
UPDATE users SET username = CONVERT(0x48616e6e696520f09fa5b0f09fa5b0 USING utf8mb4) WHERE id = 4225;
UPDATE users SET username = CONVERT(0x6b61726d6120f09fa5b0 USING utf8mb4) WHERE id = 5032;
UPDATE users SET username = CONVERT(0xf09d99aff09d999af09d9999 USING utf8mb4) WHERE id = 5241;
UPDATE users SET username = CONVERT(0x59766f6e6e65f09fa5b0 USING utf8mb4) WHERE id = 5590;
UPDATE users SET username = CONVERT(0xf09d99b0f09d9a82f09d99b8f09d99b0f09d99bdf09d9a84f09d9a8320f09d9a81f09d99b8f09d99b2f09d99b7f09d99b0f09d9a81f09d99b3 USING utf8mb4) WHERE id = 5676;
UPDATE users SET username = CONVERT(0xc3b13338 USING utf8mb4) WHERE id = 5679;
UPDATE users SET username = CONVERT(0xf09d928ef09d928af09d928cf09d928631 USING utf8mb4) WHERE id = 5889;
UPDATE users SET username = CONVERT(0x546f7279f09fa5b0 USING utf8mb4) WHERE id = 6237;
UPDATE users SET username = CONVERT(0xf09d9a9df09d9a91f09d9a98f09d9a96f09d9a8af09d9a9c USING utf8mb4) WHERE id = 6618;
UPDATE users SET username = CONVERT(0x736865696c6c616820f09f8e80f09f96a4 USING utf8mb4) WHERE id = 6893;
UPDATE users SET username = CONVERT(0xf09d9a8ff09d9a95f09d9a98f09d9a9bf09d9a8ef09d9a97f09d9a8cf09d9a8e USING utf8mb4) WHERE id = 7004;
UPDATE users SET username = CONVERT(0x70726f756420f09fa49120696e74726f76657274 USING utf8mb4) WHERE id = 7072;
UPDATE users SET username = CONVERT(0x5461736861f09fa5b0 USING utf8mb4) WHERE id = 7177;
UPDATE users SET username = CONVERT(0xf09d90b9f09d919ff09d9192f09d9191f09d90b5f09d91a2f09d9198f09d9192f09d919bf09d91a6f09d918e USING utf8mb4) WHERE id = 7233;
UPDATE users SET username = CONVERT(0xf09d9092f09d90acf09d909ef09d90a6f09d90a9f09d909ef09d909bf09d90b0f09d909a20f09d90acf09d90a2f09d90abf09d909af09d90a3f09d909e USING utf8mb4) WHERE id = 7296;
UPDATE users SET username = CONVERT(0xf09d9294f09d928df09d928af09d928ef09d928bf09d9286f09d92864031 USING utf8mb4) WHERE id = 7712;
UPDATE users SET username = CONVERT(0x4465205265616c204d6520f09f8d8f USING utf8mb4) WHERE id = 7940;
UPDATE users SET username = CONVERT(0x4245544120e29da3efb88f USING utf8mb4) WHERE id = 7989;
UPDATE users SET username = CONVERT(0x6a6f6e61686b656e74f09f9882 USING utf8mb4) WHERE id = 8192;
UPDATE users SET username = CONVERT(0xf09d90bdf09d918ef09d9190f09d9198f09d9199f09d9196f09d919bf09d919220f09d9181f09d918ef09d919af09d91a2f09d91a7f09d91a4f09d918ef09d9199f09d918e USING utf8mb4) WHERE id = 8246;
UPDATE users SET username = CONVERT(0x52657469736861207567f09f87baf09f87ac USING utf8mb4) WHERE id = 8321;
UPDATE users SET username = CONVERT(0xc3b96bc3a3c39f68c3a1 USING utf8mb4) WHERE id = 8535;
UPDATE users SET username = CONVERT(0x416c6f79f09f8d83 USING utf8mb4) WHERE id = 8688;
UPDATE users SET username = CONVERT(0xf09d90bcf09d919bf09d919bf09d919cf09d9190f09d9192f09d919bf09d91a120f09d90b4f09d919af09d918ff09d918ef09d91a6f09d919c USING utf8mb4) WHERE id = 9268;
UPDATE users SET username = CONVERT(0x414c4558275320c388374c4f5244 USING utf8mb4) WHERE id = 9272;
UPDATE users SET username = CONVERT(0x4ac3b87564c3ab USING utf8mb4) WHERE id = 9820;
UPDATE users SET username = CONVERT(0x536172616820f09fa5b0f09fa5b0 USING utf8mb4) WHERE id = 9934;
UPDATE users SET username = CONVERT(0x416973686120f09fabb0 USING utf8mb4) WHERE id = 9989;
UPDATE users SET username = CONVERT(0x526963686965272041616c6973c3b66e20f09fa783f09faba7 USING utf8mb4) WHERE id = 10394;
UPDATE users SET username = CONVERT(0xf09d99a2f09d9996f09d99a7f09d99abf09d999ef09d999af09d99a3f09d9996f09d999d USING utf8mb4) WHERE id = 10604;
UPDATE users SET username = CONVERT(0x476f6420f09f9191 USING utf8mb4) WHERE id = 10648;
UPDATE users SET username = CONVERT(0xf09d90bcf09d9181f09d9181f09d9182f09d918bf09d9187f09d90b4f09d9180f09d90b8f09d918b USING utf8mb4) WHERE id = 10778;
UPDATE users SET username = CONVERT(0x44c3a96ac3a32076c3bc USING utf8mb4) WHERE id = 11357;
UPDATE users SET username = CONVERT(0x474944454f4e20f09f9490 USING utf8mb4) WHERE id = 11475;
UPDATE users SET username = CONVERT(0xf09d9290f09d928df09d9282f09d928220f09d91baf09d9296f09d928ff09d9285f09d9282f09d929a USING utf8mb4) WHERE id = 11733;
UPDATE users SET username = CONVERT(0x427562c3bc USING utf8mb4) WHERE id = 12126;
UPDATE users SET username = CONVERT(0xf09d91966d792073656c66 USING utf8mb4) WHERE id = 12567;
UPDATE users SET username = CONVERT(0xf09f98877072696e63657373206a6f7920f09f9887 USING utf8mb4) WHERE id = 13145;
UPDATE users SET username = CONVERT(0x426172616b616820f09fa48d USING utf8mb4) WHERE id = 13246;
UPDATE users SET username = CONVERT(0x68616972776520e29c8cefb88f USING utf8mb4) WHERE id = 13393;
UPDATE users SET username = CONVERT(0x4c616b656c20f09f929e USING utf8mb4) WHERE id = 13825;
UPDATE users SET username = CONVERT(0x416b72617a636f756e74727920f09f87baf09f87ac USING utf8mb4) WHERE id = 14047;
UPDATE users SET username = CONVERT(0xe28495f09d9592f09d959df09d9596f09d9593f09d95a0 USING utf8mb4) WHERE id = 14132;
UPDATE users SET username = CONVERT(0xe1b4aee1b589e281bf2031323334 USING utf8mb4) WHERE id = 15086;
UPDATE users SET username = CONVERT(0x6561c3b170726f USING utf8mb4) WHERE id = 15182;
UPDATE users SET username = CONVERT(0x456d7072c3ad78 USING utf8mb4) WHERE id = 15325;
UPDATE users SET username = CONVERT(0x646561642067686f737420f09f91bb USING utf8mb4) WHERE id = 15484;
UPDATE users SET username = CONVERT(0x4dc68f4e USING utf8mb4) WHERE id = 16052;
UPDATE users SET username = CONVERT(0x54696dc3b870617473 USING utf8mb4) WHERE id = 16112;
UPDATE users SET username = CONVERT(0xf09d928ef09d9296f09d928cf09d9282f09d9294f09d928220f09d9291f09d9286f09d9295f09d9286f09d9293 USING utf8mb4) WHERE id = 16270;
UPDATE users SET username = CONVERT(0xf09d91abf09d9290f09d9295f09d928920f09d9289f09d9286f09d928af09d929b USING utf8mb4) WHERE id = 16496;
UPDATE users SET username = CONVERT(0xe2808f416b72616d7a USING utf8mb4) WHERE id = 16745;
UPDATE users SET username = CONVERT(0xe281b06b6766 USING utf8mb4) WHERE id = 16849;
UPDATE users SET username = CONVERT(0x4641484144204b20f09f94a5 USING utf8mb4) WHERE id = 17097;
UPDATE users SET username = CONVERT(0x6dc3a56c69616e USING utf8mb4) WHERE id = 17343;
UPDATE users SET username = CONVERT(0xc39846464c494e45 USING utf8mb4) WHERE id = 17639;
UPDATE users SET username = CONVERT(0x4b696e6720f09f9191 USING utf8mb4) WHERE id = 17640;
UPDATE users SET username = CONVERT(0x41746963616d20e29da4efb88ff09f928b USING utf8mb4) WHERE id = 17748;
UPDATE users SET username = CONVERT(0xf09d928df09d9296f09d928ff09d9282f09d92935f20f09d92995ff09d9284f09d929af09d9283f09d9286f09d9293 USING utf8mb4) WHERE id = 18086;
UPDATE users SET username = CONVERT(0xf09d9092f09d90a1f09d909af09d90aff09d90a2f09d90b120f09d90abf09d909af09d90b2f09d90b3 USING utf8mb4) WHERE id = 18098;
UPDATE users SET username = CONVERT(0x4164656b757220c3806e67656c6c61 USING utf8mb4) WHERE id = 18466;
UPDATE users SET username = CONVERT(0x6963656765c3a92e37 USING utf8mb4) WHERE id = 18502;
UPDATE users SET username = CONVERT(0x636f636f6e75742077617465722020f09f92a6 USING utf8mb4) WHERE id = 18510;
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `phone` (`phone`),
  ADD UNIQUE KEY `referral_code` (`referral_code`),
  ADD KEY `idx_users_username` (`username`),
  ADD KEY `idx_users_phone` (`phone`),
  ADD KEY `idx_users_balance` (`balance`),
  ADD KEY `idx_users_created_at` (`created_at`);
ALTER TABLE users
  MODIFY id int(11) NOT NULL AUTO_INCREMENT;
DROP TABLE IF EXISTS v_daily_earnings_trend;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v_daily_earnings_trend` AS SELECT `user_daily_earnings`.`earned_date` AS `earned_date`, count(distinct `user_daily_earnings`.`user_id`) AS `total_users`, count(distinct `user_daily_earnings`.`machine_id`) AS `total_machines`, count(0) AS `total_records`, coalesce(sum(`user_daily_earnings`.`amount`),0) AS `total_earnings` FROM `user_daily_earnings` GROUP BY `user_daily_earnings`.`earned_date` ORDER BY `user_daily_earnings`.`earned_date` DESC ;
DROP TABLE IF EXISTS v_machine_earnings;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v_machine_earnings` AS SELECT `um`.`id` AS `user_machine_id`, `um`.`user_id` AS `user_id`, `u`.`username` AS `username`, `um`.`machine_name` AS `machine_name`, `um`.`purchase_date` AS `purchase_date`, `um`.`expiry_date` AS `expiry_date`, `um`.`status` AS `status`, count(`ude`.`id`) AS `days_earned`, coalesce(sum(`ude`.`amount`),0) AS `total_earned` FROM ((`user_machines` `um` join `users` `u` on(`um`.`user_id` = `u`.`id`)) left join `user_daily_earnings` `ude` on(`um`.`id` = `ude`.`machine_id`)) WHERE `um`.`status` in ('active','expired') GROUP BY `um`.`id`, `um`.`user_id`, `u`.`username`, `um`.`machine_name`, `um`.`purchase_date`, `um`.`expiry_date`, `um`.`status` ORDER BY coalesce(sum(`ude`.`amount`),0) DESC ;
DROP TABLE IF EXISTS v_missing_earnings;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v_missing_earnings` AS SELECT `um`.`id` AS `user_machine_id`, `um`.`user_id` AS `user_id`, `u`.`username` AS `username`, `um`.`machine_name` AS `machine_name`, `um`.`purchase_date` AS `purchase_date`, `um`.`status` AS `status`, count(`ude`.`id`) AS `record_count` FROM ((`user_machines` `um` join `users` `u` on(`um`.`user_id` = `u`.`id`)) left join `user_daily_earnings` `ude` on(`um`.`id` = `ude`.`machine_id`)) WHERE `um`.`status` in ('active','expired') GROUP BY `um`.`id`, `um`.`user_id`, `u`.`username`, `um`.`machine_name`, `um`.`purchase_date`, `um`.`status` HAVING `record_count` = 0 ;
DROP TABLE IF EXISTS v_user_earnings;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v_user_earnings` AS SELECT `u`.`id` AS `user_id`, `u`.`username` AS `username`, count(distinct `ude`.`machine_id`) AS `total_machines`, count(`ude`.`id`) AS `total_days`, coalesce(sum(`ude`.`amount`),0) AS `total_earnings` FROM (`users` `u` left join `user_daily_earnings` `ude` on(`u`.`id` = `ude`.`user_id`)) WHERE `u`.`role` = 'user' GROUP BY `u`.`id`, `u`.`username` ORDER BY coalesce(sum(`ude`.`amount`),0) DESC ;
ALTER TABLE admin_logs
  ADD CONSTRAINT admin_logs_ibfk_1 FOREIGN KEY (admin_id) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE messages
  ADD CONSTRAINT messages_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE referral_earnings
  ADD CONSTRAINT referral_earnings_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  ADD CONSTRAINT referral_earnings_ibfk_2 FOREIGN KEY (from_user_id) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE transactions
  ADD CONSTRAINT transactions_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE user_machines
  ADD CONSTRAINT user_machines_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  ADD CONSTRAINT user_machines_ibfk_2 FOREIGN KEY (machine_id) REFERENCES machines (id) ON DELETE CASCADE;