-- Token System Database Setup
-- Run this script to create the required tables for the token management system

-- Create user_groups table if it doesn't exist
CREATE TABLE IF NOT EXISTS `user_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `color` varchar(7) DEFAULT '#007bff',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create user_group_memberships table if it doesn't exist
CREATE TABLE IF NOT EXISTS `user_group_memberships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `role_in_group` varchar(50) DEFAULT 'member',
  `added_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_membership` (`user_id`, `group_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create budgets table if it doesn't exist
CREATE TABLE IF NOT EXISTS `budgets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `total_spent` decimal(10,2) NOT NULL DEFAULT '0.00',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_group_id` (`group_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create token_actions table if it doesn't exist
CREATE TABLE IF NOT EXISTS `token_actions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action_name` varchar(100) NOT NULL,
  `action_description` text,
  `token_cost` decimal(8,2) NOT NULL DEFAULT '0.00',
  `category` varchar(50) DEFAULT 'general',
  `code_snippet` text,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_action_name` (`action_name`),
  KEY `idx_category` (`category`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create token_transactions table if it doesn't exist
CREATE TABLE IF NOT EXISTS `token_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `budget_id` int(11) NOT NULL,
  `action_id` int(11) DEFAULT NULL,
  `transaction_type` enum('debit','credit','purchase','refund') NOT NULL,
  `amount` decimal(8,2) NOT NULL,
  `description` text,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` int(11) DEFAULT NULL,
  `balance_after` decimal(10,2) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_budget_id` (`budget_id`),
  KEY `idx_action_id` (`action_id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_reference` (`reference_type`, `reference_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create token_packages table if it doesn't exist
CREATE TABLE IF NOT EXISTS `token_packages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text,
  `token_amount` decimal(8,2) NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `bonus_tokens` decimal(8,2) DEFAULT '0.00',
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_active` (`is_active`),
  KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default token actions if they don't exist
INSERT IGNORE INTO `token_actions` (`action_name`, `action_description`, `token_cost`, `category`, `code_snippet`, `created_by`) VALUES
('add_participant', 'Add a new participant to an event', 1.00, 'participants', 'TokenSystem::charge(\'add_participant\', $user_id, [\'event_id\' => $event_id]);', 1),
('create_event', 'Create a new event', 30.00, 'events', 'TokenSystem::charge(\'create_event\', $user_id, [\'event_id\' => $event_id]);', 1),
('add_judge', 'Add a judge to an event', 5.00, 'judges', 'TokenSystem::charge(\'add_judge\', $user_id, [\'event_id\' => $event_id]);', 1),
('generate_report', 'Generate and export reports', 10.00, 'reports', 'TokenSystem::charge(\'generate_report\', $user_id, [\'report_type\' => $report_type]);', 1),
('send_notification', 'Send notifications to participants/judges', 2.00, 'communications', 'TokenSystem::charge(\'send_notification\', $user_id, [\'notification_id\' => $notification_id]);', 1),
('export_data', 'Export event data', 5.00, 'reports', 'TokenSystem::charge(\'export_data\', $user_id, [\'export_type\' => $export_type]);', 1),
('premium_feature', 'Access premium features', 50.00, 'premium', 'TokenSystem::charge(\'premium_feature\', $user_id, [\'feature_id\' => $feature_id]);', 1);

-- Insert default token packages if they don't exist
INSERT IGNORE INTO `token_packages` (`id`, `name`, `description`, `token_amount`, `price`, `bonus_tokens`, `sort_order`) VALUES
(1, 'Starter Pack', 'Perfect for small events', 100.00, 9.99, 10.00, 1),
(2, 'Professional', 'Great for regular organizers', 500.00, 39.99, 75.00, 2),
(3, 'Enterprise', 'For large organizations', 1500.00, 99.99, 300.00, 3),
(4, 'Mega Pack', 'Maximum value package', 5000.00, 299.99, 1000.00, 4);

-- Create default admin group if it doesn't exist
INSERT IGNORE INTO `user_groups` (`id`, `name`, `description`, `color`, `created_by`) VALUES
(1, 'Administrators', 'System administrators with full access', '#dc3545', 1);

-- Add admin users to admin group (adjust user IDs as needed)
INSERT IGNORE INTO `user_group_memberships` (`user_id`, `group_id`, `role_in_group`, `added_by`) 
SELECT u.id, 1, 'admin', 1 
FROM users u 
WHERE u.role IN ('super_admin', 'admin') 
AND NOT EXISTS (
    SELECT 1 FROM user_group_memberships ugm 
    WHERE ugm.user_id = u.id AND ugm.group_id = 1
);

-- Create default budget for admin group
INSERT IGNORE INTO `budgets` (`id`, `group_id`, `balance`, `total_spent`, `is_active`, `created_by`) VALUES
(1, 1, 1000.00, 0.00, 1, 1);

-- Display setup completion message
SELECT 
    'Token system database setup completed!' as message,
    (SELECT COUNT(*) FROM token_actions) as total_actions,
    (SELECT COUNT(*) FROM token_packages) as total_packages,
    (SELECT COUNT(*) FROM user_groups) as total_groups,
    (SELECT COUNT(*) FROM budgets) as total_budgets;
