<?php
require_once dirname(__DIR__, 2) . '/config/config.php';
require_once dirname(__DIR__, 2) . '/includes/db.php';

// Suppress raw PHP errors from breaking JSON output
error_reporting(0);
header('Content-Type: application/json');

$action = $_GET['action'] ?? 'get_movements';

try {
    if ($action === 'get_lookups') {
        // 1. Fetch ENUM types dynamically from database schema
        $types = [];
        $type_query = $conn->query("SHOW COLUMNS FROM stock_movements LIKE 'transaction_type'");
        if ($row = $type_query->fetch_assoc()) {
            preg_match_all("/'([^']+)'/", $row['Type'], $matches);
            $types = $matches[1] ?? [];
        }

        // 2. Fetch Stores
        $stores = [];
        $s_res = $conn->query("SELECT id, store_name as name FROM stores WHERE is_active = 1 ORDER BY store_name ASC");
        if ($s_res) { while ($l = $s_res->fetch_assoc()) { $stores[] = $l; } }

        // 3. Fetch Departments
        $depts = [];
        $d_res = $conn->query("SELECT id, department_name as name FROM departments WHERE is_active = 1 ORDER BY department_name ASC");
        if ($d_res) { while ($d = $d_res->fetch_assoc()) { $depts[] = $d; } }

        echo json_encode([
            'status' => 'success', 
            'types' => $types, 
            'stores' => $stores,
            'departments' => $depts
        ]);
        exit;
    }

    if ($action === 'get_movements') {
        // 1. Sanitize and Prepare Filters (Defaulting to today's date if empty)
        $start = !empty($_GET['start_date']) ? $conn->real_escape_string($_GET['start_date']) : date('Y-m-d');
        $end   = !empty($_GET['end_date']) ? $conn->real_escape_string($_GET['end_date']) : date('Y-m-d');
        
        $location_id = (int)($_GET['location_id'] ?? 0);
        $type        = $conn->real_escape_string($_GET['transaction_type'] ?? '');
        $search      = $conn->real_escape_string(trim($_GET['search'] ?? ''));

        // 2. Build Query Constraints safely
        $where = ["sm.movement_date >= '$start 00:00:00'", "sm.movement_date <= '$end 23:59:59'"];
        
        if (!empty($type)) {
            $where[] = "sm.transaction_type = '$type'";
        }
        
        // Location Filter
        if ($location_id > 0) {
            $where[] = "(sm.source_store_id = $location_id OR sm.dest_store_id = $location_id)";
        }

        if (!empty($search)) {
            $where[] = "(sm.reference_id LIKE '%$search%' OR p.generic_name LIKE '%$search%' OR ib.batch_number LIKE '%$search%')";
        }
        
        $where_clause = implode(" AND ", $where);

        // 3. Main Query - Using COALESCE to safely identify if destination was a Store or Department
        $sql = "
            SELECT 
                sm.id, sm.movement_date, sm.transaction_type, sm.quantity, sm.reference_id, sm.remarks,
                p.id as product_id, p.generic_name, 
                ib.id as batch_id, ib.batch_number, ib.expiry_date,
                bd.brand_name, 
                COALESCE(src.store_name, src_d.department_name) as source_name, 
                COALESCE(dst.store_name, dst_d.department_name) as dest_name,
                CONCAT(st.first_name, ' ', st.last_name) as staff_name
            FROM stock_movements sm
            INNER JOIN inventory_batches ib ON sm.batch_id = ib.id
            INNER JOIN product_variants pv ON ib.variant_id = pv.id
            INNER JOIN products p ON pv.product_id = p.id
            LEFT JOIN brand_dictionary bd ON pv.brand_id = bd.id
            LEFT JOIN stores src ON sm.source_store_id = src.id
            LEFT JOIN departments src_d ON sm.source_store_id = src_d.id
            LEFT JOIN stores dst ON sm.dest_store_id = dst.id
            LEFT JOIN departments dst_d ON sm.dest_store_id = dst_d.id
            LEFT JOIN staff st ON sm.performed_by = st.staff_id
            WHERE $where_clause
            ORDER BY sm.movement_date DESC
            LIMIT 500
        ";

        $result = $conn->query($sql);
        if (!$result) {
            throw new Exception("Database Engine Error: " . $conn->error);
        }

        $data = [];
        $kpi_in = 0; $kpi_out = 0;

        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
            // Aggregate KPIs
            if (in_array($row['transaction_type'], ['Receive', 'Opening Allocation', 'Transfer In'])) {
                $kpi_in += $row['quantity'];
            } else {
                $kpi_out += $row['quantity'];
            }
        }

        // Get Current Asset Value
        $val_res = $conn->query("SELECT SUM(quantity_on_hand * cost_price) as total_value FROM inventory_batches WHERE quantity_on_hand > 0");
        $asset_value = $val_res ? ($val_res->fetch_assoc()['total_value'] ?? 0) : 0;

        echo json_encode([
            'status' => 'success',
            'kpis' => [
                'total_tx' => count($data),
                'qty_in'   => $kpi_in,
                'qty_out'  => $kpi_out,
                'asset_value' => (float)$asset_value
            ],
            'data' => $data
        ]);
    }
} catch (Exception $e) {
    echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
}
?>