<?php
session_start();
require_once('../../../includes/config.php');
require_once('../../../includes/utility.php');

$action = isset($_REQUEST['action']) ? $_REQUEST['action'] : '';

switch($action) {
    case 'get_sales_order_data':
        getSalesOrderData($conn);
        break;
    case 'export_sales_order_data':
        exportSalesOrderData($conn);
        break;
    default:
        echo json_encode(['error' => 'Invalid action']);
        break;
}

function getSalesOrderData($conn) {
    // DataTable parameters
    $draw = isset($_POST['draw']) ? intval($_POST['draw']) : 1;
    $start = isset($_POST['start']) ? intval($_POST['start']) : 0;
    $length = isset($_POST['length']) ? intval($_POST['length']) : 25;
    $searchValue = isset($_POST['search']['value']) ? $_POST['search']['value'] : '';
    
    // Column ordering
    $columnIndex = isset($_POST['order'][0]['column']) ? intval($_POST['order'][0]['column']) : 0;
    $columnName = isset($_POST['columns'][$columnIndex]['data']) ? $_POST['columns'][$columnIndex]['data'] : 'req_date';
    $columnSortOrder = isset($_POST['order'][0]['dir']) ? $_POST['order'][0]['dir'] : 'DESC';
    
    // Custom filters
    $from_date = isset($_POST['from_date']) && !empty($_POST['from_date']) ? mysqli_real_escape_string($conn, $_POST['from_date']) : '';
    $to_date = isset($_POST['to_date']) && !empty($_POST['to_date']) ? mysqli_real_escape_string($conn, $_POST['to_date']) : '';
    $part_number = isset($_POST['part_number']) && !empty($_POST['part_number']) ? mysqli_real_escape_string($conn, $_POST['part_number']) : '';
    $customer_order_no = isset($_POST['customer_order_no']) && !empty($_POST['customer_order_no']) ? mysqli_real_escape_string($conn, $_POST['customer_order_no']) : '';
    
    // Build WHERE clause
    $where = " WHERE 1=1 ";
    
    // Date filters
    if($from_date && $to_date) {
        $where .= " AND pp.req_date BETWEEN '$from_date 00:00:00' AND '$to_date 23:59:59' ";
    } else if($from_date) {
        $where .= " AND pp.req_date >= '$from_date 00:00:00' ";
    } else if($to_date) {
        $where .= " AND pp.req_date <= '$to_date 23:59:59' ";
    }
    
    // Part number filter
    if($part_number) {
        $where .= " AND im.part_number LIKE '%$part_number%' ";
    }
    
    // Customer order number filter
    if($customer_order_no) {
        $where .= " AND pp.RefSalesCode LIKE '%$customer_order_no%' ";
    }
    
    // Global search across all columns
    if(!empty($searchValue)) {
        $searchValue = mysqli_real_escape_string($conn, $searchValue);
        $where .= " AND (
            im.part_number LIKE '%$searchValue%' OR
            im.product_description LIKE '%$searchValue%' OR
            pp.RefSalesCode LIKE '%$searchValue%' OR
            pp.req_qty LIKE '%$searchValue%'
        ) ";
    }
    
    // Get total records (without filtering)
    $totalQuery = "SELECT COUNT(DISTINCT pp.id) as total 
                   FROM production_planning AS pp
                   LEFT JOIN item_management AS im ON pp.part_id = im.id
                   WHERE 1=1";
    $totalResult = mysqli_query($conn, $totalQuery);
    $totalRecords = mysqli_fetch_assoc($totalResult)['total'];
    
    // Get filtered records count
    $filteredQuery = "SELECT COUNT(DISTINCT pp.id) as total 
                      FROM production_planning AS pp
                      LEFT JOIN item_management AS im ON pp.part_id = im.id
                      $where";
    $filteredResult = mysqli_query($conn, $filteredQuery);
    $filteredRecords = mysqli_fetch_assoc($filteredResult)['total'];
    
    // Map column names for sorting
    $sortColumns = [
        'req_date' => 'pp.req_date',
        'part_number' => 'im.part_number',
        'part_description' => 'im.product_description',
        'RefSalesCode' => 'pp.RefSalesCode',
        'current_stock' => 'pp.req_qty',
        'req_qty' => 'pp.req_qty'
    ];
    
    $orderByColumn = isset($sortColumns[$columnName]) ? $sortColumns[$columnName] : 'pp.req_date';
    
    // Main query with pagination
    $query = "SELECT 
        pp.id,
        pp.req_date,
        pp.RefSalesCode,
        pp.req_qty,
        im.part_number,
        im.product_description
    FROM production_planning AS pp
    LEFT JOIN item_management AS im ON pp.part_id = im.id
    $where
    ORDER BY $orderByColumn $columnSortOrder";
    
    // Add limit for pagination (unless showing all)
    if($length != -1) {
        $query .= " LIMIT $start, $length";
    }
    
    $result = mysqli_query($conn, $query);
    
    $data = array();
    while($row = mysqli_fetch_assoc($result)) {
        $row = utility::validate_crypt_data($row);
        
        // Format the data for DataTable
        $data[] = array(
            "req_date" => date('Y-m-d', strtotime($row['req_date'])),
            "part_number" => htmlspecialchars($row['part_number']),
            "part_description" => htmlspecialchars($row['product_description']),
            "RefSalesCode" => htmlspecialchars($row['RefSalesCode']),
            "current_stock" => number_format($row['req_qty']),
            "req_qty" => number_format($row['req_qty'])
        );
    }
    
    // Prepare JSON response
    $response = array(
        "draw" => $draw,
        "recordsTotal" => $totalRecords,
        "recordsFiltered" => $filteredRecords,
        "data" => $data
    );
    
    echo json_encode($response);
}

function exportSalesOrderData($conn) {
    // Get filter parameters
    $from_date = isset($_GET['from_date']) && !empty($_GET['from_date']) ? mysqli_real_escape_string($conn, $_GET['from_date']) : '';
    $to_date = isset($_GET['to_date']) && !empty($_GET['to_date']) ? mysqli_real_escape_string($conn, $_GET['to_date']) : '';
    $part_number = isset($_GET['part_number']) && !empty($_GET['part_number']) ? mysqli_real_escape_string($conn, $_GET['part_number']) : '';
    $customer_order_no = isset($_GET['customer_order_no']) && !empty($_GET['customer_order_no']) ? mysqli_real_escape_string($conn, $_GET['customer_order_no']) : '';
    
    // Build WHERE clause
    $where = " WHERE 1=1 ";
    
    if($from_date && $to_date) {
        $where .= " AND pp.req_date BETWEEN '$from_date 00:00:00' AND '$to_date 23:59:59' ";
    } else if($from_date) {
        $where .= " AND pp.req_date >= '$from_date 00:00:00' ";
    } else if($to_date) {
        $where .= " AND pp.req_date <= '$to_date 23:59:59' ";
    }
    
    if($part_number) {
        $where .= " AND im.part_number LIKE '%$part_number%' ";
    }
    
    if($customer_order_no) {
        $where .= " AND pp.RefSalesCode LIKE '%$customer_order_no%' ";
    }
    
    // Query to get all data for export
    $query = "SELECT 
        pp.req_date,
        im.part_number,
        im.product_description,
        pp.RefSalesCode,
        pp.req_qty
    FROM production_planning AS pp
    LEFT JOIN item_management AS im ON pp.part_id = im.id
    $where
    ORDER BY pp.req_date DESC";
    
    $result = mysqli_query($conn, $query);
    
    // Set headers for CSV download
    $filename = "sales_order_stock_report_" . date('Y-m-d_His') . ".csv";
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=' . $filename);
    
    // Create output stream
    $output = fopen('php://output', 'w');
    
    // Add BOM for Excel UTF-8 support
    fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));
    
    // Add CSV headers
    fputcsv($output, array(
        'Required Date',
        'Part Number',
        'Part Description',
        'Sales Order Number',
        'Current Stock',
        'Qty Due'
    ));
    
    // Add data rows
    while($row = mysqli_fetch_assoc($result)) {
        $row = utility::validate_crypt_data($row);
        
        fputcsv($output, array(
            date('Y-m-d', strtotime($row['req_date'])),
            $row['part_number'],
            $row['product_description'],
            $row['RefSalesCode'],
            $row['req_qty'],
            $row['req_qty']
        ));
    }
    
    fclose($output);
    exit();
}
?>