-- Token/Credit System Database Setup

-- Create budgets table for group financial management
CREATE TABLE IF NOT EXISTS budgets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT NOT NULL UNIQUE,
    balance DECIMAL(10,2) DEFAULT 0.00,
    total_purchased DECIMAL(10,2) DEFAULT 0.00,
    total_spent DECIMAL(10,2) DEFAULT 0.00,
    currency VARCHAR(3) DEFAULT 'USD',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES user_groups(id) ON DELETE CASCADE
);

-- Create token_actions table for configurable pricing
CREATE TABLE IF NOT EXISTS token_actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action_name VARCHAR(100) NOT NULL UNIQUE,
    action_description TEXT,
    token_cost DECIMAL(8,2) NOT NULL,
    category VARCHAR(50) DEFAULT 'general',
    is_active TINYINT(1) DEFAULT 1,
    code_snippet TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Create token_transactions table for audit trail
CREATE TABLE IF NOT EXISTS token_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    budget_id INT NOT NULL,
    action_id INT,
    user_id INT NOT NULL,
    transaction_type ENUM('debit', 'credit', 'purchase', 'refund') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    balance_before DECIMAL(10,2) NOT NULL,
    balance_after DECIMAL(10,2) NOT NULL,
    description TEXT,
    reference_id VARCHAR(100), -- For linking to specific records (event_id, participant_id, etc.)
    reference_type VARCHAR(50), -- 'event', 'participant', 'judge', etc.
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create token_packages table for purchasing options
CREATE TABLE IF NOT EXISTS token_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    token_amount DECIMAL(10,2) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    bonus_tokens DECIMAL(8,2) DEFAULT 0.00,
    is_active TINYINT(1) DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Add budget_id to user_groups if not exists
ALTER TABLE user_groups ADD COLUMN IF NOT EXISTS budget_id INT NULL,
ADD FOREIGN KEY (budget_id) REFERENCES budgets(id) ON DELETE SET NULL;

-- Insert default token actions
INSERT INTO token_actions (action_name, action_description, token_cost, category, code_snippet) VALUES 
('add_participant', 'Add new participant to event', 1.00, 'participants', 'TokenSystem::charge(\'add_participant\', $user_id, [\'participant_id\' => $participant_id]);'),
('add_judge', 'Add new judge to system', 5.00, 'judges', 'TokenSystem::charge(\'add_judge\', $user_id, [\'judge_id\' => $judge_id]);'),
('create_event', 'Create new event', 30.00, 'events', 'TokenSystem::charge(\'create_event\', $user_id, [\'event_id\' => $event_id]);'),
('generate_pdf', 'Generate PDF report/certificate', 40.00, 'reports', 'TokenSystem::charge(\'generate_pdf\', $user_id, [\'document_type\' => $type]);'),
('send_email', 'Send email notification', 0.50, 'communications', 'TokenSystem::charge(\'send_email\', $user_id, [\'recipient\' => $email]);'),
('sms_notification', 'Send SMS notification', 2.00, 'communications', 'TokenSystem::charge(\'sms_notification\', $user_id, [\'phone\' => $phone]);'),
('live_streaming', 'Enable live streaming for event', 100.00, 'premium', 'TokenSystem::charge(\'live_streaming\', $user_id, [\'event_id\' => $event_id]);'),
('custom_branding', 'Apply custom branding to event', 50.00, 'premium', 'TokenSystem::charge(\'custom_branding\', $user_id, [\'event_id\' => $event_id]);'),
('export_data', 'Export event data (CSV/Excel)', 10.00, 'reports', 'TokenSystem::charge(\'export_data\', $user_id, [\'format\' => $format]);'),
('api_call', 'External API integration call', 0.10, 'integrations', 'TokenSystem::charge(\'api_call\', $user_id, [\'api_service\' => $service]);');

-- Insert default token packages
INSERT INTO token_packages (name, description, token_amount, price, bonus_tokens, sort_order) VALUES 
('Starter Pack', 'Perfect for small events', 100.00, 19.99, 0.00, 1),
('Professional Pack', 'Great for regular organizers', 500.00, 89.99, 50.00, 2),
('Enterprise Pack', 'For large organizations', 1500.00, 249.99, 200.00, 3),
('Premium Pack', 'Maximum value package', 3000.00, 449.99, 500.00, 4);

-- Create indexes for performance
CREATE INDEX idx_budgets_group_id ON budgets(group_id);
CREATE INDEX idx_token_transactions_budget_id ON token_transactions(budget_id);
CREATE INDEX idx_token_transactions_user_id ON token_transactions(user_id);
CREATE INDEX idx_token_transactions_created_at ON token_transactions(created_at);
CREATE INDEX idx_token_actions_name ON token_actions(action_name);
CREATE INDEX idx_token_actions_active ON token_actions(is_active);

-- Create default budgets for existing groups
INSERT INTO budgets (group_id, balance, total_purchased)
SELECT id, 100.00, 100.00 FROM user_groups 
WHERE NOT EXISTS (SELECT 1 FROM budgets WHERE budgets.group_id = user_groups.id);
