<?php
require_once '../includes/db.php';
require_once '../includes/auth.php';
requireLogin();

if (!hasRole(['owner', 'manager']) && !hasRole('superadmin')) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

$action = $_GET['action'] ?? '';
$company_id = $_SESSION['company_id'] ?? 0;

if ($action === 'material_consumption') {
    $start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
    $end_date = $_GET['end_date'] ?? date('Y-m-d');
    
    $end_date_full = $end_date . ' 23:59:59';
    $start_date_full = $start_date . ' 00:00:00';

    $sql = "
        SELECT 
            rm.sku,
            rm.name,
            rm.unit,
            SUM(rm.quantity_used) as total_quantity,
            SUM(rm.subtotal) as total_cost,
            AVG(rm.unit_price_at_time) as avg_unit_price
        FROM report_materials rm
        JOIN production_reports pr ON rm.report_id = pr.id
        WHERE pr.company_id = ?
          AND pr.finished_at >= ?
          AND pr.finished_at <= ?
        GROUP BY rm.sku, rm.name, rm.unit
        ORDER BY total_cost DESC
    ";
    
    $data = fetchAll($sql, [$company_id, $start_date_full, $end_date_full]);
    
    echo json_encode(['success' => true, 'data' => $data]);
    exit;
} elseif ($action === 'labor_cost') {
    $start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
    $end_date = $_GET['end_date'] ?? date('Y-m-d');
    
    $end_date_full = $end_date . ' 23:59:59';
    $start_date_full = $start_date . ' 00:00:00';

    $sql = "
        SELECT 
            rl.employee_id,
            rl.employee_name,
            SUM(rl.total_hours) as total_hours,
            SUM(rl.subtotal) as total_cost,
            AVG(rl.hourly_rate_at_time) as avg_hourly_rate
        FROM report_labor rl
        JOIN production_reports pr ON rl.report_id = pr.id
        WHERE pr.company_id = ?
          AND pr.finished_at >= ?
          AND pr.finished_at <= ?
        GROUP BY rl.employee_id, rl.employee_name
        ORDER BY total_cost DESC
    ";
    
    $data = fetchAll($sql, [$company_id, $start_date_full, $end_date_full]);
    
    echo json_encode(['success' => true, 'data' => $data]);
    exit;
} elseif ($action === 'production_cost') {
    $start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
    $end_date = $_GET['end_date'] ?? date('Y-m-d');
    
    $end_date_full = $end_date . ' 23:59:59';
    $start_date_full = $start_date . ' 00:00:00';

    $sql = "
        SELECT 
            pr.id,
            pr.line_name,
            pr.started_at,
            pr.finished_at,
            pr.total_units_produced,
            pr.total_units_produced_nodefects,
            pr.total_material_cost,
            pr.total_labor_cost,
            pr.grand_total_cost,
            GROUP_CONCAT(DISTINCT rp.product_name ORDER BY rp.product_name SEPARATOR ', ') as products_produced,
            (pr.grand_total_cost / NULLIF(pr.total_units_produced, 0)) as cost_per_unit
        FROM production_reports pr
        LEFT JOIN report_products rp ON rp.report_id = pr.id
        WHERE pr.company_id = ?
          AND pr.finished_at >= ?
          AND pr.finished_at <= ?
        GROUP BY pr.id, pr.line_name, pr.started_at, pr.finished_at, pr.total_units_produced, pr.total_units_produced_nodefects, pr.total_material_cost, pr.total_labor_cost, pr.grand_total_cost
        ORDER BY pr.finished_at DESC
    ";
    
    $data = fetchAll($sql, [$company_id, $start_date_full, $end_date_full]);
    
    echo json_encode(['success' => true, 'data' => $data]);
    exit;
} elseif ($action === 'product_build_cost') {
    $start_date = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
    $end_date = $_GET['end_date'] ?? date('Y-m-d');
    
    $end_date_full = $end_date . ' 23:59:59';
    $start_date_full = $start_date . ' 00:00:00';

    $sql = "
        SELECT 
            rp.product_id,
            rp.product_sku,
            rp.product_name,
            COUNT(DISTINCT rp.report_id) as total_runs,
            SUM(rp.quantity_produced) as total_quantity_produced,
            
            -- Avg labor hours per unit
            COALESCE(
                SUM(
                    (SELECT COALESCE(SUM(rl.total_hours), 0) FROM report_labor rl WHERE rl.report_id = rp.report_id) 
                    * rp.quantity_produced 
                    / NULLIF(pr.total_units_produced, 0)
                ) / NULLIF(SUM(rp.quantity_produced), 0),
                0
            ) as avg_labor_hours_per_unit,
            
            -- Avg labor cost per unit
            COALESCE(
                SUM(pr.total_labor_cost * rp.quantity_produced / NULLIF(pr.total_units_produced, 0)) / NULLIF(SUM(rp.quantity_produced), 0),
                0
            ) as avg_labor_cost_per_unit,
            
            -- Avg material cost per unit
            COALESCE(
                SUM(pr.total_material_cost * rp.quantity_produced / NULLIF(pr.total_units_produced, 0)) / NULLIF(SUM(rp.quantity_produced), 0),
                0
            ) as avg_material_cost_per_unit,
            
            -- Average total build cost per unit
            COALESCE(
                SUM(pr.grand_total_cost * rp.quantity_produced / NULLIF(pr.total_units_produced, 0)) / NULLIF(SUM(rp.quantity_produced), 0),
                0
            ) as avg_total_build_cost,
            
            -- Retail price
            COALESCE(p.base_price, 0.00) as retail_price
        FROM report_products rp
        JOIN production_reports pr ON rp.report_id = pr.id
        LEFT JOIN products p ON rp.product_id = p.id
        WHERE pr.company_id = ?
          AND pr.finished_at >= ?
          AND pr.finished_at <= ?
        GROUP BY rp.product_id, rp.product_sku, rp.product_name, p.base_price
        ORDER BY total_quantity_produced DESC
    ";
    
    $data = fetchAll($sql, [$company_id, $start_date_full, $end_date_full]);
    
    echo json_encode(['success' => true, 'data' => $data]);
    exit;
}

echo json_encode(['success' => false, 'message' => 'Invalid action']);
