<?php
namespace App\Http\Controllers\API;

use App\Helpers\ClickHouseHelper;
use App\Http\Controllers\Controller;
use Carbon\Carbon;
use Illuminate\Http\Request;

class ClickHouseController extends Controller
{
    private $clickHouse;

    public function __construct()
    {
        $this->clickHouse = new ClickHouseHelper();
    }

    public function getObjectNameDetected(Request $request)
    {
        $limit = max((int) $request->input('limit', 10), 1);
        $page = max((int) $request->input('page', 1), 1);
        $offset = ($page - 1) * $limit;
        $validation = $request->input('validation', 1);
        $search = trim($request->input('search', ''));
        $date = trim($request->input('date', ''));

        try {
            $invalidLabels = [
                'Person', 'person', 'Car', 'Motorcycle', 'Bus', 'Cat', 'Dog', 'Truk', 'Bicycle',
                'vehicle', 'person undetected', 'Person undetected', 'person unconfidance', 'face unconfidance',
            ];
            $invalidLabelsString = "'" . implode("', '", $invalidLabels) . "'";

            // Filter validasi
            $filterValidation = "";
            if ($validation == 1) {
                $filterValidation = "AND name NOT IN ($invalidLabelsString)";
            } elseif ($validation == 2) {
                $filterValidation = "AND name IN ($invalidLabelsString)";
            }

            // Pencarian
            $searchQuery = "";
            if (!empty($search)) {
                $safeSearch = addslashes($search);
                $searchQuery = "AND LOWER(name) LIKE LOWER('%$safeSearch%')";
            }

            // Filter tanggal
            $dateQuery = "";
            if (!empty($date) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
                $dateQuery = "AND toDate(created_at, 'Asia/Jakarta') = '$date'";
            }

            // Filter tambahan
            $securityFilter = "AND LOWER(name) NOT LIKE '%security%'";
            $superAdminFilter = "AND LOWER(name) NOT LIKE '%super admin%'";
            $developerFilter = "AND LOWER(name) NOT LIKE '%developer%'";

            // ✅ Query utama TANPA GROUP BY
            $query = "
                SELECT
                    name,
                    camera,
                    camera_label,
                    formatDateTime(created_at, '%Y-%m-%d %H:%i:%S', 'Asia/Jakarta') AS created_at,
                    confidence,
                    rel_width,
                    rel_height,
                    rel_x1,
                    rel_y1,
                    rel_x2,
                    rel_y2
                FROM object_detected
                WHERE 1=1
                $filterValidation
                $securityFilter
                $superAdminFilter
                $developerFilter
                $searchQuery
                $dateQuery
                ORDER BY created_at DESC
                LIMIT $limit OFFSET $offset
            ";

            $data = $this->clickHouse->query($query);

            $result = array_map(function ($row) {
                return [
                    'name' => $row['name'],
                    'camera' => $row['camera'],
                    'camera_label' => $row['camera_label'],
                    'created_at' => $row['created_at'],
                    'avg_confidence' => round($row['confidence'], 2),
                    'avg_width' => round($row['rel_width'], 4),
                    'avg_height' => round($row['rel_height'], 4),
                    'avg_x1' => round($row['rel_x1'], 4),
                    'avg_y1' => round($row['rel_y1'], 4),
                    'avg_x2' => round($row['rel_x2'], 4),
                    'avg_y2' => round($row['rel_y2'], 4),
                ];
            }, $data);

            $countQuery = "
                SELECT COUNT(*) AS total
                FROM object_detected
                WHERE 1=1
                $filterValidation
                $securityFilter
                $superAdminFilter
                $developerFilter
                $searchQuery
                $dateQuery
            ";
            $totalCount = $this->clickHouse->query($countQuery)[0]['total'] ?? 0;

            return response()->json([
                'data' => $result,
                'pagination' => [
                    'limit' => $limit,
                    'page' => $page,
                    'total' => $totalCount,
                    'total_pages' => ceil($totalCount / $limit),
                ],
            ]);
        } catch (\Exception $e) {
            return response()->json(['error' => $e->getMessage()], 500);
        }
    }

    public function getSecurityPatrolDetected(Request $request)
    {
        $limit = (int) $request->input('limit', 10);
        $page = (int) $request->input('page', 1);
        $offset = ($page - 1) * $limit;
        $validation = $request->input('validation', 1);
        $search = trim($request->input('search', ''));
        $date = trim($request->input('date', ''));

        try {
            $invalidLabels = [
                'Person', 'person', 'Car', 'Motorcycle', 'Bus', 'Cat', 'Dog', 'Truk', 'Bicycle',
                'vehicle', 'person undetected', 'Person undetected', 'person unconfidance', 'face unconfidance',
            ];
            $invalidLabelsString = "'" . implode("', '", $invalidLabels) . "'";

            // Filter validasi
            $filterValidation = "";
            if (1 == $validation) {
                $filterValidation = "AND name NOT IN ($invalidLabelsString)";
            } elseif (2 == $validation) {
                $filterValidation = "AND name IN ($invalidLabelsString)";
            }

            $searchQuery = "";
            if (!empty($search)) {
                $safeSearch = addslashes($search);
                $searchQuery = "AND LOWER(name) LIKE LOWER('%$safeSearch%')";
            }

            // Filter tanggal
            $dateQuery = "";
            if (!empty($date) && preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
                $dateQuery = "AND toDate(created_at, 'Asia/Jakarta') = '$date'";
            }

            $securityFilter = "AND LOWER(name) LIKE '%security%'";

            // Query utama dengan GROUP BY
            $query = "
                SELECT
                    name,
                    camera,
                    camera_label,
                    formatDateTime(created_at, '%Y-%m-%d %H:%i:%S', 'Asia/Jakarta') AS created_at,
                    confidence,
                    rel_width,
                    rel_height,
                    rel_x1,
                    rel_y1,
                    rel_x2,
                    rel_y2
                FROM object_detected
                WHERE 1=1
                $filterValidation
                $securityFilter
                $searchQuery
                $dateQuery
                ORDER BY created_at DESC
                LIMIT $limit OFFSET $offset
            ";

            // Eksekusi query
            $data = $this->clickHouse->query($query);

            // Map hasil query
            $result = array_map(function ($row) {
                return [
                    'name' => $row['name'],
                    'camera' => $row['camera'],
                    'camera_label' => $row['camera_label'],
                    'created_at' => $row['created_at'],
                    'avg_confidence' => round($row['confidence'], 2),
                    'avg_width' => round($row['rel_width'], 4),
                    'avg_height' => round($row['rel_height'], 4),
                    'avg_x1' => round($row['rel_x1'], 4),
                    'avg_y1' => round($row['rel_y1'], 4),
                    'avg_x2' => round($row['rel_x2'], 4),
                    'avg_y2' => round($row['rel_y2'], 4),
                ];
            }, $data);

            // Query untuk total data sesuai filter
            $countQuery = "
                SELECT COUNT(*) AS total
                FROM object_detected
                WHERE 1=1
                $filterValidation
                $securityFilter
                $searchQuery
                $dateQuery
            ";
            $totalCount = $this->clickHouse->query($countQuery)[0]['total'] ?? 0;

            // Return response JSON
            return response()->json([
                'data' => $result,
                'pagination' => [
                    'limit' => $limit,
                    'page' => $page,
                    'total' => $totalCount,
                    'total_pages' => ceil($totalCount / $limit),
                ],
            ]);
        } catch (\Exception $e) {
            return response()->json(['error' => $e->getMessage()], 500);
        }
    }

    public function getPlateNumberDetected(Request $request)
    {
        $limit = (int) $request->input('limit', 10);
        $page = (int) $request->input('page', 1);
        $offset = ($page - 1) * $limit;
        $date = $request->input('date');
        $plat = $request->input('plat');
        $camera = $request->input('camera');
        $serverValidation = $request->input('server_validation');

        if ($date && !preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
            return response()->json(['error' => 'Invalid date format. Use YYYY-MM-DD.'], 400);
        }

        try {
            // Build filter query
            $filters = "WHERE 1=1";
            if (!empty($date)) {
                $filters .= " AND toDate(created_at, 'Asia/Jakarta') = '$date'";
            }
            if (!empty($plat)) {
                $filters .= " AND plat LIKE '%" . addslashes($plat) . "%'";
            }
            if (!empty($camera)) {
                $filters .= " AND camera = '" . addslashes($camera) . "'";
            }
            if ($serverValidation !== null && $serverValidation !== '') {
                $filters .= " AND server_validation = " . (int) $serverValidation;
            }

            // Query utama tanpa GROUP BY, semua record langsung
            $query = "
                SELECT
                    plat,
                    confidences,
                    server_validation,
                    server_message,
                    camera,
                    formatDateTime(created_at, '%Y-%m-%d %H:%i:%S', 'Asia/Jakarta') AS detected_at
                FROM
                    plate_number_detected_log
                $filters
                ORDER BY detected_at DESC
                LIMIT $limit OFFSET $offset
            ";

            // Eksekusi query
            $data = $this->clickHouse->query($query);

            // Format hasil menjadi JSON
            $result = array_map(function ($row) {
                return [
                    'plat' => $row['plat'] ?? 'N/A',
                    'confidences' => is_array($row['confidences']) ? round(max($row['confidences']) * 100) . '%' : (!empty($row['confidences']) ? round(floatval($row['confidences']) * 100) . '%' : 'N/A'),
                    'server_validation' => isset($row['server_validation']) && $row['server_validation'] == '1' ? 'VALID' : 'INVALID',
                    'server_message' => $row['server_message'] ?? 'N/A',
                    'camera' => $row['camera'] ?? 'N/A',
                    'detected_at' => $row['detected_at'] ?? 'N/A',
                ];
            }, $data);

            // Hitung total data tanpa pagination
            $countQuery = "
                SELECT COUNT(*) AS total
                FROM plate_number_detected_log
                $filters
            ";

            $totalCount = $this->clickHouse->query($countQuery)[0]['total'] ?? 0;

            // Return response dengan data dan informasi pagination
            return response()->json([
                'data' => $result,
                'pagination' => [
                    'limit' => $limit,
                    'page' => $page,
                    'total' => $totalCount,
                    'total_pages' => ceil($totalCount / $limit),
                ],
            ]);
        } catch (\Exception $e) {
            return response()->json(['error' => $e->getMessage()], 500);
        }
    }

    // SUMMARY
    public function getObjectDetectionSummary(Request $request)
    {
        $date = $request->input('date'); // Filter by date if provided
        // $date = "2025-09-19";
        try {
            // Daftar label yang tidak valid
            $invalidLabels = [
                'Person', 'person', 'Car', 'Motorcycle', 'Bus', 'Cat', 'Dog', 'Truk', 'Bicycle',
                'vehicle', 'person undetected', 'Person undetected', 'person unconfidance', 'face unconfidance',
            ];
            $invalidLabelsString = "'" . implode("', '", $invalidLabels) . "'"; // Format untuk SQL IN clause

            // Filter berdasarkan tanggal jika diberikan
            $filters = "WHERE 1 = 1 ";
            if (!empty($date)) {
                $filters .= " AND toDate(created_at, 'Asia/Jakarta') = '$date' ";
            }

            $securityFilter = "AND LOWER(name) NOT LIKE '%security%'";
            $superAdminFilter = "AND LOWER(name) NOT LIKE '%super admin%'";
            $developerFilter = "AND LOWER(name) NOT LIKE '%developer%'";

            // Tambahkan filter untuk name yang tidak termasuk dalam daftar INVALID_LABEL
            $queryPersonIn = "
                SELECT COUNT(DISTINCT name) AS personIn
                FROM object_detected
                $filters
                AND camera = 1
                AND name NOT IN ($invalidLabelsString)
                $securityFilter
                $superAdminFilter
                $developerFilter
            ";

            $queryPersonOut = "
                SELECT COUNT(DISTINCT name) AS personOut
                FROM object_detected
                $filters
                AND camera = 2
                AND name NOT IN ($invalidLabelsString)
                $securityFilter
                $superAdminFilter
                $developerFilter
            ";

            $querytotalFalseDetected = "
                SELECT COUNT(name) AS totalFalseDetected
                FROM object_detected
                $filters
                AND name IN ($invalidLabelsString)
                $securityFilter
                $superAdminFilter
                $developerFilter
            ";

            // Eksekusi query untuk mendapatkan hasil
            $personIn = $this->clickHouse->query($queryPersonIn)[0]['personIn'] ?? 0;
            $personOut = $this->clickHouse->query($queryPersonOut)[0]['personOut'] ?? 0;
            $totalFalseDetected = $this->clickHouse->query($querytotalFalseDetected)[0]['totalFalseDetected'] ?? 0;

            // Hitung total detect (personIn + personOut)
            $totalDetect = $personIn + $personOut;

            // Kembalikan hasil dalam response JSON
            return response()->json([
                'data' => [
                    'personIn' => (int) $personIn,
                    'personOut' => (int) $personOut,
                    'totalDetect' => (int) $totalDetect,
                    'totalFalseDetected' => (int) $totalFalseDetected,
                ],
            ]);
        } catch (\Exception $e) {
            return response()->json(['error' => $e->getMessage()], 500);
        }
    }

    public function getSecurityPatrolDetectionSummary(Request $request)
    {
        $date = $request->input('date'); // Filter by date if provided

        try {
            // Daftar label yang tidak valid
            $invalidLabels = [
                'Person', 'person', 'Car', 'Motorcycle', 'Bus', 'Cat', 'Dog', 'Truk', 'Bicycle',
                'vehicle', 'person undetected', 'Person undetected', 'person unconfidance', 'face unconfidance',
            ];
            $invalidLabelsString = "'" . implode("', '", $invalidLabels) . "'"; // Format untuk SQL IN clause

            // Filter berdasarkan tanggal jika diberikan
            $filters = "WHERE 1 = 1 ";
            if (!empty($date)) {
                $filters .= "AND toDate(created_at) = '$date' ";
            }
            $securityFilter = "AND LOWER(name) LIKE '%security%'";

            // Tambahkan filter untuk name yang tidak termasuk dalam daftar INVALID_LABEL
            $queryPersonIn = "
                SELECT COUNT(DISTINCT name) AS personIn
                FROM object_detected
                $filters
                AND camera = 1
                AND name NOT IN ($invalidLabelsString)
                $securityFilter
            ";

            $queryPersonOut = "
                SELECT COUNT(DISTINCT name) AS personOut
                FROM object_detected
                $filters
                AND camera = 2
                AND name NOT IN ($invalidLabelsString)
                $securityFilter
            ";

            $querytotalFalseDetected = "
                SELECT COUNT(name) AS totalFalseDetected
                FROM object_detected
                $filters
                AND name IN ($invalidLabelsString)
                $securityFilter
            ";

            // Eksekusi query untuk mendapatkan hasil
            $personIn = $this->clickHouse->query($queryPersonIn)[0]['personIn'] ?? 0;
            $personOut = $this->clickHouse->query($queryPersonOut)[0]['personOut'] ?? 0;

            $totalDetect = $personIn + $personOut;

            // Kembalikan hasil dalam response JSON
            return response()->json([
                'data' => [
                    'in_patrol' => (int) $personIn,
                    'out_patrol' => (int) $personOut,
                    'total_patrol' => (int) $totalDetect,
                ],
            ]);
        } catch (\Exception $e) {
            return response()->json(['error' => $e->getMessage()], 500);
        }
    }

    public function getPlateNumberSummary(Request $request)
    {
        $date = $request->input('date'); // Filter by date if provided
        $camera = $request->input('camera'); // Filter kamera
        $plat = $request->input('plat'); // Filter plat
        $serverValidation = '1'; // Filter server validation

        try {
            // Buat filter dinamis berdasarkan parameter
            $filters = "WHERE 1 = 1 ";
            if (!empty($date)) {
                $filters .= "AND toDate(created_at) = '$date' ";
            }
            if (!empty($camera)) {
                $filters .= "AND camera = '" . addslashes($camera) . "' ";
            }
            if (!empty($plat)) {
                $filters .= "AND plat LIKE '%" . addslashes($plat) . "%' ";
            }
            if ($serverValidation !== null && $serverValidation !== '') {
                $filters .= " AND server_validation = " . (int) $serverValidation;
            }

            // Query untuk menghitung jumlah kendaraan unik berdasarkan plat
            $queryVehicleDetected = "
                SELECT COUNT(DISTINCT plat) AS vehicleDetected
                FROM plate_number_detected_log
                $filters
                ";

            $queryVehicleIn = "
                SELECT COUNT(DISTINCT plat) AS vehicleIn
                FROM plate_number_detected_log
                $filters AND camera = 'pos-1'
                ";

            $queryVehicleOut = "
                SELECT COUNT(DISTINCT plat) AS vehicleOut
                FROM plate_number_detected_log
                $filters AND camera = 'pos-2'
                ";

            // Eksekusi query
            $vehicleDetected = $this->clickHouse->query($queryVehicleDetected)[0]['vehicleDetected'] ?? 0;
            $vehicleIn = $this->clickHouse->query($queryVehicleIn)[0]['vehicleIn'] ?? 0;
            $vehicleOut = $this->clickHouse->query($queryVehicleOut)[0]['vehicleOut'] ?? 0;

            return response()->json([
                'data' => [
                    'vehicleDetected' => (int) $vehicleDetected,
                    'vehicleIn' => (int) $vehicleIn,
                    'vehicleOut' => (int) $vehicleOut,
                ],
            ]);
        } catch (\Exception $e) {
            return response()->json(['error' => $e->getMessage()], 500);
        }
    }

    public function getAllDetectionSummary(Request $request)
    {
        $date = $request->input('date'); // Filter by date if provided

        try {
            // Daftar label yang tidak valid
            $invalidLabels = [
                'Person', 'person', 'Car', 'Motorcycle', 'Bus', 'Cat', 'Dog', 'Truk', 'Bicycle',
                'vehicle', 'person undetected', 'Person undetected', 'person unconfidance', 'face unconfidance',
            ];
            $invalidLabelsString = "'" . implode("', '", $invalidLabels) . "'"; // Format untuk SQL IN clause

            // Filter berdasarkan tanggal jika diberikan
            $filters = "WHERE 1 = 1 ";
            if (!empty($date)) {
                // Convert the date to Asia/Jakarta timezone
                $dateTime = Carbon::createFromFormat('Y-m-d', $date, 'UTC')
                    ->setTimezone('Asia/Jakarta')
                    ->format('Y-m-d');
                $filters .= "AND toDate(created_at) = '$dateTime' ";
            }

            $superAdminFilter = "AND LOWER(name) NOT LIKE '%super admin%'";
            $developerFilter = "AND LOWER(name) NOT LIKE '%developer%'";

            // Tambahkan filter untuk name yang tidak termasuk dalam daftar INVALID_LABEL
            $queryPersonIn = "SELECT COUNT(DISTINCT name) AS personIn
                FROM object_detected
                $filters
                AND camera = 1
                AND name NOT IN ($invalidLabelsString)
                $superAdminFilter
                $developerFilter
            ";

            $queryPersonOut = "SELECT COUNT(DISTINCT name) AS personOut
                FROM object_detected
                $filters
                AND camera = 2
                AND name NOT IN ($invalidLabelsString)
                $superAdminFilter
                $developerFilter
            ";

            $queryVehicleIn = "SELECT COUNT(DISTINCT plat) AS vehicleIn
                FROM plate_number_detected_log
                $filters
                AND server_validation = 1
                AND camera = 'pos-1'
            ";

            $queryVehicleOut = "SELECT COUNT(DISTINCT plat) AS vehicleOut
                FROM plate_number_detected_log
                $filters
                AND server_validation = 1
                AND camera = 'pos-2'
            ";

            // Eksekusi query untuk mendapatkan hasil
            $personIn = $this->clickHouse->query($queryPersonIn)[0]['personIn'] ?? 0;
            $personOut = $this->clickHouse->query($queryPersonOut)[0]['personOut'] ?? 0;
            $vehicleIn = $this->clickHouse->query($queryVehicleIn)[0]['vehicleIn'] ?? 0;
            $vehicleOut = $this->clickHouse->query($queryVehicleOut)[0]['vehicleOut'] ?? 0;

            // Kembalikan hasil dalam response JSON
            return response()->json([
                'data' => [
                    'personIn' => (int) $personIn,
                    'personOut' => (int) $personOut,
                    'vehicleIn' => (int) $vehicleIn,
                    'vehicleOut' => (int) $vehicleOut,
                ],
            ]);
        } catch (\Exception $e) {
            // Tangani error
            return response()->json(['error' => $e->getMessage()], 500);
        }
    }

}