-- Role-Based Admin System Database Update
-- This script safely adds the required columns to the existing users table
-- and creates the admin_transactions table
-- 
-- WALLET SYSTEM CLARIFICATION:
-- 1. All users (admin, moderator, agent, shop, ordinary) use the existing `wallets` table
-- 2. Admin transactions are tracked in the separate `admin_transactions` table
-- 3. This unified approach uses the existing wallet infrastructure
--
-- Execute this script on live server to implement role-based admin system

-- Step 1: Add new columns to users table safely
ALTER TABLE `users`
ADD COLUMN `role` ENUM('admin', 'moderator', 'agent', 'shop') DEFAULT 'shop' AFTER `password`;

ALTER TABLE `users`
ADD COLUMN `status` ENUM('active', 'inactive', 'suspended') DEFAULT 'active' AFTER `role`;

ALTER TABLE `users`
ADD COLUMN `created_by` BIGINT(20) UNSIGNED NULL AFTER `status`;

-- Step 2: Add foreign key constraint for created_by
ALTER TABLE `users`
ADD CONSTRAINT `fk_users_created_by`
FOREIGN KEY (`created_by`) REFERENCES `users`(`user_id`) ON DELETE SET NULL;

-- Step 3: Create admin_transactions table
CREATE TABLE `admin_transactions` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `created_by` BIGINT(20) UNSIGNED NOT NULL,
  `type` ENUM('deposit', 'withdrawal', 'adjustment', 'commission') NOT NULL,
  `amount` DECIMAL(15,2) NOT NULL,
  `balance_before` DECIMAL(15,2) NOT NULL,
  `balance_after` DECIMAL(15,2) NOT NULL,
  `description` VARCHAR(255) NULL,
  `reference` VARCHAR(255) NULL,
  `status` ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
  `metadata` JSON NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_user_id_type` (`user_id`, `type`),
  INDEX `idx_created_by` (`created_by`),
  INDEX `idx_status` (`status`),
  CONSTRAINT `fk_admin_trans_user_id` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_admin_trans_created_by` FOREIGN KEY (`created_by`) REFERENCES `users`(`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 4: Update existing users with appropriate roles based on current is_admin and is_shop flags
UPDATE `users` SET
  `role` = CASE
    WHEN `is_admin` = 1 THEN 'admin'
    WHEN `is_shop` = 1 THEN 'shop'
    ELSE 'shop'
  END,
  `status` = 'active';

-- Step 5: Set created_by relationships (admin users have no creator)  
UPDATE `users` SET `created_by` = NULL WHERE `role` = 'admin';

-- Step 6: Insert sample hierarchy data (moderator and agents)
INSERT INTO `users` (`user_id`, `name`, `email`, `password`, `role`, `status`, `is_admin`, `is_shop`, `created_by`, `created_at`, `updated_at`) VALUES
(104, 'Head Moderator', 'moderator@sportbook.com', '$2y$12$cqtZqjm4F0YPMr0JOIER/.8wWiok/nf8XgyfItrzxy7u3tXT86hkW', 'moderator', 'active', 0, 0, 102, NOW(), NOW()),
(105, 'Agent Alpha', 'agent1@sportbook.com', '$2y$12$cqtZqjm4F0YPMr0JOIER/.8wWiok/nf8XgyfItrzxy7u3tXT86hkW', 'agent', 'active', 0, 0, 104, NOW(), NOW()),
(106, 'Agent Beta', 'agent2@sportbook.com', '$2y$12$cqtZqjm4F0YPMr0JOIER/.8wWiok/nf8XgyfItrzxy7u3tXT86hkW', 'agent', 'active', 0, 0, 102, NOW(), NOW());

-- Step 7: Update existing shop users to belong to agents
UPDATE `users` SET `created_by` = 105 WHERE `user_id` = 101; -- Shop belongs to Agent Alpha
UPDATE `users` SET `created_by` = 106 WHERE `user_id` = 103; -- Sample belongs to Agent Beta

-- Step 8: Insert sample transactions to establish financial history
INSERT INTO `admin_transactions` (`user_id`, `created_by`, `type`, `amount`, `balance_before`, `balance_after`, `description`, `reference`, `status`, `created_at`, `updated_at`) VALUES
(104, 102, 'deposit', 10000.00, 0.00, 10000.00, 'Initial deposit for moderator', 'DEP20250905001', 'completed', NOW(), NOW()),
(105, 104, 'deposit', 5000.00, 0.00, 5000.00, 'Initial deposit for agent', 'DEP20250905002', 'completed', NOW(), NOW()),
(106, 102, 'deposit', 3000.00, 0.00, 3000.00, 'Initial deposit for agent', 'DEP20250905003', 'completed', NOW(), NOW()),
(101, 105, 'deposit', 1000.00, 0.00, 1000.00, 'Initial deposit for shop', 'DEP20250905004', 'completed', NOW(), NOW()),
(103, 106, 'deposit', 1000.00, 0.00, 1000.00, 'Initial deposit for shop', 'DEP20250905005', 'completed', NOW(), NOW());

-- Step 9: Verification queries (uncomment to run after execution)
-- SELECT 'Updated users table:' as Info;
-- SELECT user_id, name, email, role, status, created_by FROM users ORDER BY user_id;
-- 
-- SELECT 'Sample transactions:' as Info;
-- SELECT t.id, t.user_id, u.name as user_name, t.type, t.amount, t.balance_before, t.balance_after, t.reference, t.status 
-- FROM admin_transactions t 
-- JOIN users u ON t.user_id = u.user_id 
-- ORDER BY t.id;