-- Volunteer Management Table Creation Script
-- This table stores volunteer role information managed by admin user 999914
-- Table name follows event- prefix convention: EventVolunteerRoles

CREATE TABLE IF NOT EXISTS EventVolunteerRoles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Member Information
    member_id VARCHAR(20) NOT NULL,
    
    -- Volunteer Role Information
    volunteer_email VARCHAR(255) NOT NULL,
    volunteer_password VARCHAR(255) NOT NULL,
    role_name VARCHAR(100) NOT NULL, -- e.g., 'secretary', 'greens'
    
    -- Google Drive Information
    google_drive_folder_id VARCHAR(255), -- Google Drive folder ID for this role
    google_drive_folder_url VARCHAR(500), -- Full URL to the folder
    
    -- Google Workspace Access
    private_workspace_email VARCHAR(255), -- elparaisogolf.co@gmail.com
    business_workspace_email VARCHAR(255), -- elparaisogolfclub@gmail.com
    
    -- Status and Timestamps
    is_active ENUM('Y', 'N') DEFAULT 'Y',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_member_role (member_id, role_name),
    INDEX idx_member_id (member_id),
    INDEX idx_role_name (role_name),
    INDEX idx_volunteer_email (volunteer_email),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert initial data for secretary and greens roles
INSERT INTO EventVolunteerRoles (member_id, volunteer_email, volunteer_password, role_name, private_workspace_email, business_workspace_email) 
VALUES 
    ('8022', 'secretary@elparaisogolf.com', '@Elparaisogolf2025@', 'secretary', 'elparaisogolf.co@gmail.com', 'elparaisogolfclub@gmail.com'),
    ('199', 'greens@elparaisogolf.com', '@Elparaisogolf2025@', 'greens', 'elparaisogolf.co@gmail.com', 'elparaisogolfclub@gmail.com')
ON DUPLICATE KEY UPDATE 
    volunteer_email = VALUES(volunteer_email),
    volunteer_password = VALUES(volunteer_password),
    updated_at = CURRENT_TIMESTAMP;


