<?php

namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\DB;
use Validator;
use Auth;
use App\Responses;
use App\Helper;
use Carbon\Carbon;
use Carbon\CarbonPeriod;
use App\Models\UserLogs;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Contracts\Encryption\DecryptException;
use Illuminate\Support\Facades\Log;
use App\Models\ConfTruckTypes;

class ReportsController extends Controller
{
    public function view_report_vehicle_trips(Request $req)
    {
        $q = "SELECT id, nopol1 from t_vehicles WHERE dlt is null order by nopol1";
        $vehicletype = DB::table('t_vehicles_cats')->where('is_active', 1)->get();
        $listNopol = DB::select($q);
        $listpool = DB::table('t_conf_pool')->where('dlt', null)->get();

        $data = [
            'listNopol' => $listNopol,
            'vehicletype' => $vehicletype,
            'listpool' => $listpool
        ];

        $log = [
            "module" => "Vehicle Trips Report",
            "action" => "View",
            "desc" => "Open Vehicle Trips Report menu",
        ];
        UserLogs::insert(Auth::user()->id, $log);
        return view('menu_v1.reports.vehicle_trips', $data);
    }
    public function api_report_vehicle_trips_list(Request $req)
    {
        // $TIMEFIX = Helper::TIMEFIX;
        // Validate input
        // date in unix datetime format
        // dd($req->type);
        $rules = [
            // 'from_date' => 'required|date',
            // 'to_date' => 'required|date|after_or_equal:from_date',
            'type' => 'nullable|in:report,list', // enum "report", "list". nullable default "list"
        ];

        $isValidInput = Validator::make($req->all(), $rules);
        if (!$isValidInput->passes()) {
            $apiResp = Responses::bad_input($isValidInput->messages()->first());
            return new Response($apiResp, $apiResp["meta"]["code"]);
        }

        // $from_date = $req->input('from_date');
        // $to_date = $req->input('to_date');
        $from_date = $req->input('from_date') - Helper::TIMEFIX;
        $to_date = $req->input('to_date') - Helper::TIMEFIX;
        $vid = $req->input('vid');
        $vt = $req->input('vt');
        $poolcode = $req->input('poolcode');
        // $from_date = 1756054800;
        // $to_date = 1756745940;

        // get month year
        $date = Carbon::createFromTimestamp($from_date);
        $yymm = $date->format('ym');

        try {
            // $list = DB::select("WITH
            // 	gaps AS (
            // 		SELECT
            // 			-- previous gap since previous row > 1 hour (3600s)
            // 			CASE
            // 				WHEN (crt_d - LAG(crt_d, 1, NULL) OVER (PARTITION BY vhc_id ORDER BY crt_d)) > 3600
            // 				THEN 1 ELSE 0
            // 			END AS isStop,
            // 			t.*
            // 		FROM tracks_2509 t
            // 		WHERE
            // 		t.latitude IS NOT NULL
            // 		AND t.longitude IS NOT NULL
            // 		AND t.action = 'location'
            // 		AND t.crt_d BETWEEN ? AND ?
            // 	)
            // 	, trips AS (
            // 		SELECT
            // 			-- mark the start of a trip when ignition=4 and previous ignition <> 4
            // 			CASE
            // 				WHEN ignition = 4
            // 					AND LAG(ignition, 1, 0) OVER (PARTITION BY vhc_id ORDER BY crt_d) <> 4
            // 					or LAG(isStop, 1, 0) over (PARTITION BY vhc_id ORDER BY crt_d) = 1
            // 				THEN 1 ELSE 0
            // 			END AS trip_start,
            // 			g.*
            // 		FROM gaps g
            // 	)
            // 	, numbered AS (
            // 		SELECT
            // 			*,
            // 			-- assign a trip_id by cumulative sum of trip_start
            // 			SUM(trip_start) OVER (PARTITION BY vhc_id ORDER BY crt_d) AS trip_id
            // 		FROM trips
            // 		where
            // 			ignition = 4
            // 			and isStop = 0
            // 	),
            // 	agg AS (
            // 		SELECT
            // 			COUNT(*) AS row_count,
            // 			v.name,
            // 			v.nopol1,
            // 			vhc_id,
            // 			-- trip_id,
            // 			ROW_NUMBER() OVER (PARTITION BY v.id ORDER BY MIN(a.crt_d)) AS trip_id,
            // 			SUM(pre_milleage) AS mileage,
            // 			MIN(a.crt_d) AS start,
            // 			MAX(a.crt_d) AS finish,
            // 			MIN(a.vhc_milleage) AS startMileage,
            // 			MAX(a.vhc_milleage) AS finishMileage,
            // 			(SELECT fulladdress FROM t_gps_tracks_address WHERE master_id = MIN(a.id) LIMIT 1) AS startLoc,
            // 			(SELECT fulladdress FROM t_gps_tracks_address WHERE master_id = MAX(a.id) LIMIT 1) AS finishLoc
            // 		FROM t_vehicles v
            // 		LEFT JOIN numbered a ON a.vhc_id = v.id
            // 		WHERE
            // 			v.dlt is null and trip_id != 0
            // 			and if(? , v.id = ? , 1=1)
            // 		GROUP BY v.id, a.trip_id
            // 		HAVING COUNT(*) > 1
            // 	)
            // 	SELECT
            // 		*,
            // 		SUM(mileage) OVER (PARTITION BY agg.id) AS total_mileage,
            // 		COUNT(trip_id) OVER (PARTITION BY agg.id) AS total_trip,
            // 		tvd.pool_code, tvd.dc_code
            // 	FROM agg agg
            // 		join t_vehicles_detail tvd on tvd.vid = agg.vhc_id
            // 	ORDER BY agg.id, trip_id
            // ", [$from_date, $to_date, $vid, $vid]);

            //     $list = DB::select("WITH TotalMileage AS (
            //         SELECT id, SUM(mileage) AS total_mileage, count(*) total_trip
            //         FROM trips
            //         WHERE start BETWEEN ? AND ?
            //         GROUP BY id
            //     )
            //     SELECT
            //         t.*,
            //         tm.total_mileage, total_trip,
            //         vt.name as type_name,
            //         ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY t.start) AS trip_id
            //     FROM trips t
            //         JOIN TotalMileage tm ON t.id = tm.id
            //         LEFT JOIN t_vehicles tvt ON tvt.id = t.vhc_id
            //         LEFT JOIN t_vehicles_cats vt ON vt.id = tvt.cat_id
            //     WHERE
            //         t.start BETWEEN ? AND ?
            //         and if(? , t.id = ? , 1=1)
            //         and if(? , vt.id = ? , 1=1)
            //         and if(? , t.pool_code = ? , 1=1)
            // ", [$from_date, $to_date, $from_date, $to_date, $vid, $vid, $vt, $vt, $poolcode, $poolcode]);
            $sql = "WITH TotalMileage AS (
            SELECT id, SUM(mileage) AS total_mileage, count(*) total_trip
            FROM trips
            WHERE start BETWEEN ? AND ?
            GROUP BY id
        )
        SELECT
            t.*,
            tm.total_mileage, total_trip,
            vt.name as type_name,
            ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY t.start) AS trip_id
        FROM trips t
            JOIN TotalMileage tm ON t.id = tm.id
            LEFT JOIN t_vehicles tvt ON tvt.id = t.vhc_id
            LEFT JOIN t_vehicles_cats vt ON vt.id = tvt.cat_id
        WHERE
            t.start BETWEEN ? AND ?
            and (? IS NULL or t.id = ?)
            and (? IS NULL or vt.id = ?)
            and (? IS NULL or t.pool_code = ?)
    ";

            $vid = $vid ?: null;
            $vt = $vt ?: null;
            $poolcode = $poolcode ?: null;

            $list = DB::select($sql, [
                $from_date,
                $to_date,
                $from_date,
                $to_date,
                $vid,
                $vid,
                $vt,
                $vt,
                $poolcode,
                $poolcode,
            ]);
            // // RETURN 1 - LIST
            // if($req->type != 'report'){
            $apiResp = Responses::success("success list vehicles report");
            $apiResp["data"] = $list;
            return new Response($apiResp, $apiResp["meta"]["code"]);
            // }

            // // RETURN 2 - REPORT
            // if($req->type == 'report'){
            // 	$headings = ['Name', 'License Plate', 'Number of Trip', 'Total Mileage'];

            // 	$export = new class($list, $headings) implements FromArray, WithHeadings {
            // 		private $list;
            // 		private $headings;

            // 		public function __construct($list, $headings)
            // 		{
            // 			$this->list = $list;
            // 			$this->headings = $headings;
            // 		}

            // 		public function array(): array
            // 		{
            // 			return array_map(function ($item) {
            // 				return [
            // 					$item->name,
            // 					$item->nopol1,
            // 					$item->numOfTrip,
            // 					$item->total_milleage,
            // 				];
            // 			}, $this->list);
            // 		}

            // 		public function headings(): array
            // 		{
            // 			return $this->headings;
            // 		}

            // 		// Start table from A3
            // 		public function startCell(): string
            // 		{
            // 			return 'A3';
            // 		}

            // 		// Add title & border styling
            // 		public function styles(Worksheet $sheet)
            // 		{
            // 			$tgl0 = date('d-m-Y', $GLOBALS['from_date']);
            // 			$tgl1 = date('d-m-Y', $GLOBALS['to_date']);

            // 			// Title in A1
            // 			$sheet->setCellValue('A1', 'Vehicle Trip Report $tgl0 until $tgl1');
            // 			$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
            // 			$sheet->getStyle('A1')->getAlignment()->setHorizontal('center');
            // 			$sheet->mergeCells('A1:D1'); // Merge across 4 columns

            // 			// Get last row
            // 			$lastRow = $this->list->count() + 3; // 3 = heading row
            // 			$range = "A3:D{$lastRow}";

            // 			// Add borders
            // 			$sheet->getStyle($range)->getBorders()->getAllBorders()
            // 				->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);

            // 			// Bold headings
            // 			$sheet->getStyle('A3:D3')->getFont()->setBold(true);

            // 			return [];
            // 		}
            // 	};

            // 	return Excel::download($export, 'trip_report.xlsx');
            // }
        } catch (\Exception $e) {
            $apiResp = Responses::error($e->getMessage());
            return new Response($apiResp, $apiResp["meta"]["code"]);
            // return Responses::json(Responses::SERVER_ERROR, 'An error occurred while generating the report.', (object)[]);
        }
    }
    public function view_report_trip_detail(Request $req)
    {
        $vid = $req->vid;
        $tgl0 = $req->tgl0;
        $tgl1 = $req->tgl1;
        $nopol1 = $req->nopol1;

        $d = [$vid, $tgl0, $tgl1];
        $list = DB::select("SELECT
				t.crt_d , t.latitude, t.longitude, t.speed,
				tgta.fulladdress,
				t.pre_milleage, t.vhc_milleage, fuel_count
			FROM
				t_gps_tracks t
				left join t_gps_tracks_address tgta on tgta.master_id = t.id
			WHERE
				t.vhc_id = ?
				and t.latitude IS NOT NULL
				AND t.longitude IS NOT NULL
				AND t.action = 'location'
				AND t.crt_d BETWEEN ? AND ?
			ORDER BY t.crt_d asc
		", $d);

        $start = [
            'time' => $list[0]->crt_d,
            'fulladdress' => urldecode($list[0]->fulladdress),
            'mileage' => $list[0]->vhc_milleage,
        ];
        $finish = [
            'time' => $list[count($list) - 1]->crt_d,
            'fulladdress' => urldecode($list[count($list) - 1]->fulladdress),
            'mileage' => $list[count($list) - 1]->vhc_milleage,
        ];

        $t0 = Carbon::createFromTimestamp($list[0]->crt_d);
        $t1 = Carbon::createFromTimestamp($list[count($list) - 1]->crt_d);
        $diff = $t1->diff($t0);
        $hours = $diff->h + ($diff->days * 24); // include days converted to hours
        $minutes = $diff->i;
        $duration = "{$hours} hour" . ($hours > 1 ? 's' : '') . " {$minutes} minute" . ($minutes > 1 ? 's' : '');

        $distance = $list[count($list) - 1]->vhc_milleage - $list[0]->vhc_milleage;
        $fuel_consumed = $list[count($list) - 1]->fuel_count - $list[0]->fuel_count;

        $data = [
            'nopol1' => $nopol1,
            'vid' => $vid,
            'tgl0' => $tgl0,
            'tgl1' => $tgl1,
            'list' => $list,
            'start' => $start,
            'finish' => $finish,
            'duration' => $duration,
            'distance' => $distance,
            'fuel_consumed' => $fuel_consumed,
        ];
        // dd($list);
        return view('menu_v1.reports._trip_detail', $data);
    }

    public function view_report_abnormalities(Request $req)
    {
        $q = "select id, nopol1 from t_vehicles WHERE dlt is null order by nopol1";
        $listNopol = DB::select($q);
        $listpool = DB::table('t_conf_pool')->where('dlt', null)->get();

        $data = [
            'listNopol' => $listNopol,
            'listpool' => $listpool
        ];

        $log = [
            "module" => "Abnormalities Report",
            "action" => "View",
            "desc" => "Open Abnormalities Report menu",
        ];
        UserLogs::insert(Auth::user()->id, $log);
        return view('menu_v1.reports.abnormalities', $data);
    }
    public function api_report_abnormalities_list(Request $req)
    {
        // Validate input
        $rules = [
            // 'from_date' => 'required|date',
            // 'to_date' => 'required|date|after_or_equal:from_date',
            'type' => 'nullable|in:report,list', // enum "report", "list". nullable default "list"
        ];

        $isValidInput = Validator::make($req->all(), $rules);
        if (!$isValidInput->passes()) {
            $apiResp = Responses::bad_input($isValidInput->messages()->first());
            return new Response($apiResp, $apiResp["meta"]["code"]);
        }

        $from_date = $req->input('from_date') - Helper::TIMEFIX;
        $to_date = $req->input('to_date') - Helper::TIMEFIX;
        $vid = $req->input('vid');
        $poolcode = $req->input('poolcode');

        // Create a period between the two timestamps
        $period = CarbonPeriod::create(
            Carbon::createFromTimestamp($from_date)->startOfMonth(),
            '1 month',
            Carbon::createFromTimestamp($to_date)
        );
        $subQueryParts = [];
        foreach ($period as $date) {
            $tableName = "tracks_" . $date->format('ym');

            // We filter inside each table so the DB uses indexes immediately
            $subQueryParts[] = "SELECT id, vhc_id, crt_d, speed
				FROM $tableName
				WHERE crt_d BETWEEN $from_date AND $to_date
				AND action = 'location' and speed>60
			";
        }
        $qString = "(" . implode(' UNION ALL ', $subQueryParts) . ")";
        // dd("qString", $qString);
        try {
            // $list = DB::select("SELECT
            // 		tv.name,
            // 		tv.nopol1,
            // 		t.crt_d,
            // 		t.speed,
            // 		tgta.fulladdress,
            // 		tvd.speed_limit,
            // 		tvd.pool_code,
            // 		tvd.dc_code
            // 	FROM
            // 		$qString t
            // 	INNER JOIN t_vehicles tv ON tv.id = t.vhc_id
            // 	INNER JOIN t_vehicles_detail tvd ON tvd.vid = tv.id
            // 	LEFT JOIN t_gps_tracks_address tgta ON tgta.master_id = t.id
            // 	WHERE
            // 		if(? , tv.id = ? , 1=1)
            // 		AND t.speed > tvd.speed_limit
            //         AND if(? , tvd.pool_code = ? , 1=1)
            // 	ORDER BY t.crt_d;
            // ", [$vid, $vid, $poolcode, $poolcode]);

            $vid = $vid ?: null;
            $poolcode = $poolcode ?: null;

            $sql = "SELECT
            tv.name,
            tv.nopol1,
            t.crt_d,
            t.speed,
            tgta.fulladdress,
            tvd.speed_limit,
            tvd.pool_code,
            tvd.dc_code
        FROM
            $qString t
        INNER JOIN t_vehicles tv ON tv.id = t.vhc_id
        INNER JOIN t_vehicles_detail tvd ON tvd.vid = tv.id
        LEFT JOIN t_gps_tracks_address tgta ON tgta.master_id = t.id
        WHERE
            (? IS NULL OR tv.id = ?)
            AND t.speed > tvd.speed_limit
            AND (? IS NULL OR tvd.pool_code = ?)
        ORDER BY t.crt_d
    ";

            $list = DB::select($sql, [
                $vid,
                $vid,
                $poolcode,
                $poolcode,
            ]);

            // // RETURN 1 - LIST
            // if($req->type != 'report'){
            $apiResp = Responses::success("success list abnormalities report");
            $apiResp["data"] = $list;
            return new Response($apiResp, $apiResp["meta"]["code"]);
            // }

        } catch (\Exception $e) {
            $apiResp = Responses::error($e->getMessage());
            return new Response($apiResp, $apiResp["meta"]["code"]);
            // return Responses::json(Responses::SERVER_ERROR, 'An error occurred while generating the report.', (object)[]);
        }
    }

    public function api_view_trip_detail(Request $req, $token)
    {
        // token = base64(unix(start_time) + '|' + unix(end_time) + '|' + nopol + '|' + unix(now()))
        // $token = "1759686805|1759693045|B.10-517|1765845676";
        $token = base64_decode($token);
        $token = explode('|', $token);
        $tgl0 = (int) $token[0] ?? null;
        $tgl1 = (int) $token[1] ?? null;
        $nopol1 = $token[2] ?? null;
        $now = (int) $token[3] ?? null;
        $isMoreThanOneHour = time() - $now > 60 * 60;

        if ($tgl0 == null || $tgl1 == null || $nopol1 == null || $now == null || $isMoreThanOneHour) {
            $apiResp = Responses::bad_request("Invalid token");
            return new Response($apiResp, $apiResp["meta"]["code"]);
        }

        // get vid by nopol1
        $vid = DB::select("SELECT id FROM t_vehicles WHERE nopol1 = ?", [$nopol1]);
        if (count($vid) == 0) {
            $apiResp = Responses::bad_request("Vehicle not found");
            return new Response($apiResp, $apiResp["meta"]["code"]);
        }
        $vid = $vid[0]->id;

        $d = [$vid, $tgl0, $tgl1];
        $list = DB::select("SELECT
				t.crt_d, t.latitude, t.longitude, t.speed,
				tgta.fulladdress,
				t.pre_milleage, t.vhc_milleage, fuel_count
			FROM
				t_gps_tracks t
				left join t_gps_tracks_address tgta on tgta.master_id = t.id
			WHERE
				t.vhc_id = ?
				and t.latitude IS NOT NULL
				AND t.longitude IS NOT NULL
				AND t.action = 'location'
				AND t.crt_d BETWEEN ? AND ?
			ORDER BY t.crt_d asc
		", $d);
        if (count($list) == 0) {
            $apiResp = Responses::not_found("Track not found");
            return new Response($apiResp, $apiResp["meta"]["code"]);
        }

        $start = [
            'time' => $list[0]->crt_d,
            'fulladdress' => urldecode($list[0]->fulladdress),
            'mileage' => $list[0]->vhc_milleage,
        ];
        $finish = [
            'time' => $list[count($list) - 1]->crt_d,
            'fulladdress' => urldecode($list[count($list) - 1]->fulladdress),
            'mileage' => $list[count($list) - 1]->vhc_milleage,
        ];

        $t0 = Carbon::createFromTimestamp($list[0]->crt_d);
        $t1 = Carbon::createFromTimestamp($list[count($list) - 1]->crt_d);
        $diff = $t1->diff($t0);
        $hours = $diff->h + ($diff->days * 24); // include days converted to hours
        $minutes = $diff->i;
        $duration = "{$hours} hour" . ($hours > 1 ? 's' : '') . " {$minutes} minute" . ($minutes > 1 ? 's' : '');

        $distance = $list[count($list) - 1]->vhc_milleage - $list[0]->vhc_milleage;
        $fuel_consumed = $list[count($list) - 1]->fuel_count - $list[0]->fuel_count;

        $data = [
            'nopol1' => $nopol1,
            'vid' => $vid,
            'tgl0' => $tgl0,
            'tgl1' => $tgl1,
            'list' => $list,
            'start' => $start,
            'finish' => $finish,
            'duration' => $duration,
            'distance' => $distance,
            'fuel_consumed' => $fuel_consumed,
        ];
        // dd($list);
        return view('menu_v1.reports.view_trip_detail', $data);
    }

    public function decryptText(string $ciphertext = "FqujI/06YPCpCSP0Xlt6bA==")
    {
        $secret = "Mg3Xt1169cRNJWX6HG12DVkgmAXINVuq";
        $iv_str = "Mg3Xt1169cRNJWX6";

        // Derive key and IV exactly as the web tool does
        $md5 = md5($secret, true);                  // 16-byte raw binary MD5
        $key = $md5 . $md5;                          // 32 bytes → AES-256 key
        $iv = substr($md5, 0, 16);                   // IV = first 16 bytes of MD5

        $data = base64_decode($ciphertext, true);
        if ($data === false) {
            return 'Invalid Base64';
        }

        $plaintext = openssl_decrypt(
            $data,
            'aes-256-cbc',
            $key,
            0,
            $iv
        );

        if ($plaintext === false) {
            return 'Decryption failed: ' . openssl_error_string();
        }

        return $plaintext; // → "1764833739"
    }
}
