<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\GeneralExport;
use Faker\Provider\ar_EG\Person;
use Illuminate\Support\Str;

class ReportsController extends Controller
{
    private function variables($variables = null)
    {
        $variables['header_routes'] = ' 
								<li class="breadcrumb-item">' . $variables['menu'] . '</li> 
								<li class="breadcrumb-item active">' . $variables['judul'] . '</li>
							 ';
        return $variables;
    }

    function sqlUnBindgings($query, $bindings)
    {
        echo '<pre>';
        foreach ($bindings as $key => $value) {
            // Escape string
            if (is_string($value)) {
                $value = "'" . addslashes($value) . "'";
            } elseif (is_null($value)) {
                $value = 'NULL';
            }

            // Replace named parameters
            if (is_string($key)) {
                $query = str_replace(':' . $key, $value, $query);
            } else {
                // Positional binding (if used)
                $query = preg_replace('/\?/', $value, $query, 1);
            }
        }

        return $query;
    }
 
    public function system_logs(Request $request)
    {
        // Base SQL
        $sql = "SELECT  
                b.name AS store_name,
                --split_part(a.log_key, '#', 1) AS store_id,
                split_part(a.log_key, '#', 2) AS key,
                a.status_code AS code,
                a.message AS msg,
                to_char(a.created_at, 'YYYY-MM-DD HH24:MI:SS') as created
                FROM system_logs a
                JOIN m_stores b on b.id=split_part(a.log_key, '#', 1)::bigint
                WHERE DATE(a.created_at) BETWEEN :start_date AND :end_date";

        $variables = array(
            'menu' => 'Configurations',
            'judul' => 'System Logs',
            'f_columns' => [
                "b.name"  => 'Store Name',
                "split_part(a.log_key, '#', 2)"  => 'Key',
                "a.status_code"  => 'Code',
                "a.message"  => 'Message',
                "to_char(a.created_at, 'YYYY-MM-DD HH24:MI:SS')"  => 'Created'
            ],
            'f_column_class_name' => [],
            'link_' => route('reports.system_logs'),
            'sql' => $sql,
            'bindings' => [],
            'default_order' => "to_char(a.created_at, 'YYYY-MM-DD HH24:MI') desc,log_key,idx",
            'export_filename' => "system_logs.xlsx",

        );

        return  $this->general_report($request, $variables);
    }

    public function system_errors(Request $request)
    {
        // Base SQL
        $sql = "SELECT   
                a.type,
                a.message,
                concat(a.file,' line ',a.line) AS file,
                -- a.trace,
                to_char(a.created_at, 'YYYY-MM-DD HH24:MI:SS') AS created
                from system_errors a
                WHERE DATE(a.created_at) BETWEEN :start_date AND :end_date";

        return  $this->general_report($request, $variables);
    }

    // ===== LSM REPORTS =====
    
    public function lsm_report(Request $request)
    {
        // Base SQL untuk LSM Report
        $sql = "SELECT  
                a.so_number,
                b.name AS store_name,
                a.so_date,
                c.fullname AS pic_input,
                COALESCE(d.total_items, 0) AS total_items,
                COALESCE(d.items_empty, 0) AS items_empty,
                COALESCE(d.items_suggested, 0) AS items_suggested,
                a.status,
                to_char(a.created_at, 'YYYY-MM-DD HH24:MI:SS') AS created_at
                FROM t_stock_opnames a
                LEFT JOIN m_stores b ON b.id = a.store_id
                LEFT JOIN users c ON c.id = a.created_by
                LEFT JOIN (
                    SELECT 
                        so_id,
                        COUNT(*) AS total_items,
                        COUNT(CASE WHEN qty_total = 0 THEN 1 END) AS items_empty,
                        COUNT(CASE WHEN qty_total > 0 THEN 1 END) AS items_suggested
                    FROM t_stock_opname_details 
                    GROUP BY so_id
                ) d ON d.so_id = a.id
                WHERE a.delete_flag = 0 
                AND DATE(a.so_date) BETWEEN :start_date AND :end_date";

        $variables = array(
            'menu' => 'Reports',
            'judul' => 'LSM Report',
            'f_columns' => [
                "a.so_number" => 'SO Number',
                "b.name" => 'Store Name',
                "a.so_date" => 'SO Date',
                "c.fullname" => 'PIC Input',
                "COALESCE(d.total_items, 0)" => 'Total Items',
                "COALESCE(d.items_empty, 0)" => 'Items Empty',
                "COALESCE(d.items_suggested, 0)" => 'Items Suggested',
                "a.status" => 'Status',
                "to_char(a.created_at, 'YYYY-MM-DD HH24:MI:SS')" => 'Created At'
            ],
            'f_column_class_name' => [],
            'link_' => route('reports.lsm_report'),
            'sql' => $sql,
            'bindings' => [],
            'default_order' => "a.so_date desc, a.created_at desc",
            'export_filename' => "lsm_report.xlsx",
        );

        return $this->general_report($request, $variables);
    }

    public function spb_report(Request $request)
    {
        // Base SQL untuk SPB Report
        $sql = "SELECT  
                a.spb_number,
                b.name AS store_name,
                a.request_type,
                a.request_date,
                a.delivery_date,
                COALESCE(d.total_items, 0) AS total_items,
                COALESCE(d.total_qty, 0) AS total_qty,
                a.status,
                c.fullname AS created_by,
                to_char(a.created_at, 'YYYY-MM-DD HH24:MI:SS') AS created_at
                FROM t_item_requests a
                LEFT JOIN m_stores b ON b.id = a.store_id
                LEFT JOIN users c ON c.id = a.created_by
                LEFT JOIN (
                    SELECT 
                        item_request_id,
                        COUNT(*) AS total_items,
                        SUM(final_qty) AS total_qty
                    FROM t_item_request_details 
                    GROUP BY item_request_id
                ) d ON d.item_request_id = a.id
                WHERE a.delete_flag = 0 
                AND DATE(a.request_date) BETWEEN :start_date AND :end_date";

        $variables = array(
            'menu' => 'Reports',
            'judul' => 'SPB Report',
            'f_columns' => [
                "a.spb_number" => 'SPB Number',
                "b.name" => 'Store Name',
                "a.request_type" => 'Request Type',
                "a.request_date" => 'Request Date',
                "a.delivery_date" => 'Delivery Date',
                "COALESCE(d.total_items, 0)" => 'Total Items',
                "COALESCE(d.total_qty, 0)" => 'Total Qty',
                "a.status" => 'Status',
                "c.fullname" => 'Created By',
                "to_char(a.created_at, 'YYYY-MM-DD HH24:MI:SS')" => 'Created At'
            ],
            'f_column_class_name' => [],
            'link_' => route('reports.spb_report'),
            'sql' => $sql,
            'bindings' => [],
            'default_order' => "a.request_date desc, a.created_at desc",
            'export_filename' => "spb_report.xlsx",
        );

        return $this->general_report($request, $variables);
    }

    public function doi_report(Request $request)
    {
        // Base SQL untuk DOI Report
        $sql = "SELECT  
                a.so_number,
                b.name AS store_name,
                c.code AS material_code,
                c.name AS material_name,
                c.uom,
                d.qty_total AS ending_stock,
                COALESCE(f.target_qty, 0) AS target_qty,
                CASE 
                    WHEN COALESCE(f.target_qty, 0) > 0 THEN 
                        ROUND(d.qty_total / f.target_qty, 2)
                    ELSE 0 
                END AS doi_ratio,
                CASE 
                    WHEN COALESCE(f.target_qty, 0) = 0 THEN 'No Target'
                    WHEN d.qty_total / f.target_qty < 1 THEN 'Critical'
                    WHEN d.qty_total / f.target_qty < 2 THEN 'Normal'
                    ELSE 'Overstock'
                END AS doi_status,
                a.so_date
                FROM t_stock_opnames a
                LEFT JOIN m_stores b ON b.id = a.store_id
                LEFT JOIN t_stock_opname_details d ON d.so_id = a.id
                LEFT JOIN m_raw_materials c ON c.id = d.raw_material_id
                LEFT JOIN m_target_sisa_malam f ON f.store_id = a.store_id AND f.raw_material_id = d.raw_material_id AND f.status = 'Active'
                WHERE a.delete_flag = 0 
                AND a.status = 'Submitted'
                AND DATE(a.so_date) BETWEEN :start_date AND :end_date";

        $variables = array(
            'menu' => 'Reports',
            'judul' => 'DOI Report',
            'f_columns' => [
                "a.so_number" => 'SO Number',
                "b.name" => 'Store Name',
                "c.code" => 'Material Code',
                "c.name" => 'Material Name',
                "c.uom" => 'UOM',
                "d.qty_total" => 'Ending Stock',
                "COALESCE(f.target_qty, 0)" => 'Target Qty',
                "CASE WHEN COALESCE(f.target_qty, 0) > 0 THEN ROUND(d.qty_total / f.target_qty, 2) ELSE 0 END" => 'DOI Ratio',
                "CASE WHEN COALESCE(f.target_qty, 0) = 0 THEN 'No Target' WHEN d.qty_total / f.target_qty < 1 THEN 'Critical' WHEN d.qty_total / f.target_qty < 2 THEN 'Normal' ELSE 'Overstock' END" => 'DOI Status',
                "a.so_date" => 'SO Date'
            ],
            'f_column_class_name' => [],
            'link_' => route('reports.doi_report'),
            'sql' => $sql,
            'bindings' => [],
            'default_order' => "a.so_date desc, b.name, c.code",
            'export_filename' => "doi_report.xlsx",
        );

        return $this->general_report($request, $variables);
    }

    public function general_report($request, $variables)
    {
        $variables['layout'] = 'layouts.base_view';
        $variables['f_operators'] = ["=", "!=", ">", ">=", "<", "<=", "ILIKE"];
        $variables['column_keys'] =  array_keys($variables['f_columns']);
        $variables['column_values'] =  array_values($variables['f_columns']);

        if (!$request->has('type')) {
            return view('reports.general_report', [
                'variables' => $this->variables($variables)
            ]);
        }

        // Filter default tanggal
        $start_date = $request->start_date ?? date('Y-m-01');
        $end_date = $request->end_date ?? date('Y-m-d');

        $bindings = [
            'start_date' => $start_date,
            'end_date' => $end_date,
        ];

        if ($variables['bindings']) {
            $bindings = array_merge($bindings, $variables['bindings']);
        }

        // Base SQL
        $sql = $variables['sql'];

        //filter header
        if ($request->has('filters')) {
            // Decode JSON ke array
            $filters = $request->filters;
            if (is_string($filters)) {
                $filters = json_decode($filters, true);
            }

            // Cek kalau decode berhasil
            if (is_array($filters)) {
                foreach ($filters as $index => $filter) {
                    if (!isset($filter['column'], $filter['operator'], $filter['value'])) {
                        continue;
                    }

                    $col = $filter['column'];
                    $op = strtoupper($filter['operator']);
                    $paramKey = "filter_{$index}";
                    if (in_array($op, $variables['f_operators'])) {
                        $sql .= " AND {$col} {$op} :{$paramKey}";
                        $bindings[$paramKey] = $op === 'ILIKE' ? "%{$filter['value']}%" : $filter['value'];
                    }
                }
            }
        }

        // inputan Search filter
        if (($request->has('search') && $request->search['value'] != '') || $request->has('search_excel') && $request->search_excel != '') {
            $search = $request->search['value'] ?? $request->search_excel;
            $bindings['search'] = "%{$search}%";

            $whereParts = [];
            foreach (array_keys($variables['f_columns']) as $col) {
                $whereParts[] = "{$col}::TEXT ILIKE :search";
            }

            // Gabungkan semua kondisi OR jadi satu string
            $sql .= " AND (" . implode(" OR ", $whereParts) . ")";
        }

        // Total data //taruh disini saat variable $sql belum di limit
        $count_sql = "SELECT COUNT(0) AS total FROM ({$sql}) AS count_query";
        $total = DB::selectOne($count_sql, $bindings)->total;

        // Sorting dan Paging (untuk datatable list)
        if ($request->type == 'list') {
            // $columns = array('a.id', array_keys($columns)); //agar order by kolom 0 yg di set di datable diarahinnya ke kolom id
            $columns = array_keys($variables['f_columns']); //agar order by kolom 0 yg di set di datable diarahinnya ke kolom id
            $orderColumn = $columns[$request->order[0]['column']];
            $orderDir = $request->order[0]['dir'];

            if ($orderDir == 'default_') { //akalin di datable nya untuk pertamakali load
                $sql .= " ORDER BY {$variables['default_order']}";
            } else {
                $sql .= " ORDER BY {$orderColumn} {$orderDir}";
            }

            $start = (int)$request->start;
            $length = (int)$request->length;
            $sql .= " OFFSET {$start} LIMIT {$length}";
            // print_r($this->sqlUnBindgings($sql, $bindings));
            // exit;
            $data = DB::select($sql, $bindings);

            $result = [];
            $no = $start + 1;
            switch ($variables['judul']) { 
                case 'System Logs':
                    foreach ($data as $row) {
                        $result[] = [
                            $no++,
                            $row->created,
                            $row->store_name,
                            $row->key,
                            $row->code,
                            wordwrap($row->msg, 150, "<br>\n", true),
                        ];
                    }
                    break;
                case 'System Errors':
                    $projectName = basename(base_path());
                    foreach ($data as $row) {
                        $result[] = [
                            $no++,
                            $row->created,
                            wordwrap($row->type, 15, "<br>\n", true),
                            wordwrap($row->file, 30, "<br>\n", true),
                            // Str::after($row->file, $projectName . DIRECTORY_SEPARATOR),
                            $row->message,
                        ];
                    }
                    break;
                case 'LSM Report':
                    foreach ($data as $row) {
                        $result[] = [
                            $no++,
                            '<a href="' . route('stock_opnames.show', ['stockOpname' => $row->so_number]) . '">' . $row->so_number . '</a>',
                            $row->store_name,
                            $row->so_date,
                            $row->pic_input,
                            $row->total_items,
                            $row->items_empty,
                            $row->items_suggested,
                            '<span class="badge badge-' . ($row->status == 'Submitted' ? 'success' : 'warning') . '">' . $row->status . '</span>',
                            $row->created_at,
                        ];
                    }
                    break;
                case 'SPB Report':
                    foreach ($data as $row) {
                        $result[] = [
                            $no++,
                            '<a href="' . route('item_requests.show', ['itemRequest' => $row->spb_number]) . '">' . $row->spb_number . '</a>',
                            $row->store_name,
                            '<span class="badge badge-' . ($row->request_type == 'Regular' ? 'info' : 'dark') . '">' . $row->request_type . '</span>',
                            $row->request_date,
                            $row->delivery_date ?: '-',
                            $row->total_items,
                            number_format($row->total_qty, 2),
                            '<span class="badge badge-' . ($row->status == 'Approved' ? 'success' : ($row->status == 'Submitted' ? 'primary' : 'warning')) . '">' . $row->status . '</span>',
                            $row->created_by,
                            $row->created_at,
                        ];
                    }
                    break;
                case 'DOI Report':
                    foreach ($data as $row) {
                        $doiClass = 'secondary';
                        if ($row->doi_status == 'Critical') $doiClass = 'danger';
                        elseif ($row->doi_status == 'Normal') $doiClass = 'warning';
                        elseif ($row->doi_status == 'Overstock') $doiClass = 'success';
                        
                        $result[] = [
                            $no++,
                            $row->so_number,
                            $row->store_name,
                            $row->material_code,
                            $row->material_name,
                            $row->uom,
                            number_format($row->ending_stock, 2),
                            number_format($row->target_qty, 2),
                            $row->doi_ratio,
                            '<span class="badge badge-' . $doiClass . '">' . $row->doi_status . '</span>',
                            $row->so_date,
                        ];
                    }
                    break;
            }

            return response()->json([
                "draw" => intval($request->draw),
                "recordsTotal" => $total,
                "recordsFiltered" => $total,
                "data" => $result,
            ]);
        } else if ($request->type == 'excel_all') {
            $sql .= " ORDER BY " . $variables['default_order'];
            $data = DB::select($sql, $bindings);
            return Excel::download(new GeneralExport(collect($data), $variables['judul'], array_merge(array('No'), $variables['column_values'])), $variables['export_filename']);
            // return Excel::download(new BalanceHistoriesExport(collect($data)), 'balance_histories.xlsx');
        } else if ($request->type == 'print_all') {
            $sql .= " ORDER BY " . $variables['default_order'];
            $data = DB::select($sql, $bindings);
            $variables['layout'] = 'layouts.base_print';
            $print_data = '';
            switch ($variables['judul']) { 
                case 'System Logs':
                    $no = 1;
                    foreach ($data as $row) {
                        $print_data .= "<tr>";
                        $print_data .= "<td>" . $no++ . "</td>";
                        $print_data .= "<td>" . e($row->created) . "</td>";
                        $print_data .= "<td>" . e($row->store_name) . "</td>";
                        $print_data .= "<td>" . e($row->key) . "</td>";
                        $print_data .= "<td>" . e($row->code) . "</td>";
                        $print_data .= "<td>" . wordwrap(e($row->msg), 150, "<br>\n", true) . "</td>";
                        $print_data .= "</tr>";
                    }
                    break;
                case 'System Errors':
                    $no = 1;
                    foreach ($data as $row) {
                        $print_data .= "<tr>";
                        $print_data .= "<td>" . $no++ . "</td>";
                        $print_data .= "<td>" . e($row->created) . "</td>";
                        $print_data .= "<td>" . wordwrap(e($row->type), 15, "<br>\n", true) . "</td>";
                        $print_data .= "<td>" . wordwrap(e($row->file), 30, "<br>\n", true) . "</td>";
                        $print_data .= "<td>" . e($row->message) . "</td>";
                        $print_data .= "</tr>";
                    }
                    break;
                case 'LSM Report':
                    $no = 1;
                    foreach ($data as $row) {
                        $print_data .= "<tr>";
                        $print_data .= "<td>" . $no++ . "</td>";
                        $print_data .= "<td>" . e($row->so_number) . "</td>";
                        $print_data .= "<td>" . e($row->store_name) . "</td>";
                        $print_data .= "<td>" . e($row->so_date) . "</td>";
                        $print_data .= "<td>" . e($row->pic_input) . "</td>";
                        $print_data .= "<td>" . e($row->total_items) . "</td>";
                        $print_data .= "<td>" . e($row->items_empty) . "</td>";
                        $print_data .= "<td>" . e($row->items_suggested) . "</td>";
                        $print_data .= "<td>" . e($row->status) . "</td>";
                        $print_data .= "<td>" . e($row->created_at) . "</td>";
                        $print_data .= "</tr>";
                    }
                    break;
                case 'SPB Report':
                    $no = 1;
                    foreach ($data as $row) {
                        $print_data .= "<tr>";
                        $print_data .= "<td>" . $no++ . "</td>";
                        $print_data .= "<td>" . e($row->spb_number) . "</td>";
                        $print_data .= "<td>" . e($row->store_name) . "</td>";
                        $print_data .= "<td>" . e($row->request_type) . "</td>";
                        $print_data .= "<td>" . e($row->request_date) . "</td>";
                        $print_data .= "<td>" . e($row->delivery_date ?: '-') . "</td>";
                        $print_data .= "<td>" . e($row->total_items) . "</td>";
                        $print_data .= "<td>" . number_format($row->total_qty, 2) . "</td>";
                        $print_data .= "<td>" . e($row->status) . "</td>";
                        $print_data .= "<td>" . e($row->created_by) . "</td>";
                        $print_data .= "<td>" . e($row->created_at) . "</td>";
                        $print_data .= "</tr>";
                    }
                    break;
                case 'DO Report':
                    $no = 1;
                    foreach ($data as $row) {
                        $print_data .= "<tr>";
                        $print_data .= "<td>" . $no++ . "</td>";
                        $print_data .= "<td>" . e($row->do_number) . "</td>";
                        $print_data .= "<td>" . e($row->spb_number) . "</td>";
                        $print_data .= "<td>" . e($row->store_name) . "</td>";
                        $print_data .= "<td>" . e($row->do_date) . "</td>";
                        $print_data .= "<td>" . e($row->total_items) . "</td>";
                        $print_data .= "<td>" . number_format($row->total_qty, 2) . "</td>";
                        $print_data .= "<td>" . e($row->status) . "</td>";
                        $print_data .= "<td>" . e($row->csv_generated) . "</td>";
                        $print_data .= "<td>" . e($row->created_by) . "</td>";
                        $print_data .= "<td>" . e($row->created_at) . "</td>";
                        $print_data .= "</tr>";
                    }
                    break;
                case 'DOI Report':
                    $no = 1;
                    foreach ($data as $row) {
                        $print_data .= "<tr>";
                        $print_data .= "<td>" . $no++ . "</td>";
                        $print_data .= "<td>" . e($row->so_number) . "</td>";
                        $print_data .= "<td>" . e($row->store_name) . "</td>";
                        $print_data .= "<td>" . e($row->material_code) . "</td>";
                        $print_data .= "<td>" . e($row->material_name) . "</td>";
                        $print_data .= "<td>" . e($row->uom) . "</td>";
                        $print_data .= "<td>" . number_format($row->ending_stock, 2) . "</td>";
                        $print_data .= "<td>" . number_format($row->target_qty, 2) . "</td>";
                        $print_data .= "<td>" . e($row->doi_ratio) . "</td>";
                        $print_data .= "<td>" . e($row->doi_status) . "</td>";
                        $print_data .= "<td>" . e($row->so_date) . "</td>";
                        $print_data .= "</tr>";
                    }
                    break;
                default:
                    # code...
                    break;
            }
            return view('reports.general_report', [
                'variables' => $this->variables($variables),
                'print_data' => $print_data
            ]);
        }
    }
}
