upskill-event-manager/includes/google-sheets/class-google-sheets-manager.php

<?php
/**
 * Google Sheets Manager
 *
 * Manages spreadsheet creation and data export for HVAC Community Events
 *
 * @package HVAC_Community_Events
 * @subpackage Google_Sheets_Integration
 */

if (!defined('ABSPATH')) {
    exit;
}

require_once plugin_dir_path(__FILE__) . 'class-google-sheets-auth.php';

// Include folder manager if it exists
$folder_manager_file = plugin_dir_path(__FILE__) . 'class-google-sheets-folder-manager.php';
if (file_exists($folder_manager_file)) {
    require_once $folder_manager_file;
}

class HVAC_Google_Sheets_Manager {
    
    private $auth;
    private $master_dashboard_data;
    private $logger;
    private $folder_manager;
    
    public function __construct() {
        $this->auth = new HVAC_Google_Sheets_Auth();
        
        // Initialize folder manager only if the class exists
        if (class_exists('HVAC_Google_Sheets_Folder_Manager')) {
            $this->folder_manager = new HVAC_Google_Sheets_Folder_Manager();
        }
        
        // Load master dashboard data class
        if (class_exists('HVAC_Master_Dashboard_Data')) {
            $this->master_dashboard_data = new HVAC_Master_Dashboard_Data();
        }
        
        // Load logger if available
        if (class_exists('HVAC_Logger')) {
            $this->logger = new HVAC_Logger();
        }
    }
    
    /**
     * Create Master Report spreadsheet with 4 tabs
     */
    public function create_master_report() {
        try {
            if (!$this->auth->is_authenticated()) {
                throw new Exception('Google Sheets not authenticated');
            }
            
            // Get master trainer folder ID
            $master_folder_id = null;
            if ($this->folder_manager) {
                $master_folder_id = $this->folder_manager->get_master_trainer_folder_id();
            }
            if (!$master_folder_id) {
                throw new Exception('Failed to get/create master trainer folder');
            }
            
            $spreadsheet_data = array(
                'properties' => array(
                    'title' => 'HVAC Master Report - ' . date('Y-m-d H:i:s')
                ),
                'parents' => array($master_folder_id),
                'sheets' => array(
                    array(
                        'properties' => array(
                            'title' => 'System Overview',
                            'index' => 0
                        )
                    ),
                    array(
                        'properties' => array(
                            'title' => 'Trainer Performance',
                            'index' => 1
                        )
                    ),
                    array(
                        'properties' => array(
                            'title' => 'All Events',
                            'index' => 2
                        )
                    ),
                    array(
                        'properties' => array(
                            'title' => 'Revenue Analytics',
                            'index' => 3
                        )
                    )
                )
            );
            
            // First create the spreadsheet using Sheets API
            $sheet_data = array(
                'properties' => $spreadsheet_data['properties'],
                'sheets' => $spreadsheet_data['sheets']
            );
            
            $response = $this->auth->make_api_request('POST', '', $sheet_data);
            
            // Then move it to the correct folder using Drive API
            if (isset($response['spreadsheetId'])) {
                $this->move_file_to_folder($response['spreadsheetId'], $master_folder_id);
            }
            
            if (isset($response['spreadsheetId'])) {
                $spreadsheet_id = $response['spreadsheetId'];
                
                // Populate each tab with data
                $this->populate_system_overview_tab($spreadsheet_id);
                $this->populate_trainer_performance_tab($spreadsheet_id);
                $this->populate_all_events_tab($spreadsheet_id);
                $this->populate_revenue_analytics_tab($spreadsheet_id);
                
                // Store spreadsheet info
                $this->store_master_report_info($spreadsheet_id, $response['spreadsheetUrl']);
                
                $this->log_info("Master Report created: {$spreadsheet_id}");
                
                return array(
                    'success' => true,
                    'spreadsheet_id' => $spreadsheet_id,
                    'url' => $response['spreadsheetUrl']
                );
            }
            
            throw new Exception('Failed to create spreadsheet');
            
        } catch (Exception $e) {
            $this->log_error('Failed to create Master Report: ' . $e->getMessage());
            return array(
                'success' => false,
                'error' => $e->getMessage()
            );
        }
    }
    
    /**
     * Create Event-specific spreadsheet with 3 tabs
     */
    public function create_event_spreadsheet($event_id) {
        try {
            if (!$this->auth->is_authenticated()) {
                throw new Exception('Google Sheets not authenticated');
            }
            
            $event = get_post($event_id);
            if (!$event) {
                throw new Exception('Event not found');
            }
            
            // Get event-specific folder ID
            $event_folder_id = null;
            if ($this->folder_manager) {
                $event_folder_id = $this->folder_manager->get_event_folder_id($event_id);
            }
            if (!$event_folder_id) {
                // If no folder manager, use the root folder or throw exception
                throw new Exception('Failed to get/create event folder - folder manager not available');
            }
            
            $spreadsheet_data = array(
                'properties' => array(
                    'title' => 'Event Report - ' . $event->post_title . ' - ' . date('Y-m-d')
                ),
                'parents' => array($event_folder_id),
                'sheets' => array(
                    array(
                        'properties' => array(
                            'title' => 'Event Details',
                            'index' => 0
                        )
                    ),
                    array(
                        'properties' => array(
                            'title' => 'Attendees',
                            'index' => 1
                        )
                    ),
                    array(
                        'properties' => array(
                            'title' => 'Financial Summary',
                            'index' => 2
                        )
                    )
                )
            );
            
            // First create the spreadsheet using Sheets API
            $sheet_data = array(
                'properties' => $spreadsheet_data['properties'],
                'sheets' => $spreadsheet_data['sheets']
            );
            
            $response = $this->auth->make_api_request('POST', '', $sheet_data);
            
            // Then move it to the correct folder using Drive API
            if (isset($response['spreadsheetId'])) {
                $this->move_file_to_folder($response['spreadsheetId'], $event_folder_id);
            }
            
            if (isset($response['spreadsheetId'])) {
                $spreadsheet_id = $response['spreadsheetId'];
                
                // Populate each tab with event data
                $this->populate_event_details_tab($spreadsheet_id, $event_id);
                $this->populate_event_attendees_tab($spreadsheet_id, $event_id);
                $this->populate_event_financial_tab($spreadsheet_id, $event_id);
                
                // Store event spreadsheet info
                $this->store_event_spreadsheet_info($event_id, $spreadsheet_id, $response['spreadsheetUrl']);
                
                $this->log_info("Event spreadsheet created for event {$event_id}: {$spreadsheet_id}");
                
                return array(
                    'success' => true,
                    'spreadsheet_id' => $spreadsheet_id,
                    'url' => $response['spreadsheetUrl']
                );
            }
            
            throw new Exception('Failed to create event spreadsheet');
            
        } catch (Exception $e) {
            $this->log_error("Failed to create event spreadsheet for {$event_id}: " . $e->getMessage());
            return array(
                'success' => false,
                'error' => $e->getMessage()
            );
        }
    }
    
    /**
     * Populate System Overview tab
     */
    private function populate_system_overview_tab($spreadsheet_id) {
        if (!$this->master_dashboard_data) {
            return;
        }
        
        $data = array(
            'range' => 'System Overview!A1:B10',
            'majorDimension' => 'ROWS',
            'valueInputOption' => 'USER_ENTERED',
            'values' => array(
                array('HVAC Community Events - System Overview', ''),
                array('Generated', date('Y-m-d H:i:s')),
                array('', ''),
                array('Metric', 'Value'),
                array('Total Events', $this->master_dashboard_data->get_total_events_count()),
                array('Upcoming Events', $this->master_dashboard_data->get_upcoming_events_count()),
                array('Completed Events', $this->master_dashboard_data->get_completed_events_count()),
                array('Active Trainers', $this->master_dashboard_data->get_active_trainers_count()),
                array('Tickets Sold', $this->master_dashboard_data->get_total_tickets_sold()),
                array('Total Revenue', '$' . number_format($this->master_dashboard_data->get_total_revenue(), 2))
            )
        );
        
        $endpoint = "/{$spreadsheet_id}/values/System Overview!A1:B10";
        $this->auth->make_api_request('PUT', $endpoint, $data);
    }
    
    /**
     * Populate Trainer Performance tab
     */
    private function populate_trainer_performance_tab($spreadsheet_id) {
        if (!$this->master_dashboard_data) {
            return;
        }
        
        $trainer_data = $this->master_dashboard_data->get_trainer_performance_data();
        
        $values = array(
            array('Trainer Performance Analytics', '', '', '', ''),
            array('Generated', date('Y-m-d H:i:s'), '', '', ''),
            array('', '', '', '', ''),
            array('Trainer', 'Events', 'Tickets Sold', 'Revenue', 'Avg Revenue/Event')
        );
        
        foreach ($trainer_data as $trainer) {
            $avg_revenue = $trainer['events'] > 0 ? $trainer['revenue'] / $trainer['events'] : 0;
            $values[] = array(
                $trainer['name'],
                $trainer['events'],
                $trainer['tickets'],
                '$' . number_format($trainer['revenue'], 2),
                '$' . number_format($avg_revenue, 2)
            );
        }
        
        $data = array(
            'range' => 'Trainer Performance!A1:E' . (count($values)),
            'majorDimension' => 'ROWS',
            'valueInputOption' => 'USER_ENTERED',
            'values' => $values
        );
        
        $endpoint = "/{$spreadsheet_id}/values/Trainer Performance!A1:E" . (count($values));
        $this->auth->make_api_request('PUT', $endpoint, $data);
    }
    
    /**
     * Populate All Events tab
     */
    private function populate_all_events_tab($spreadsheet_id) {
        if (!$this->master_dashboard_data) {
            return;
        }
        
        $events_data = $this->master_dashboard_data->get_all_events_data();
        
        $values = array(
            array('All Events Management', '', '', '', '', ''),
            array('Generated', date('Y-m-d H:i:s'), '', '', '', ''),
            array('', '', '', '', '', ''),
            array('Event Title', 'Trainer', 'Date', 'Status', 'Tickets', 'Revenue')
        );
        
        foreach ($events_data as $event) {
            $values[] = array(
                $event['title'],
                $event['trainer_name'],
                $event['date'],
                $event['status'],
                $event['tickets'],
                '$' . number_format($event['revenue'], 2)
            );
        }
        
        $data = array(
            'range' => 'All Events!A1:F' . (count($values)),
            'majorDimension' => 'ROWS',
            'valueInputOption' => 'USER_ENTERED',
            'values' => $values
        );
        
        $endpoint = "/{$spreadsheet_id}/values/All Events!A1:F" . (count($values));
        $this->auth->make_api_request('PUT', $endpoint, $data);
    }
    
    /**
     * Populate Revenue Analytics tab
     */
    private function populate_revenue_analytics_tab($spreadsheet_id) {
        if (!$this->master_dashboard_data) {
            return;
        }
        
        $monthly_data = $this->master_dashboard_data->get_monthly_revenue_data();
        
        $values = array(
            array('Revenue Analytics', '', ''),
            array('Generated', date('Y-m-d H:i:s'), ''),
            array('', '', ''),
            array('Month', 'Events', 'Revenue')
        );
        
        foreach ($monthly_data as $month_data) {
            $values[] = array(
                $month_data['month'],
                $month_data['events'],
                '$' . number_format($month_data['revenue'], 2)
            );
        }
        
        $data = array(
            'range' => 'Revenue Analytics!A1:C' . (count($values)),
            'majorDimension' => 'ROWS',
            'valueInputOption' => 'USER_ENTERED',
            'values' => $values
        );
        
        $endpoint = "/{$spreadsheet_id}/values/Revenue Analytics!A1:C" . (count($values));
        $this->auth->make_api_request('PUT', $endpoint, $data);
    }
    
    /**
     * Populate Event Details tab
     */
    private function populate_event_details_tab($spreadsheet_id, $event_id) {
        $event = get_post($event_id);
        $event_meta = get_post_meta($event_id);
        
        $values = array(
            array('Event Details Report', ''),
            array('Generated', date('Y-m-d H:i:s')),
            array('', ''),
            array('Field', 'Value'),
            array('Event Title', $event->post_title),
            array('Event Date', get_post_meta($event_id, '_EventStartDate', true)),
            array('Event Time', get_post_meta($event_id, '_EventStartTime', true)),
            array('Venue', get_post_meta($event_id, '_EventVenueName', true)),
            array('Address', get_post_meta($event_id, '_EventAddress', true)),
            array('Trainer', get_the_author_meta('display_name', $event->post_author)),
            array('Status', $event->post_status),
            array('Capacity', get_post_meta($event_id, '_EventCapacity', true)),
            array('Description', wp_strip_all_tags($event->post_content))
        );
        
        $data = array(
            'range' => 'Event Details!A1:B' . (count($values)),
            'majorDimension' => 'ROWS',
            'valueInputOption' => 'USER_ENTERED',
            'values' => $values
        );
        
        $endpoint = "/{$spreadsheet_id}/values/Event Details!A1:B" . (count($values));
        $this->auth->make_api_request('PUT', $endpoint, $data);
    }
    
    /**
     * Populate Event Attendees tab
     */
    private function populate_event_attendees_tab($spreadsheet_id, $event_id) {
        // Get attendees data for this event
        global $wpdb;
        
        $attendees = $wpdb->get_results($wpdb->prepare(
            "SELECT u.display_name, u.user_email, um.meta_value as phone
             FROM {$wpdb->posts} p
             JOIN {$wpdb->users} u ON p.post_author = u.ID
             LEFT JOIN {$wpdb->usermeta} um ON u.ID = um.user_id AND um.meta_key = 'phone'
             WHERE p.post_parent = %d AND p.post_type = 'tribe_rsvp_attendees'",
            $event_id
        ));
        
        $values = array(
            array('Event Attendees', '', ''),
            array('Generated', date('Y-m-d H:i:s'), ''),
            array('', '', ''),
            array('Name', 'Email', 'Phone')
        );
        
        foreach ($attendees as $attendee) {
            $values[] = array(
                $attendee->display_name,
                $attendee->user_email,
                $attendee->phone ?: 'N/A'
            );
        }
        
        $data = array(
            'range' => 'Attendees!A1:C' . (count($values)),
            'majorDimension' => 'ROWS',
            'valueInputOption' => 'USER_ENTERED',
            'values' => $values
        );
        
        $endpoint = "/{$spreadsheet_id}/values/Attendees!A1:C" . (count($values));
        $this->auth->make_api_request('PUT', $endpoint, $data);
    }
    
    /**
     * Populate Event Financial tab
     */
    private function populate_event_financial_tab($spreadsheet_id, $event_id) {
        // Calculate financial data for this event
        $ticket_sales = $this->calculate_event_revenue($event_id);
        $capacity = get_post_meta($event_id, '_EventCapacity', true);
        $sold_tickets = count($this->get_event_attendees($event_id));
        
        $values = array(
            array('Financial Summary', ''),
            array('Generated', date('Y-m-d H:i:s')),
            array('', ''),
            array('Metric', 'Value'),
            array('Ticket Price', '$' . number_format($ticket_sales['price_per_ticket'], 2)),
            array('Tickets Sold', $sold_tickets),
            array('Capacity', $capacity),
            array('Total Revenue', '$' . number_format($ticket_sales['total_revenue'], 2)),
            array('Capacity Utilization', round(($sold_tickets / max($capacity, 1)) * 100, 1) . '%'),
            array('Average Revenue per Attendee', '$' . number_format($sold_tickets > 0 ? $ticket_sales['total_revenue'] / $sold_tickets : 0, 2))
        );
        
        $data = array(
            'range' => 'Financial Summary!A1:B' . (count($values)),
            'majorDimension' => 'ROWS',
            'valueInputOption' => 'USER_ENTERED',
            'values' => $values
        );
        
        $endpoint = "/{$spreadsheet_id}/values/Financial Summary!A1:B" . (count($values));
        $this->auth->make_api_request('PUT', $endpoint, $data);
    }
    
    /**
     * Store Master Report info in WordPress options
     */
    private function store_master_report_info($spreadsheet_id, $url) {
        $report_info = array(
            'spreadsheet_id' => $spreadsheet_id,
            'url' => $url,
            'created_at' => current_time('mysql'),
            'created_by' => get_current_user_id()
        );
        
        update_option('hvac_master_report_latest', $report_info);
        
        // Also store in history
        $history = get_option('hvac_master_report_history', array());
        $history[] = $report_info;
        // Keep only last 10 reports
        if (count($history) > 10) {
            $history = array_slice($history, -10);
        }
        update_option('hvac_master_report_history', $history);
    }
    
    /**
     * Store Event spreadsheet info
     */
    private function store_event_spreadsheet_info($event_id, $spreadsheet_id, $url) {
        $spreadsheet_info = array(
            'spreadsheet_id' => $spreadsheet_id,
            'url' => $url,
            'created_at' => current_time('mysql'),
            'created_by' => get_current_user_id()
        );
        
        update_post_meta($event_id, '_hvac_google_sheet', $spreadsheet_info);
    }
    
    /**
     * Get latest Master Report info
     */
    public function get_latest_master_report() {
        return get_option('hvac_master_report_latest', null);
    }
    
    /**
     * Get Master Report history
     */
    public function get_master_report_history() {
        return get_option('hvac_master_report_history', array());
    }
    
    /**
     * Get Event spreadsheet info
     */
    public function get_event_spreadsheet($event_id) {
        return get_post_meta($event_id, '_hvac_google_sheet', true);
    }
    
    /**
     * Helper: Calculate event revenue
     */
    private function calculate_event_revenue($event_id) {
        global $wpdb;
        
        // Get ticket data for this event
        $ticket_data = $wpdb->get_row($wpdb->prepare(
            "SELECT 
                COUNT(attendees.ID) as tickets_sold,
                MAX(CAST(ticket_meta.meta_value AS DECIMAL(10,2))) as ticket_price
             FROM {$wpdb->posts} tickets
             LEFT JOIN {$wpdb->posts} attendees ON tickets.ID = attendees.post_parent
             LEFT JOIN {$wpdb->postmeta} ticket_meta ON tickets.ID = ticket_meta.post_id 
                AND ticket_meta.meta_key = '_ticket_price'
             WHERE tickets.post_parent = %d 
             AND tickets.post_type = 'tribe_rsvp_tickets'",
            $event_id
        ));
        
        $price_per_ticket = $ticket_data->ticket_price ?: 0;
        $tickets_sold = $ticket_data->tickets_sold ?: 0;
        
        return array(
            'price_per_ticket' => $price_per_ticket,
            'tickets_sold' => $tickets_sold,
            'total_revenue' => $price_per_ticket * $tickets_sold
        );
    }
    
    /**
     * Helper: Get event attendees
     */
    private function get_event_attendees($event_id) {
        global $wpdb;
        
        return $wpdb->get_results($wpdb->prepare(
            "SELECT attendees.ID, attendees.post_title as name
             FROM {$wpdb->posts} tickets
             JOIN {$wpdb->posts} attendees ON tickets.ID = attendees.post_parent
             WHERE tickets.post_parent = %d 
             AND tickets.post_type = 'tribe_rsvp_tickets'
             AND attendees.post_type = 'tribe_rsvp_attendees'",
            $event_id
        ));
    }
    
    /**
     * Log info message
     */
    private function log_info($message) {
        if ($this->logger) {
            $this->logger->info($message, 'GoogleSheets');
        }
        error_log("HVAC Google Sheets: {$message}");
    }
    
    /**
     * Log error message
     */
    private function log_error($message) {
        if ($this->logger) {
            $this->logger->error($message, 'GoogleSheets');
        }
        error_log("HVAC Google Sheets Error: {$message}");
    }
    
    /**
     * Get authentication status
     */
    public function get_auth_status() {
        return $this->auth->get_config_status();
    }
    
    /**
     * Test connection
     */
    public function test_connection() {
        return $this->auth->test_connection();
    }
    
    /**
     * Move a file to a specific folder using Drive API
     */
    private function move_file_to_folder($file_id, $folder_id) {
        try {
            // Get current parents
            $file_info = $this->auth->make_drive_api_request('GET', "files/{$file_id}", null, array('fields' => 'parents'));
            
            if (!isset($file_info['parents'])) {
                return false;
            }
            
            $previous_parents = implode(',', $file_info['parents']);
            
            // Move to new folder
            $response = $this->auth->make_drive_api_request('PATCH', "files/{$file_id}", null, array(
                'addParents' => $folder_id,
                'removeParents' => $previous_parents
            ));
            
            $this->log_info("Moved file {$file_id} to folder {$folder_id}");
            return true;
            
        } catch (Exception $e) {
            $this->log_error("Failed to move file {$file_id} to folder {$folder_id}: " . $e->getMessage());
            return false;
        }
    }
}