<?php
header('Content-Type: application/json');
require_once './shikanisha.kts.php';

$response = ['status' => 'error', 'message' => 'Invalid request'];

// Get filters from request
$start_date = $_GET['start_date'] ?? date('Y-m-01');
$end_date = $_GET['end_date'] ?? date('Y-m-t');
$report_type = $_GET['report_type'] ?? 'daily';
$category = $_GET['category'] ?? 'all';

// Validate dates
if (!validateDate($start_date) || !validateDate($end_date)) {
    $response['message'] = 'Invalid date format';
    echo json_encode($response);
    exit;
}

try {
    switch ($_GET['action'] ?? '') {
        case 'get_profit_analysis':
            $data = getProfitAnalysis($conn, $start_date, $end_date, $category, $report_type);
            break;
        case 'get_employee_performance':
            $data = getEmployeePerformance($conn, $start_date, $end_date);
            break;
        case 'get_inventory_turnover':
            $data = getInventoryTurnover($conn);
            break;
        case 'get_sales_reports':
            $data = getSalesReports($conn, $start_date, $end_date, $report_type);
            break;
        default:
            $data = ['error' => 'Invalid action'];
    }

    echo json_encode($data);
} catch (Exception $e) {
    $response['message'] = 'Database error: ' . $e->getMessage();
    echo json_encode($response);
} finally {
    $conn->close();
}

function getProfitAnalysis($conn, $start_date, $end_date, $category, $report_type) {
    $category_filter = $category !== 'all' ? " AND s.category = ?" : "";
    $group_by = getGroupByClause($report_type, 'r');
    
    $sql = "SELECT 
                s.category,
                $group_by AS period,
                SUM(s.subtotal) AS total_sales,
                SUM(s.bp * s.quantity) AS total_cost,
                SUM(s.subtotal - (s.bp * s.quantity)) AS gross_profit,
                (SUM(s.subtotal - (s.bp * s.quantity)) / SUM(s.subtotal)) * 100 AS profit_margin
            FROM sales s
            JOIN transaction_receipts r ON s.trans_id = r.trans_id
            WHERE r.seller_date BETWEEN ? AND ? $category_filter
            GROUP BY s.category, $group_by
            ORDER BY $group_by, s.category";
    
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        throw new Exception("Prepare failed: " . $conn->error);
    }
    
    if ($category !== 'all') {
        $stmt->bind_param("sss", $start_date, $end_date, $category);
    } else {
        $stmt->bind_param("ss", $start_date, $end_date);
    }
    
    $stmt->execute();
    $result = $stmt->get_result();
    
    $data = [];
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    
    return $data;
}

function getEmployeePerformance($conn, $start_date, $end_date) {
    $sql = "SELECT 
                e.employee_id,
                e.name,
                e.type AS role,
                COUNT(DISTINCT r.trans_id) AS transactions_processed,
                SUM(r.total) AS total_sales,
                SUM(r.total) / COUNT(DISTINCT r.trans_id) AS avg_sale
            FROM employees e
            JOIN transaction_receipts r ON e.employee_id = r.order_employee_id
            WHERE r.seller_date BETWEEN ? AND ?
            GROUP BY e.employee_id, e.name, e.type
            ORDER BY total_sales DESC";
    
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        throw new Exception("Prepare failed: " . $conn->error);
    }
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $data = [];
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    
    return $data;
}

function getInventoryTurnover($conn) {
    // Since you don't have an inventory_turnover table, this is a placeholder
    // You would need to implement actual inventory tracking logic
    $data = [
        'message' => 'Inventory turnover tracking not implemented',
        'status' => 'info'
    ];
    
    return $data;
}

function getSalesReports($conn, $start_date, $end_date, $report_type) {
    $group_by = getGroupByClause($report_type, 'r');
    
    $sql = "SELECT 
                $group_by AS period,
                COUNT(DISTINCT r.trans_id) AS transactions,
                SUM(r.total) AS total_sales,
                SUM(r.amount_received) AS cash_received,
                COUNT(DISTINCT r.order_employee_id) AS employees,
                COUNT(DISTINCT s.product_id) AS unique_products_sold,
                SUM(s.quantity) AS total_items_sold
            FROM transaction_receipts r
            JOIN sales s ON r.trans_id = s.trans_id
            WHERE r.seller_date BETWEEN ? AND ?
            GROUP BY $group_by
            ORDER BY $group_by";
    
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        throw new Exception("Prepare failed: " . $conn->error);
    }
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $data = [];
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    
    return $data;
}

function getGroupByClause($report_type, $table_alias = '') {
    $prefix = $table_alias ? $table_alias . '.' : '';
    switch ($report_type) {
        case 'daily': return "DATE(${prefix}seller_date)";
        case 'weekly': return "YEARWEEK(${prefix}seller_date, 1)";
        case 'monthly': return "DATE_FORMAT(${prefix}seller_date, '%Y-%m')";
        case 'annual': return "YEAR(${prefix}seller_date)";
        default: return "DATE(${prefix}seller_date)";
    }
}

function validateDate($date, $format = 'Y-m-d') {
    $d = DateTime::createFromFormat($format, $date);
    return $d && $d->format($format) === $date;
}
?>