<?php
/**
 * Report Controller for Zen Zone Spa API
 * Handles all reporting and analytics functionality
 */

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../utils/Response.php';

class ReportController {
    
    private $db;
    
    public function __construct() {
        $this->db = DatabaseConfig::getConnection();
    }
    
    /**
     * Get dashboard summary data
     * GET /reports/dashboard
     */
    public function getDashboard() {
        try {
            $currentMonth = date('Y-m');
            $previousMonth = date('Y-m', strtotime('-1 month'));
            $currentYear = date('Y');
            
            // Revenue statistics (from massages)
            $revenueQuery = "
                SELECT 
                    COUNT(*) as total_sessions,
                    SUM(amount) as total_revenue,
                    AVG(amount) as average_session_value,
                    (SELECT COUNT(*) FROM massages WHERE DATE_FORMAT(session_date, '%Y-%m') = ?) as current_month_sessions,
                    (SELECT SUM(amount) FROM massages WHERE DATE_FORMAT(session_date, '%Y-%m') = ?) as current_month_revenue,
                    (SELECT COUNT(*) FROM massages WHERE DATE_FORMAT(session_date, '%Y-%m') = ?) as previous_month_sessions,
                    (SELECT SUM(amount) FROM massages WHERE DATE_FORMAT(session_date, '%Y-%m') = ?) as previous_month_revenue
                FROM massages
                WHERE DATE_FORMAT(session_date, '%Y') = ?
            ";
            
            $stmt = $this->db->prepare($revenueQuery);
            $stmt->execute([$currentMonth, $currentMonth, $previousMonth, $previousMonth, $currentYear]);
            $revenueStats = $stmt->fetch(PDO::FETCH_ASSOC);
            
            // Expense statistics
            $expenseQuery = "
                SELECT 
                    COUNT(*) as total_expenses,
                    SUM(amount) as total_amount,
                    (SELECT COUNT(*) FROM expenses WHERE DATE_FORMAT(expense_date, '%Y-%m') = ?) as current_month_expenses,
                    (SELECT SUM(amount) FROM expenses WHERE DATE_FORMAT(expense_date, '%Y-%m') = ?) as current_month_amount
                FROM expenses
                WHERE DATE_FORMAT(expense_date, '%Y') = ?
            ";
            
            $stmt = $this->db->prepare($expenseQuery);
            $stmt->execute([$currentMonth, $currentMonth, $currentYear]);
            $expenseStats = $stmt->fetch(PDO::FETCH_ASSOC);
            
            // Calculate growth percentages
            $revenueGrowth = 0;
            if ($revenueStats['previous_month_revenue'] > 0) {
                $revenueGrowth = (($revenueStats['current_month_revenue'] - $revenueStats['previous_month_revenue']) / $revenueStats['previous_month_revenue']) * 100;
            }
            
            $sessionGrowth = 0;
            if ($revenueStats['previous_month_sessions'] > 0) {
                $sessionGrowth = (($revenueStats['current_month_sessions'] - $revenueStats['previous_month_sessions']) / $revenueStats['previous_month_sessions']) * 100;
            }
            
            // Net profit/loss
            $currentMonthProfit = ($revenueStats['current_month_revenue'] ?? 0) - ($expenseStats['current_month_amount'] ?? 0);
            $yearlyProfit = ($revenueStats['total_revenue'] ?? 0) - ($expenseStats['total_amount'] ?? 0);
            
            // Top massage types this month
            $topTypesQuery = "
                SELECT 
                    mt.name,
                    COUNT(*) as session_count,
                    SUM(m.amount) as revenue
                FROM massages m
                LEFT JOIN massage_types mt ON m.massage_type_id = mt.id
                WHERE DATE_FORMAT(m.session_date, '%Y-%m') = ?
                GROUP BY m.massage_type_id, mt.name
                ORDER BY session_count DESC
                LIMIT 5
            ";
            
            $stmt = $this->db->prepare($topTypesQuery);
            $stmt->execute([$currentMonth]);
            $topMassageTypes = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Top therapists this month
            $topTherapistsQuery = "
                SELECT 
                    t.name,
                    COUNT(*) as session_count,
                    SUM(m.amount) as revenue
                FROM massages m
                LEFT JOIN therapists t ON m.therapist_id = t.id
                WHERE DATE_FORMAT(m.session_date, '%Y-%m') = ?
                GROUP BY m.therapist_id, t.name
                ORDER BY session_count DESC
                LIMIT 5
            ";
            
            $stmt = $this->db->prepare($topTherapistsQuery);
            $stmt->execute([$currentMonth]);
            $topTherapists = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Ensure all numeric values are properly typed
            $response = [
                'revenue' => [
                    'current_month' => (float)($revenueStats['current_month_revenue'] ?? 0),
                    'previous_month' => (float)($revenueStats['previous_month_revenue'] ?? 0),
                    'total_revenue' => (float)($revenueStats['total_revenue'] ?? 0),
                    'growth_percentage' => round($revenueGrowth, 2),
                    'average_session_value' => (float)($revenueStats['average_session_value'] ?? 0),
                ],
                'sessions' => [
                    'current_month' => (int)($revenueStats['current_month_sessions'] ?? 0),
                    'previous_month' => (int)($revenueStats['previous_month_sessions'] ?? 0),
                    'total_sessions' => (int)($revenueStats['total_sessions'] ?? 0),
                    'growth_percentage' => round($sessionGrowth, 2),
                ],
                'expenses' => [
                    'current_month' => (float)($expenseStats['current_month_amount'] ?? 0),
                    'total_amount' => (float)($expenseStats['total_amount'] ?? 0),
                    'total_expenses' => (int)($expenseStats['total_expenses'] ?? 0),
                ],
                'profit' => [
                    'current_month' => (float)$currentMonthProfit,
                    'yearly' => (float)$yearlyProfit,
                ],
                'period' => [
                    'current_month' => $currentMonth,
                    'previous_month' => $previousMonth,
                    'current_year' => $currentYear,
                ],
                'top_massage_types' => $topMassageTypes,
                'top_therapists' => $topTherapists
            ];
            
            Response::success($response, 'Dashboard data retrieved successfully');
            
        } catch (Exception $e) {
            error_log("Dashboard error: " . $e->getMessage());
            Response::serverError('Failed to fetch dashboard data');
        }
    }
    
    /**
     * Get revenue reports
     * GET /reports/revenue
     */
    public function getRevenue() {
        try {
            $startDate = $_GET['start_date'] ?? date('Y-m-01'); // First day of current month
            $endDate = $_GET['end_date'] ?? date('Y-m-t'); // Last day of current month
            $groupBy = $_GET['group_by'] ?? 'day'; // day, week, month
            
            // Validate groupBy parameter
            $validGroupBy = ['day', 'week', 'month'];
            if (!in_array($groupBy, $validGroupBy)) {
                $groupBy = 'day';
            }
            
            // Build date format based on groupBy
            $dateFormat = [
                'day' => '%Y-%m-%d',
                'week' => '%Y-%u', // Year-Week
                'month' => '%Y-%m'
            ][$groupBy];
            
            // Revenue by period
            $revenueQuery = "
                SELECT 
                    DATE_FORMAT(session_date, ?) as period,
                    COUNT(*) as session_count,
                    SUM(amount) as revenue,
                    AVG(amount) as average_amount
                FROM massages
                WHERE session_date BETWEEN ? AND ?
                GROUP BY DATE_FORMAT(session_date, ?)
                ORDER BY period ASC
            ";
            
            $stmt = $this->db->prepare($revenueQuery);
            $stmt->execute([$dateFormat, $startDate, $endDate, $dateFormat]);
            $revenueByPeriod = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Revenue by massage type
            $typeQuery = "
                SELECT 
                    COALESCE(mt.name, 'Unknown') as massage_type,
                    COUNT(*) as session_count,
                    SUM(m.amount) as revenue,
                    AVG(m.amount) as average_amount
                FROM massages m
                LEFT JOIN massage_types mt ON m.massage_type_id = mt.id
                WHERE m.session_date BETWEEN ? AND ?
                GROUP BY m.massage_type_id, mt.name
                ORDER BY revenue DESC
            ";
            
            $stmt = $this->db->prepare($typeQuery);
            $stmt->execute([$startDate, $endDate]);
            $revenueByType = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Revenue by therapist
            $therapistQuery = "
                SELECT 
                    COALESCE(t.name, 'Unknown') as therapist_name,
                    COUNT(*) as session_count,
                    SUM(m.amount) as revenue,
                    AVG(m.amount) as average_amount
                FROM massages m
                LEFT JOIN therapists t ON m.therapist_id = t.id
                WHERE m.session_date BETWEEN ? AND ?
                GROUP BY m.therapist_id, t.name
                ORDER BY revenue DESC
            ";
            
            $stmt = $this->db->prepare($therapistQuery);
            $stmt->execute([$startDate, $endDate]);
            $revenueByTherapist = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Summary statistics
            $totalRevenue = array_sum(array_column($revenueByPeriod, 'revenue'));
            $totalSessions = array_sum(array_column($revenueByPeriod, 'session_count'));
            $averageSessionValue = $totalSessions > 0 ? $totalRevenue / $totalSessions : 0;
            
            $report = [
                'period' => [
                    'start_date' => $startDate,
                    'end_date' => $endDate,
                    'group_by' => $groupBy
                ],
                'summary' => [
                    'total_revenue' => $totalRevenue,
                    'total_sessions' => $totalSessions,
                    'average_session_value' => round($averageSessionValue, 2)
                ],
                'revenue_by_period' => $revenueByPeriod,
                'revenue_by_type' => $revenueByType,
                'revenue_by_therapist' => $revenueByTherapist
            ];
            
            Response::success($report, 'Revenue report generated successfully');
            
        } catch (Exception $e) {
            error_log("Revenue report error: " . $e->getMessage() . " | Line: " . $e->getLine());
            Response::serverError('Failed to generate revenue report: ' . $e->getMessage());
        }
    }
    
    /**
     * Get expense reports
     * GET /reports/expenses
     */
    public function getExpenses() {
        try {
            $startDate = $_GET['start_date'] ?? date('Y-m-01');
            $endDate = $_GET['end_date'] ?? date('Y-m-t');
            $groupBy = $_GET['group_by'] ?? 'category';
            
            // Expenses by category
            $categoryQuery = "
                SELECT 
                    ec.name as category_name,
                    COUNT(*) as expense_count,
                    SUM(e.amount) as total_amount,
                    AVG(e.amount) as average_amount
                FROM expenses e
                LEFT JOIN expense_categories ec ON e.category_id = ec.id
                WHERE e.expense_date BETWEEN ? AND ?
                GROUP BY e.category_id, ec.name
                ORDER BY total_amount DESC
            ";
            
            $stmt = $this->db->prepare($categoryQuery);
            $stmt->execute([$startDate, $endDate]);
            $expensesByCategory = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Expenses by month
            $monthlyQuery = "
                SELECT 
                    DATE_FORMAT(expense_date, '%Y-%m') as month,
                    COUNT(*) as expense_count,
                    SUM(amount) as total_amount
                FROM expenses
                WHERE expense_date BETWEEN ? AND ?
                GROUP BY DATE_FORMAT(expense_date, '%Y-%m')
                ORDER BY month ASC
            ";
            
            $stmt = $this->db->prepare($monthlyQuery);
            $stmt->execute([$startDate, $endDate]);
            $expensesByMonth = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Recent large expenses
            $largeExpensesQuery = "
                SELECT 
                    e.description,
                    e.amount,
                    e.expense_date,
                    ec.name as category_name,
                    e.payment_method
                FROM expenses e
                LEFT JOIN expense_categories ec ON e.category_id = ec.id
                WHERE e.expense_date BETWEEN ? AND ?
                ORDER BY e.amount DESC
                LIMIT 10
            ";
            
            $stmt = $this->db->prepare($largeExpensesQuery);
            $stmt->execute([$startDate, $endDate]);
            $largeExpenses = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Summary
            $totalExpenses = array_sum(array_column($expensesByCategory, 'total_amount'));
            $expenseCount = array_sum(array_column($expensesByCategory, 'expense_count'));
            $averageExpense = $expenseCount > 0 ? $totalExpenses / $expenseCount : 0;
            
            $report = [
                'period' => [
                    'start_date' => $startDate,
                    'end_date' => $endDate
                ],
                'summary' => [
                    'total_expenses' => $totalExpenses,
                    'expense_count' => $expenseCount,
                    'average_expense' => round($averageExpense, 2)
                ],
                'expenses_by_category' => $expensesByCategory,
                'expenses_by_month' => $expensesByMonth,
                'largest_expenses' => $largeExpenses
            ];
            
            Response::success($report, 'Expense report generated successfully');
            
        } catch (Exception $e) {
            error_log("Expense report error: " . $e->getMessage());
            Response::serverError('Failed to generate expense report');
        }
    }
    
    /**
     * Get therapist performance reports
     * GET /reports/therapists
     */
    public function getTherapists() {
        try {
            $startDate = $_GET['start_date'] ?? date('Y-m-01');
            $endDate = $_GET['end_date'] ?? date('Y-m-t');
            
            // Therapist performance - simplified
            $performanceQuery = "
                SELECT 
                    t.id,
                    t.name,
                    t.phone,
                    COUNT(m.id) as total_sessions,
                    SUM(m.amount) as total_revenue,
                    AVG(m.amount) as average_session_value,
                    AVG(m.duration) as average_duration
                FROM therapists t
                LEFT JOIN massages m ON t.id = m.therapist_id 
                    AND m.session_date BETWEEN ? AND ?
                GROUP BY t.id, t.name, t.phone
                ORDER BY total_revenue DESC
            ";
            
            $stmt = $this->db->prepare($performanceQuery);
            $stmt->execute([$startDate, $endDate]);
            $therapistPerformance = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Massage types by therapist - simplified
            $typesQuery = "
                SELECT 
                    COALESCE(t.name, 'Unknown') as therapist_name,
                    COALESCE(mt.name, 'Unknown') as massage_type,
                    COUNT(*) as session_count,
                    SUM(m.amount) as revenue
                FROM massages m
                LEFT JOIN therapists t ON m.therapist_id = t.id
                LEFT JOIN massage_types mt ON m.massage_type_id = mt.id
                WHERE m.session_date BETWEEN ? AND ?
                GROUP BY t.id, t.name, mt.id, mt.name
                ORDER BY t.name, session_count DESC
            ";
            
            $stmt = $this->db->prepare($typesQuery);
            $stmt->execute([$startDate, $endDate]);
            $typesByTherapist = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Group types by therapist
            $therapistTypes = [];
            foreach ($typesByTherapist as $row) {
                $therapistName = $row['therapist_name'];
                if (!isset($therapistTypes[$therapistName])) {
                    $therapistTypes[$therapistName] = [];
                }
                $therapistTypes[$therapistName][] = [
                    'massage_type' => $row['massage_type'],
                    'session_count' => $row['session_count'],
                    'revenue' => $row['revenue']
                ];
            }
            
            $report = [
                'period' => [
                    'start_date' => $startDate,
                    'end_date' => $endDate
                ],
                'therapist_performance' => $therapistPerformance,
                'massage_types_by_therapist' => $therapistTypes
            ];
            
            Response::success($report, 'Therapist report generated successfully');
            
        } catch (Exception $e) {
            error_log("Therapist report error: " . $e->getMessage() . " | Line: " . $e->getLine());
            Response::serverError('Failed to generate therapist report: ' . $e->getMessage());
        }
    }
    
    /**
     * Get business analytics summary
     * GET /reports/analytics
     */
    public function getAnalytics() {
        try {
            $currentYear = date('Y');
            $previousYear = date('Y', strtotime('-1 year'));
            
            // Year-over-year comparison
            $yearComparisonQuery = "
                SELECT 
                    YEAR(session_date) as year,
                    COUNT(*) as sessions,
                    SUM(amount) as revenue
                FROM massages
                WHERE YEAR(session_date) IN (?, ?)
                GROUP BY YEAR(session_date)
                ORDER BY year
            ";
            
            $stmt = $this->db->prepare($yearComparisonQuery);
            $stmt->execute([$previousYear, $currentYear]);
            $yearComparison = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Monthly trends for current year
            $monthlyTrendsQuery = "
                SELECT 
                    DATE_FORMAT(session_date, '%Y-%m') as month,
                    COUNT(*) as sessions,
                    SUM(amount) as revenue
                FROM massages
                WHERE YEAR(session_date) = ?
                GROUP BY DATE_FORMAT(session_date, '%Y-%m')
                ORDER BY month
            ";
            
            $stmt = $this->db->prepare($monthlyTrendsQuery);
            $stmt->execute([$currentYear]);
            $monthlyTrends = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Customer frequency analysis (based on client names)
            $customerFrequencyQuery = "
                SELECT 
                    CASE 
                        WHEN session_count = 1 THEN 'One-time'
                        WHEN session_count BETWEEN 2 AND 5 THEN 'Occasional (2-5)'
                        WHEN session_count BETWEEN 6 AND 12 THEN 'Regular (6-12)'
                        ELSE 'Frequent (13+)'
                    END as customer_type,
                    COUNT(*) as customer_count
                FROM (
                    SELECT 
                        client_name,
                        COUNT(*) as session_count
                    FROM massages
                    WHERE client_name IS NOT NULL 
                        AND client_name != ''
                        AND YEAR(session_date) = ?
                    GROUP BY client_name
                ) customer_sessions
                GROUP BY customer_type
            ";
            
            $stmt = $this->db->prepare($customerFrequencyQuery);
            $stmt->execute([$currentYear]);
            $customerFrequency = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            $analytics = [
                'year_comparison' => $yearComparison,
                'monthly_trends' => $monthlyTrends,
                'customer_frequency' => $customerFrequency
            ];
            
            Response::success($analytics, 'Analytics data retrieved successfully');
            
        } catch (Exception $e) {
            error_log("Analytics error: " . $e->getMessage());
            Response::serverError('Failed to fetch analytics data');
        }
    }
}