<?php

include 'db_connect.php';
if (!class_exists('Enum')) {
    include(__DIR__ . '/../config/enum.php');
}

/**
 * Converts date strings like:
 *  - "04-Jan-1995"
 *  - "04-Jan-1995 10:10 PM"
 *  - "04-Jan-1995 22:10"
 * 
 * @param string $input      The input date/time string
 * @param bool   $onlyDate   If true, return only the DATE (Y-m-d)
 * @return string|false       MySQL date/datetime format or false if invalid
 */
function sDateToSql(string $input, bool $onlyDate = true)
{
    $formats = [
        'd-M-Y h:i A',
        'd-M-Y H:i',
        'd-M-Y',
    ];

    foreach ($formats as $format) {
        $dt = DateTime::createFromFormat($format, $input);
        if ($dt !== false) {
            return $onlyDate
                ? $dt->format('Y-m-d')
                : $dt->format('Y-m-d H:i:s');
        }
    }

    return false;
}

class Model extends DbModel
{
    private function GenerateInvoice($tableName, $columnName)
    {
        $invFrm = date('y') . date('m');

        $sql = "SELECT MAX($columnName) AS $columnName FROM $tableName WHERE $columnName LIKE '{$invFrm}%'";

        $result = $this->FetchOneAssoc($sql);

        if ($result) {
            $lastInv = $result[$columnName];

            if (!empty($lastInv)) {
                $lastNum = (int) substr($lastInv, strlen($invFrm));
                $nextNum = $lastNum + 1;
                $seq = str_pad($nextNum, 2, '0', STR_PAD_LEFT);
                return $invFrm . $seq;
            }
        }

        return $invFrm . '01';
    }

    private function InsertLedger($data)
    {
        $emp_id = $data['emp_id'];
        $event_id = $data['event_id'];
        $pay_id = $data['pay_id'];
        $date = $data['date'];
        $remarks = $data['remarks'];
        $payable_amt = $data['payable_amt'];
        $payment = $data['payment'];
        $type = $data['type'];

        $sqlS = "SELECT IFNULL(balance,0) AS balance FROM employee_ledger WHERE emp_id = $emp_id AND create_at IN (SELECT MAX(create_at) FROM employee_ledger WHERE emp_id=$emp_id)";
        $result = $this->conn->query($sqlS);
        $balance = 0;
        if ($result->num_rows == 1) {
            $row = $result->fetch_assoc();
            $amt = $row['balance'];
            $balance = $amt;
        }

        $balance += $payable_amt;
        $balance -= $payment;

        $sql = "INSERT INTO `employee_ledger` (`emp_id`,`event_id`, `pay_id`, `date`, `remarks`, `payable_amt`, `payment`, `balance`, `type`, `create_at`) ";
        $sql .= "VALUES ('$emp_id','$event_id','$pay_id','$date','$remarks', '$payable_amt', '$payment', '$balance','$type', current_timestamp())";

        return $this->ExecuteStatement($sql);
    }

    public function GetEmpLedgerByEmpId($data, $dateRange = false, $forPrint = false)
    {
        $emp_id = $data['emp_id'];
        $fromDate = $data['fromDate'];
        $toDate = $data['toDate'];

        $sql = "SELECT * FROM employee_ledger WHERE emp_id = $emp_id";
        if ($dateRange) {
            $sql .= " AND DATE(create_at) BETWEEN '$fromDate' AND '$toDate'";
        }

        $sql .= ($forPrint) ? " ORDER BY date" : " ORDER BY create_at";

        return $this->FetchAllAssoc($sql);
    }

    public function UpdateEmpLedger($data, $dateRange = false)
    {

        $emp_id = $data['emp_id'];
        $fromDate = $data['fromDate'];
        $toDate = $data['toDate'];

        $InvoicePrefix = InvoicePrefix::EMP_PAY;

        $sqlDel = "DELETE FROM employee_ledger WHERE emp_id = $emp_id ";
        if ($dateRange) {
            $sqlDel .= " AND DATE(create_at) BETWEEN '$fromDate' AND '$toDate'";
        }
        $delete = $this->ExecuteStatement($sqlDel);

        $sql = "SELECT s.* FROM(
        (SELECT ae.emp_id AS emp_id, ae.event_id AS event_id, 0 AS pay_id, DATE(vwe.event_date_time) AS date, 
        CAST(CONCAT(vwe.id, '-', vwe.event_name, ' (', vwe.package_name, ')') AS VARCHAR(255)) AS remarks, 
        ae.payable_amt AS payable_amt, 0 AS payment, 'E' AS type, ae.create_at AS create_at 
        FROM assign_employee AS ae 
        LEFT JOIN vw_event AS vwe ON ae.event_id=vwe.id
        WHERE ae.emp_id=$emp_id)
        UNION ALL
        (SELECT ep.emp_id AS emp_id, 0 AS event_id, ep.id AS pay_id, DATE(ep.pay_date) AS date, 
        CAST(CONCAT('Payment ($InvoicePrefix', ep.invoice, CASE WHEN ep.remarks='' THEN '' ELSE ' / ' END, ep.remarks, ')') AS VARCHAR(255)) AS remarks, 
        0 AS payable_amt, ep.amt AS payment, 'P' AS type, ep.create_at AS create_at 
        FROM employee_payment AS ep WHERE ep.emp_id=$emp_id)) AS s
        WHERE s.emp_id=$emp_id";

        if ($dateRange) {
            $sql .= " AND s.create_at BETWEEN '$fromDate' AND '$toDate'";
        }
        $sql .= " ORDER BY s.create_at ASC";


        $result = $this->conn->query($sql);
        if ($result->num_rows > 0) {

            $insertSqlValues = "";
            $balance = 0;

            while ($row = $result->fetch_assoc()) {
                $event_id = $row['event_id'];
                $pay_id = $row['pay_id'];
                $date = $row['date'];
                $remarks = $row['remarks'];
                $payable_amt = $row['payable_amt'];
                $payment = $row['payment'];
                $type = $row['type'];
                $create_at = $row['create_at'];

                $balance += $payable_amt;
                $balance -= $payment;

                if ($insertSqlValues == "") {
                    $insertSqlValues .= "('$emp_id','$event_id','$pay_id','$date','$remarks', '$payable_amt', '$payment', '$balance','$type', '$create_at')";
                } else {
                    $insertSqlValues .= ", ('$emp_id','$event_id','$pay_id','$date','$remarks', '$payable_amt', '$payment', '$balance','$type', '$create_at')";
                }
            }
            $insertSqlValues .= ";";
            $insertSql = "INSERT INTO `employee_ledger` (`emp_id`,`event_id`, `pay_id`, `date`, `remarks`, `payable_amt`, `payment`, `balance`, `type`, `create_at`) VALUES " . $insertSqlValues;

            return $this->ExecuteStatement($insertSql);
        }

        return $delete;
    }

    public function AddUser($user)
    {
        $sql = '';
        $userName = $user['userName'];
        $fullName = strtoupper($user['fullName']);
        $pass = password_hash($user['pass'], PASSWORD_DEFAULT);
        $role_id = $user['role'];
        $status = $user['status'];

        $sql = "INSERT INTO `users` (`id`,`username`, `password`, `full_name`, `role_id`, `status`) ";
        $sql .= "VALUES (NULL,'$userName','$pass','$fullName','$role_id','$status')";

        return $this->ExecuteStatement($sql);
    }

    public function UpdateUser($user)
    {
        $sql = '';
        $id = $user['id'];
        $userName = $user['userName'];
        $fullName = strtoupper($user['fullName']);
        $pass = password_hash($user['pass'], PASSWORD_DEFAULT);
        $role_id = $user['role'];
        $status = $user['status'];

        if ($id > 0 || $id != '') {
            $sql = "UPDATE `users` SET `full_name`='$fullName', `role_id`='$role_id', `status`='$status'";
            if ($user['pass'] != '') {
                $sql .= " , `password`='$pass' ";
            }
            $sql .= " WHERE `id` = $id AND `username`='$userName'";
        }

        return $this->ExecuteStatement($sql);
    }

    public function UpdateUserPassword($user)
    {
        $sql = '';
        $id = $user['id'];
        $pass = password_hash($user['newPassword'], PASSWORD_DEFAULT);

        if ($id > 0 && $id != '' && $id != null) {
            $sql = "UPDATE `users` SET `password`='$pass' WHERE `id` = $id";
        }

        return $this->ExecuteStatement($sql);
    }

    public function UpdateUserProfile($user)
    {
        $sql = '';
        $id = $user['id'];
        $fullName = strtoupper($user['fullName']);


        if ($id > 0 && $id != '' && $id != null) {
            $sql = "UPDATE `users` SET `full_name`='$fullName' WHERE `id` = $id";
        }

        return $this->ExecuteStatement($sql);
    }

    /**
     * Summary of GetUser
     * @param mixed $data : "" = Return All
     * @param mixed $where : "un" = Username, "id" = id
     * @return array|bool|null|string
     */
    public function GetUser($data = "", $where = "")
    {
        $sql = "SELECT `u`.*, `r`.`name` AS `role_name`, 
        CASE 
            WHEN `u`.`status` = 0 THEN 'DE-ACTIVE' 
            WHEN `u`.`status` = 1 THEN 'ACTIVE' 
        END AS `user_status` 
        FROM `users` AS `u` 
        LEFT JOIN `user_role` AS `r` ON `u`.`role_id` = `r`.`id`";

        $cond = "";

        switch ($where) {
            case 'un':
                $cond = " WHERE `u`.`username` = '$data'";
                break;
            case 'id':
                $cond = " WHERE `u`.`id` = $data";
                break;
        }

        $sql .= $cond;

        if ($where == "un" || $where == "id") {
            return $this->FetchOneAssoc($sql);
        }

        return $this->FetchAllAssoc($sql);
    }

    public function AddEmp($data)
    {
        $sql = '';
        $full_name = $data['fullName'];
        $email = strtolower($data['email']);
        $phone_number = $data['phoneNumber'];
        $address = $data['address'];
        $salary = $data['salary'];
        $desig_code = $data['desig'];
        $status = $data['status'];

        $sql = "INSERT INTO `employees` (`id`,`full_name`, `email`, `phone_number`, `address`, `salary`, `desig_code`, `status`) ";
        $sql .= "VALUES (NULL,'$full_name','$email','$phone_number','$address','$salary', '$desig_code','$status')";

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    public function UpdateEmp($data)
    {
        $sql = '';
        $id = $data['id'];
        $full_name = $data['fullName'];
        $email = strtolower($data['email']);
        $phone_number = $data['phoneNumber'];
        $address = $data['address'];
        $salary = $data['salary'];
        $desig_code = $data['desig'];
        $status = $data['status'];

        if ($id > 0 || $id != '') {
            $sql = "UPDATE `employees` SET `full_name`='$full_name', `email`='$email', `phone_number`='$phone_number', `address`='$address', `salary`='$salary', `desig_code`='$desig_code', `status`='$status' WHERE `id` = $id";
        }

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    public function EmployeeList($onlyActive = false)
    {
        $sql = "SELECT * FROM vw_employee";
        if ($onlyActive) {
            $sql .= " WHERE status=1";
        }
        $sql .= " ORDER BY status DESC";

        return $this->FetchAllAssoc($sql);
    }

    public function GetEmpById($id)
    {
        $sql = "SELECT * FROM vw_employee WHERE id= $id";

        return $this->FetchOneAssoc($sql);
    }

    public function GetAssignedEmpByEventId($id)
    {
        $sql = "SELECT ae.*, vwe.full_name, vwe.phone_number, vwe.designation FROM assign_employee AS ae LEFT JOIN vw_employee AS vwe ON ae.emp_id=vwe.id WHERE ae.event_id = $id";

        return $this->FetchAllAssoc($sql);
    }

    public function GetAssignedEventByEmpId($emp_id, $firstDate, $lastDate)
    {
        $InvoicePrefix = InvoicePrefix::EMP_PAY;

        $sql = "SELECT s.* FROM(
            (SELECT ae.emp_id, ae.shift_amt, ae.shift_count, ae.total_shift_amt, ae.hour_amt, ae.hour_count, ae.total_hour_amt, ae.edit_fee, ae.travel_cost, ae.event_id AS event_id, 0 AS pay_id, DATE(vwe.event_date_time) AS date, 
            CAST(CONCAT(vwe.id, '-', vwe.event_name, ' (', vwe.package_name, ')') AS VARCHAR(255)) AS remarks, 
            ae.payable_amt AS payable_amt, 0 AS payment, 'E' AS type, ae.create_at AS create_at 
            FROM assign_employee AS ae 
            LEFT JOIN vw_event AS vwe ON ae.event_id=vwe.id
            WHERE ae.emp_id=$emp_id)
            UNION ALL
            (SELECT ep.emp_id, 0 AS shift_amt, 0 AS shift_count, 0 AS total_shift_amt, 0 AS hour_amt, 0 AS hour_count, 0 AS total_hour_amt, 0 AS edit_fee, 0 AS travel_cost, 0 AS event_id, ep.id AS pay_id, DATE(ep.pay_date) AS date, 
            CAST(CONCAT('Payment ($InvoicePrefix', ep.invoice, CASE WHEN ep.remarks='' THEN '' ELSE ' / ' END, ep.remarks, ')') AS VARCHAR(255)) AS remarks, 
            0 AS payable_amt, ep.amt AS payment, 'P' AS type, ep.create_at AS create_at 
            FROM employee_payment AS ep WHERE ep.emp_id=$emp_id)) AS s
            WHERE s.emp_id=$emp_id AND s.date BETWEEN '$firstDate' AND '$lastDate'
            ORDER BY s.date";

        return $this->FetchAllAssoc($sql);
    }

    public function GetAssignedEmpEvent()
    {
        $sql = "SELECT ae.event_id, vwe.event_date_time, vwe.event_name, vwe.package_name, vwe.venue, COUNT(*) AS assi_emp_count
            FROM assign_employee AS ae
            LEFT JOIN vw_event AS vwe ON ae.event_id=vwe.id
            GROUP BY ae.event_id, vwe.event_date_time, vwe.event_name, vwe.package_name, vwe.venue
            ORDER BY vwe.event_date_time DESC";

        return $this->FetchAllAssoc($sql);
    }

    public function DeleteAssignedEmployee($id, $event_id)
    {
        $sql1 = "SELECT emp_id, -(payable_amt) as payable_amt FROM assign_employee WHERE id=$id";

        $row = $this->FetchOneAssoc($sql1);

        $event = $this->GetEventById($event_id);

        $ledgerData = [
            'emp_id' => $row['emp_id'],
            'event_id' => $event_id,
            'pay_id' => '',
            'date' => $event['event_date_time'],
            'remarks' => 'Remove - ' . $event['event_type'] . ' (' . $event['package_type'] . ')',
            'payable_amt' => $row['payable_amt'],
            'payment' => 0,
            'type' => 'E'
        ];

        $sql = "DELETE FROM `assign_employee` WHERE `id`=$id";
        $delete = $this->ExecuteStatement($sql);

        $this->InsertLedger($ledgerData);
        return $delete;
    }

    public function EmpDesigList()
    {
        $sql = "SELECT * FROM `emp_designation`";

        return $this->FetchAllAssoc($sql);
    }

    public function AssignEmployee($data)
    {
        $event_id = $data['eventId'];
        $emp_id = $data['empId'];
        $shift_amt = $data['shiftPayable'];
        $shift_count = $data['shiftCount'];
        $total_shift_amt = $data['totalShiftPayable'];
        $hour_amt = $data['hourPayable'];
        $hour_count = $data['totalHour'];
        $total_hour_amt = $data['extraHourPayable'];
        $edit_fee = $data['editingFee'];
        $travel_cost = $data['travelCost'];
        $payable_amt = $data['totalPayable'];
        $user_name = User::UserName();

        $hour_amt = $total_hour_amt > 0 ? $hour_amt : 0;

        $sql = '';


        $sql = "INSERT INTO assign_employee (id, event_id, emp_id, shift_amt, shift_count, total_shift_amt,
        hour_amt, hour_count, total_hour_amt, edit_fee, travel_cost, payable_amt, create_by, create_at) 
        VALUES (NULL,'$event_id','$emp_id', '$shift_amt', '$shift_count', '$total_shift_amt', '$hour_amt', 
        '$hour_count', '$total_hour_amt', '$edit_fee', '$travel_cost', '$payable_amt', '$user_name', current_timestamp())";

        $insert = $this->ExecuteStatement($sql);

        $event = $this->GetEventById($event_id);

        $ledgerData = [
            'emp_id' => $emp_id,
            'event_id' => $event_id,
            'pay_id' => '',
            'date' => $event['event_date_time'],
            'remarks' => $event['id'] . '-' . $event['event_name'] . ' (' . $event['package_name'] . ')',
            'payable_amt' => $payable_amt,
            'payment' => 0,
            'type' => 'E',
        ];

        $this->InsertLedger($ledgerData);
        return $insert;
    }

    public function EmployeePayment($data)
    {
        $emp_id = $data['empId'];
        $payDate = sDateToSql($data['payDate']);
        $remarks = $data['remarks'];
        $payment = $data['payment'];
        $invNumber = $this->GenerateInvoice("employee_payment", "invoice");

        $user_name = User::UserName();

        $sql = "INSERT INTO `employee_payment` (`id`, `invoice`, `pay_date`, `emp_id`, `amt`, `remarks`, `create_by`, `create_at`)";
        $sql .= " VALUES (NULL, '$invNumber', '$payDate', '$emp_id', $payment, '$remarks', ' $user_name', current_timestamp())";

        $insertId = $this->ExecuteStatement($sql, true);

        if ($insertId > 0) {
            $rem = ($remarks == "") ? "" : " / " . $remarks;

            $ledgerData = [
                'emp_id' => $emp_id,
                'event_id' => '',
                'pay_id' => $insertId,
                'date' => $payDate,
                'remarks' => 'Payment (' . InvoicePrefix::EMP_PAY . $invNumber . $rem . ')',
                'payable_amt' => 0,
                'payment' => $payment,
                'type' => 'P',
            ];

            $this->InsertLedger($ledgerData);
            return $insertId;
        }
        return false;
    }

    public function EmployeePaymentDelete($id)
    {
        $sql = "DELETE FROM `employee_payment` WHERE `id`=$id;";


        $emp_pay = $this->GetEmpPayById($id);

        $rem = ($emp_pay['remarks'] == "") ? "" : " / " . $emp_pay['remarks'];

        $ledgerData = [
            'emp_id' => $emp_pay['emp_id'],
            'event_id' => '',
            'pay_id' => $emp_pay['id'],
            'date' => (new DateTime())->format('Y-m-d H:i:s'),
            'remarks' => 'Delete-Payment (' . InvoicePrefix::EMP_PAY . $emp_pay['invoice'] . $rem . ')',
            'payable_amt' => 0,
            'payment' => -$emp_pay['amt'],
            'type' => 'P',
        ];

        $delete = $this->ExecuteStatement($sql);
        $this->InsertLedger($ledgerData);
        return $delete;
    }

    public function GetLastEmpPayByEmpId($id)
    {
        $sql = "SELECT e.*, v.full_name, v.designation, v.phone_number, v.email, v.due_amt
                FROM employee_payment AS e
                LEFT JOIN vw_employee AS v ON e.emp_id=v.id
                WHERE e.id IN(SELECT MAX(id) FROM employee_payment WHERE emp_id = $id)";

        return $this->FetchOneAssoc($sql);
    }

    public function GetEmpPayById($id)
    {
        $sql = "SELECT e.*, v.full_name, v.designation, v.phone_number, v.email, v.due_amt
                FROM employee_payment AS e
                LEFT JOIN vw_employee AS v ON e.emp_id=v.id
                WHERE e.id = $id";

        return $this->FetchOneAssoc($sql);
    }

    public function GetEmpPayByEmpId($id)
    {
        $sql = "SELECT * FROM `employee_payment` WHERE emp_id=$id ORDER BY id DESC";

        return $this->FetchAllAssoc($sql);
    }

    public function EventList($eventStatus = null, bool $statusReverse = false, bool $payCompleteList = false, bool $dueList = false)
    {
        $sql = "SELECT * FROM vw_event";
        $where = '';

        if ($payCompleteList) {
            $where = "due_amount <= 0";
        } elseif ($dueList) {
            $where = "due_amount > 0";
        }

        if ($eventStatus !== null && EventStatus::IsValid($eventStatus)) {
            $operator = $statusReverse ? '!=' : '=';
            $where .= ($where ? " AND " : "") . "status $operator '$eventStatus'";
        }

        if (!empty($where)) {
            $sql .= " WHERE " . $where;
        }

        $sql .= " ORDER BY event_date_time DESC";

        return $this->FetchAllAssoc($sql);
    }

    public function GetEventById($eventId)
    {
        $sql = "SELECT * FROM `vw_event` WHERE id= $eventId ";

        return $this->FetchOneAssoc($sql);
    }

    public function EventPayment($data)
    {
        $event_id = $data['event_id'];
        $payment_amt = $data['payment_amt'];
        $comment = $data['comment'];
        $invNumber = $this->GenerateInvoice("event_payment", "invoice");
        $user_name = User::UserName();

        $sql = "INSERT INTO `event_payment` (`id`, `invoice`, `event_id`, `payment_date`, `payment_amt`, `comment`, `update_by`)";
        $sql .= " VALUES (NULL, $invNumber, '$event_id', current_timestamp(), '$payment_amt', ' $comment', '$user_name');";

        return $this->ExecuteStatement($sql);
    }

    public function Discount($data)
    {
        $event_id = $data['event_id'];
        $discount_amt = $data['discount_amt'];

        $user_name = User::UserName();

        $discCount = $this->conn->query("SELECT COUNT(*) AS `is_disc` FROM `event_payment` WHERE `invoice`=0 AND `event_id`=$event_id")->fetch_assoc();

        $sql = "";

        if ($discCount['is_disc'] == 1) {
            $sql = "UPDATE `event_payment` SET `payment_amt`=$discount_amt, `comment`='Update Discount', `update_by`='$user_name' WHERE `invoice`=0 AND `event_id`=$event_id";
        } else if ($discCount['is_disc'] > 1) {
            return 0;
        } else {
            $sql = "INSERT INTO `event_payment` (`id`, `invoice`, `event_id`, `payment_date`, `payment_amt`, `comment`, `update_by`)";
            $sql .= " VALUES (NULL, 0, $event_id, current_timestamp(), $discount_amt, 'Discount', '$user_name');";
        }

        if ($sql == "") {
            return 0;
        }
        return $this->ExecuteStatement($sql);
    }

    public function UpdateEventPayment($data)
    {
        $payId = $data['payId'];
        $payment_amt = $data['payment_amt'];
        $comment = $data['comment'];
        $user_name = User::UserName();

        $sql = "UPDATE `event_payment` SET `payment_date`=current_timestamp(), `payment_amt`='$payment_amt', `comment`= '$comment', `update_by`='$user_name' WHERE `id`=$payId;";

        return $this->ExecuteStatement($sql);
    }

    public function DeleteEventPayment($id)
    {
        $sql = "DELETE FROM `event_payment` WHERE `id`=$id;";
        return $this->ExecuteStatement($sql);
    }

    public function GetEventPaymentById($id)
    {
        $sql = "SELECT * FROM `event_payment` WHERE id= $id ";

        return $this->FetchOneAssoc($sql);
    }

    public function EventPayListByEventId($eventId)
    {
        $sql = "SELECT * FROM `event_payment` WHERE `event_id`=$eventId ORDER BY `payment_date` DESC;";

        return $this->FetchAllAssoc($sql);
    }

    public function LastEventPayByEventId($eventId)
    {
        $sql = "SELECT * FROM `event_payment` WHERE `id` IN (SELECT MAX(`id`) FROM `event_payment` WHERE `event_id`=$eventId);";

        return $this->FetchOneAssoc($sql);
    }

    public function AddEventType($data)
    {
        $sql = '';
        $name = $data['name'];
        $status = $data['status'];

        $sql = "INSERT INTO `event_type` (`id`,`name`, `status`) ";
        $sql .= "VALUES (NULL,'$name','$status')";

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    public function EventTypeList($onlyActive = false)
    {
        $sql = "SELECT * FROM event_type";
        if ($onlyActive) {
            $sql .= " WHERE status=1";
        }
        $sql .= " ORDER BY status DESC";

        return $this->FetchAllAssoc($sql);
    }

    public function GetEventTypeById($id)
    {
        $sql = "SELECT * FROM event_type WHERE id= $id";

        return $this->FetchOneAssoc($sql);
    }

    public function UpdateEventType($data)
    {
        $sql = '';
        $id = $data['id'];
        $name = $data['name'];
        $status = $data['status'];

        if ($id > 0 || $id != '') {
            $sql = "UPDATE `event_type` SET `name`='$name', `status`='$status' WHERE `id` = $id";
        }

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    public function AddPackage($data)
    {
        $sql = '';
        $name = $data['name'];
        $amt = $data['amount'];
        $status = $data['status'];

        $sql = "INSERT INTO `package` (`id`,`name`, `amt`, `status`) ";
        $sql .= "VALUES (NULL,'$name','$amt','$status')";

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    public function GetPackage($id = 0, bool $onlyActive = true)
    {
        $sql = "SELECT p.*, ifnull(pd.discount_amt, 0) AS discount_amt, ifnull(p.amt, 0) - ifnull(pd.discount_amt, 0) AS after_discount 
        FROM package AS p 
        LEFT JOIN (
        SELECT p.package_id, p.discount_amt 
        FROM package_discount AS p 
        WHERE CURRENT_DATE() BETWEEN p.valid_from AND p.valid_until
        ) AS pd ON p.id = pd.package_id";

        $where = "";
        $data = (int) ($id ?? 0);
        if ($data > 0) {
            $where = " WHERE p.id = $data";
        }

        if ($onlyActive) {
            $where .= (empty($where) ? " WHERE" : " AND") . " p.status = 1";
        }

        $sql .= $where;

        if ($data > 0) {
            $sql .= " LIMIT 1";
            return $this->FetchOneAssoc($sql);
        } else {
            return $this->FetchAllAssoc($sql);
        }
    }

    public function UpdatePackage($data)
    {
        $sql = '';
        $id = $data['id'];
        $name = $data['name'];
        $amt = $data['amount'];
        $status = $data['status'];

        if ($id > 0 || $id != '') {
            $sql = "UPDATE `package` SET `name`='$name', `amt`='$amt', `status`='$status' WHERE `id` = $id";
        }

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    public function AddPackageDetails($data)
    {
        $sql = '';
        $package_id = $data['package_id'];
        $title = $data['title'];
        $details = $data['details'] ?? '';
        $order = $data['order'] ?? 1;
        $status = $data['status'];

        $sql = "INSERT INTO `package_details` (`id`,`package_id`, `title`, `details`, `order`, `status`) ";
        $sql .= "VALUES (NULL,'$package_id','$title','$details','$order','$status')";

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    /**
     * Retrieves Package Details.
     * @param mixed $id Provide Package Details ID or Package ID.
     * @param bool $packageId If true, then $id is Package ID; otherwise $id is Package Details ID.
     * @param bool $onlyActive If true, returns only active; otherwise, returns all bills.
     * @return array|bool|null
     */
    public function GetPackageDetails($id = 0, bool $packageId = false, bool $onlyActive = true)
    {
        $where = [];
        $single = false;


        if (!empty($id)) {
            $column = $packageId ? "pd.package_id" : "pd.id";

            if (is_string($id) && strpos($id, ',') !== false) {
                $idArray = array_filter(array_map('intval', explode(',', $id)));
                if (!empty($idArray)) {
                    $idList = implode(',', $idArray);
                    $where[] = "$column IN ($idList)";
                }
            } else {
                $id = (int) $id;
                if ($id > 0) {
                    $where[] = "$column = $id";
                    $single = $packageId ? false : true;
                }
            }
        }

        if ($onlyActive) {
            $where[] = "pd.status = 1";
        }

        $sql = "SELECT pd.*, p.name AS package_name FROM package_details AS pd";
        $sql .= " LEFT JOIN package AS p ON pd.package_id = p.id";
        $sql .= (empty($where) ? "" : " WHERE " . implode(" AND ", $where));
        $sql .= " ORDER BY pd.package_id, pd.order ASC";

        if ($single) {
            $sql .= " LIMIT 1";
            return $this->FetchOneAssoc($sql);
        } else {
            return $this->FetchAllAssoc($sql);
        }
    }

    public function UpdatePackageDetails($data)
    {
        $sql = '';
        $id = $data['id'];
        $package_id = $data['package_id'];
        $title = $data['title'];
        $details = $data['details'] ?? '';
        $order = $data['order'] ?? 1;
        $status = $data['status'];

        if ($id > 0 || $id != '') {
            $sql = "UPDATE `package_details` SET `package_id`='$package_id', `title`='$title', `details`='$details', `order`='$order', `status`='$status' WHERE `id` = $id";
        }

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    public function AddDiscount($data)
    {
        $sql = '';
        $package_id = $data['packageId'];
        $valid_from = $data['validFrom'];
        $valid_until = $data['validUntil'];
        $discount_amt = $data['discountAmount'];

        $sql = "INSERT INTO `package_discount` (`id`,`package_id`, `valid_from`, `valid_until`, `discount_amt`) ";
        $sql .= "VALUES (NULL,'$package_id','$valid_from','$valid_until', '$discount_amt')";

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    public function DiscountList()
    {
        $sql = "SELECT d.*, p.name AS package_name FROM package_discount AS d LEFT JOIN package AS p ON d.package_id=p.id";
        $sql .= " ORDER BY valid_until DESC";

        return $this->FetchAllAssoc($sql);
    }

    public function GetDiscountById($id)
    {
        $sql = "SELECT d.*, p.name AS package_name FROM package_discount AS d LEFT JOIN package AS p ON d.package_id=p.id";
        $sql .= " WHERE d.id= $id";

        return $this->FetchOneAssoc($sql);
    }

    public function UpdateDiscount($data)
    {
        $sql = '';
        $id = $data['id'];
        $package_id = $data['packageId'];
        $valid_from = $data['validFrom'];
        $valid_until = $data['validUntil'];
        $discount_amt = $data['discountAmount'];

        if ($id > 0 || $id != '') {
            $sql = "UPDATE `package_discount` SET `package_id`='$package_id', `valid_from`='$valid_from', `valid_until`='$valid_until' 
            , `discount_amt`=$discount_amt WHERE `id` = $id";
        }

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    public function DeleteDiscount($id)
    {
        $sql = "DELETE FROM `package_discount` WHERE `id`=$id;";
        return $this->ExecuteStatement($sql);
    }

    public function AddAddOns($data)
    {
        $sql = '';
        $desig_code = $data['desig'] ?? 0;
        $name = $data['name'];
        $qty = $data['qty'] ?? 0;
        $amt = $data['amount'];
        $status = $data['status'];

        $sql = "INSERT INTO `addons` (`id`, `desig_code`, `qty`, `name`, `amt`, `status`) ";
        $sql .= "VALUES (NULL, '$desig_code', '$qty', '$name','$amt','$status')";

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    /**
     * Summary of GetAddOns
     * @param bool $onlyActive
     * @param int $id if any value in id then get specific data otherwise get all data
     * @return array<array|bool|null>
     */
    public function GetAddOns(bool $onlyActive = false, int $id = 0)
    {
        $sql = "SELECT a.*, CASE WHEN a.qty > 0 THEN CONCAT(a.name, ' (', a.qty, ')') ELSE a.name END AS name_qty FROM addons AS a";

        $where = "";

        if ($id > 0) {
            $where = empty($where) ? " WHERE a.id = $id" : " AND a.id = $id";
        }

        if ($onlyActive) {
            $where = empty($where) ? " WHERE a.status = 1" : " AND a.status = 1";
        }

        $sql .= $where;

        $sql .= " ORDER BY a.status DESC";

        return $id > 0 ? $this->FetchOneAssoc($sql) : $this->FetchAllAssoc($sql);
    }

    public function UpdateAddOns($data)
    {
        $sql = '';
        $id = $data['id'];
        $desig_code = $data['desig'] ?? 0;
        $name = $data['name'];
        $qty = $data['qty'] ?? 0;
        $amt = $data['amount'];
        $status = $data['status'];

        if ($id > 0 || $id != '') {
            $sql = "UPDATE `addons` SET `desig_code` = '$desig_code', `qty` = '$qty', `name`='$name', `amt`='$amt', `status`='$status' WHERE `id` = $id";
        }

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }
    }

    public function AddEventAddOns($addOnsId, $eventId)
    {
        $addOnsIdArr = explode(',', $addOnsId);
        sort($addOnsIdArr);

        if (count($addOnsIdArr) > 0 && $addOnsId != 0 && $eventId > 0) {
            $values = [];

            foreach ($addOnsIdArr as $id) {
                $addOnsData = $this->GetAddOns(false, $id);

                if (empty($addOnsData)) {
                    continue;
                }

                $desig_code = $addOnsData['desig_code'];
                $qty = $addOnsData['qty'];
                $addOnsAmount = $addOnsData['amt'];
                $addOnsName = $addOnsData['name'];

                $values[] = "(NULL, '$eventId', '$id', '$desig_code', '$qty', '$addOnsName', '$addOnsAmount')";
            }

            if (!empty($values)) {
                $addOnsInsertSql = "INSERT INTO event_addons (id, event_id, addons_id, desig_code, qty, addons_name, amount) VALUES " . implode(", ", $values) . ";";

                try {
                    $result = $this->ExecuteStatement($addOnsInsertSql);
                    return $result;
                } catch (mysqli_sql_exception $e) {
                    return $e->getMessage();
                }
            }
        }
    }

    /**
     * Summary of GetEventAddOns
     * if include g then GROUP BY addons_id (ex: eig)
     * @param mixed $data '' = All, (ex: 1 (id), 1 (event_id), etc)
     * @param mixed $for '' = id, ei = event_id
     * @return array<array|bool|null> 
     */
    public function GetEventAddOns($data = '', $for = '')
    {
        $sql = "SELECT ea.*, CASE WHEN ea.qty > 0 THEN CONCAT(ea.addons_name, ' (', ea.qty, ')') ELSE ea.addons_name END AS addons_name_qty FROM event_addons AS ea";
        $where = "";

        if (!empty($data)) {
            switch ($for) {
                case 'ei':
                    $where = (empty($where)) ? " WHERE ea.event_id = $data" : " AND ea.event_id = $data";
                    break;
                case 'eig':
                    $sql = "SELECT ea.event_id, ea.addons_id, ea.desig_code, SUM(ifnull(ea.qty,0)) AS qty, ea.addons_name, SUM(ifnull(ea.amount, 0)) AS amount 
                    FROM event_addons AS ea 
                    WHERE ea.event_id = $data 
                    GROUP BY addons_id ";
                    break;
            }
        }

        $sql .= $where;

        $sql .= " ORDER BY ea.addons_id ASC";

        return $this->FetchAllAssoc($sql);
    }

    public function DeleteEventAddOns($eventAddOnsId)
    {
        $sql = "DELETE FROM `event_addons` WHERE `id`=$eventAddOnsId;";
        return $this->ExecuteStatement($sql);
    }

    public function BookingInfo($data)
    {
        $id = (int) ($data['id'] ?? 0);
        $clientName = $data['client_name'] ?? '';
        $contactNo = $data['contact_no'] ?? '';
        $emailAddress = $data['email'] ?? '';
        $eventTypeId = $data['event_type'] ?? 0;
        $groomName = $data['groom_name'] ?? '';
        $brideName = $data['bride_name'] ?? '';
        $birthdayPerson = $data['birthday_person'] ?? '';
        $organizationName = $data['organization_name'] ?? '';
        $packageId = $data['package'] ?? 0;
        $amountTk = $data['amount'] ?? 0;
        $location = $data['location'] ?? '';
        $eventDateTime = $data['event_date_time'] ?? '';
        $songPreference = $data['song_preference'] ?? '';
        $addOnsId = $data['addons'] ?? 0;
        $yourPreference = $data['your_preference'] ?? '';

        if ($amountTk == null || $amountTk == '' || $amountTk < 0) {
            $amountTk = 0;
        }

        $packageDtl = $this->GetPackage($packageId);
        $packageAmount = $packageDtl['after_discount'] ?? 0;
        $packageName = $packageDtl['name'] ?? '';

        if ($addOnsId == null || $addOnsId == '' || $addOnsId < 0) {
            $addOnsId = 0;
        }

        $eventDateTimeFormat = "";
        if (!empty($eventDateTime)) {
            $dTime = DateTime::createFromFormat('d-M-Y h:i A', $eventDateTime);
            if ($dTime instanceof DateTime) {
                $eventDateTimeFormat = $dTime->format('Y-m-d H:i:s');
            } else {
                return "Invalid date format";
            }
        }

        $today = new DateTime();
        $today = $today->setTime(0,0,0,0);
        $evd = new DateTime($eventDateTimeFormat);
        $evd = $evd->setTime(0,0,0,0);
        
        if ($evd < $today && $id == 0) {
            return "Invalid date";
        }

        $event_type_id = $eventTypeId;
        $package_id = '';
        $package_name = '';
        $client_name = $this->SanitizeString($clientName);
        $phone = $contactNo;
        $email = $emailAddress;
        $groom = '';
        $bride = '';
        $birthday_person = '';
        $org_name = '';
        $venue = $this->SanitizeString($location);
        $event_date_time = $eventDateTimeFormat;
        $song = '';
        $preference = '';
        $amount = 0;
        $update_by = 'sys';

        if (
            $eventTypeId == 1 ||   // Wedding
            $eventTypeId == 2 ||   // Reception
            $eventTypeId == 3 ||   // Engagement
            $eventTypeId == 4 ||   // Halud
            $eventTypeId == 5 ||   // Mehadi
            $eventTypeId == 6 ||   // Akhd
            $eventTypeId == 7 ||   // Anniversary
            $eventTypeId == 8 ||   // Rong Khela
            $eventTypeId == 9 ||   // Ashirbad
            $eventTypeId == 14      // Dala
        ) {
            $package_id = $packageId;
            $package_name = $this->SanitizeString($packageName);
            $groom = $this->SanitizeString($groomName);
            $bride = $this->SanitizeString($brideName);
            $amount = $packageAmount;
        } else if ($eventTypeId == 10) { // Birthday
            $package_id = $packageId;
            $package_name = $this->SanitizeString($packageName);
            $birthday_person = $this->SanitizeString($birthdayPerson);
            $amount = $packageAmount;
        } else if (
            $eventTypeId == 11 ||  // Architectural
            $eventTypeId == 12     // Corporate
        ) {
            $org_name = $this->SanitizeString($organizationName);
            $amount = $amountTk;
        } else if ($eventTypeId == 13) { // Fashion & Lifetyle
            $amount = $amountTk;
        }

        if (
            $packageId == 1 ||   // Standard - I
            $packageId == 2 ||   // Standard - II
            $packageId == 3 ||   // Standard - III
            $packageId == 4 ||   // Premium - I
            $packageId == 5 ||   // Premium - II
            $packageId == 6 ||   // Premium - III
            $packageId == 7 ||   // Elite - I
            $packageId == 8 ||   // Elite - II
            $packageId == 9 ||   // Sonaton - Standrad
            $packageId == 10 ||  // Sonaton - Premium
            $packageId == 11 ||  // Only Photography
            $packageId == 12 ||  // Only Photography - Premium
            $packageId == 14     // Quick Look
        ) {
            $song = $this->SanitizeString($songPreference);
            $preference = $yourPreference;
        } else if ($packageId == 13) { // Other
            $amount = $amountTk;
            $preference = $yourPreference;
        }

        $eventSql = "";

        if ($id > 0) {
            $status = $data['event_status'] ?? '';
            if (!EventStatus::IsValid($status)) {
                return false;
            }

            $eventSql = "UPDATE `event` SET `event_type_id` = '$event_type_id', `package_id` = '$package_id', `package_name` = '$package_name', `client_name` = '$client_name', `phone` = '$phone', `email` = '$email', `groom` = '$groom', `bride` = '$bride', `birthday_person` = '$birthday_person', `org_name` = '$org_name', `venue` = '$venue', `event_date_time` = '$event_date_time', `song` = '$song', `preference` = '$preference', `amount` = '$amount', `update_by` = '$update_by', `update_at` = current_timestamp(), `status` = '$status' WHERE `id` = '$id'";

            try {
                return $this->ExecuteStatement($eventSql);
            } catch (mysqli_sql_exception $e) {
                return $e->getMessage();
            }
        } else {
            $status = EventStatus::OPEN;
            $eventSql = "INSERT INTO `event` (id, booking_date_time, event_type_id, package_id, package_name, client_name, phone, email, groom, bride, birthday_person, org_name, venue, event_date_time, song, preference, amount, update_by, update_at, status) VALUES (NULL, current_timestamp(), '$event_type_id', '$package_id', '$package_name', '$client_name', '$phone', '$email', '$groom', '$bride', '$birthday_person', '$org_name', '$venue', '$event_date_time', '$song', '$preference', '$amount', '$update_by', current_timestamp(), '$status')";

            $eventId = 0;

            try {
                // $ip = $this->SanitizeString(GetClientIP());
                // $country = $this->SanitizeString(GetCountryByIP($ip));
                // $city = $this->SanitizeString(GetCountryByIP($ip, "city"));
                // $browser = $this->SanitizeString(GetBrowserName());

                // $this->ExecuteStatement("SET @tr_ip = '$ip', @tr_country = '$country', @tr_city = '$city', @tr_browser = '$browser'");

                $eventId = $this->ExecuteStatement($eventSql, true);
            } catch (mysqli_sql_exception $e) {
                return $e->getMessage();
            }

            $this->AddEventAddOns($addOnsId, $eventId);

            return $eventId;
        }
    }

    public function GetCountryList()
    {
        $sql = "SELECT * FROM country ORDER BY id";

        return $this->FetchAllAssoc($sql);
    }

    public function GenerateBill($data)
    {
        $eventId = $data['eventId'] ?? 0;

        if ($eventId > 0) {
            $eventDtl = $this->GetEventById($eventId);
            $newBillNumber = $this->GenerateInvoice("bill_mst", "bill_num");

            if ($eventDtl) {
                $bill_num = $newBillNumber;
                $name = $eventDtl["client_name"];
                $bill_amt = 0;
                $discount = 0;
                $ttl_bill_amt = 0;
                $create_by = User::UserName();

                $billMstSql = "INSERT INTO `bill_mst` (`bill_num`, `name`, `bill_amt`, `discount`, `ttl_bill_amt`, `create_by`, `create_at`, `update_by`, `update_at`) 
                    VALUES ('$bill_num','$name', '$bill_amt','$discount','$ttl_bill_amt','$create_by',current_timestamp(),'','')";

                $mstFlag = null;
                try {
                    $mstFlag = $this->ExecuteStatement($billMstSql);
                } catch (mysqli_sql_exception $e) {
                    return $e->getMessage();
                }

                if ($mstFlag) {
                    $dataArr = $data;
                    $dataArr["billNumber"] = $bill_num;
                    $this->AddUpdateEventInBill($dataArr);
                }
            }

        }

        return false;

    }

    public function AddUpdateEventInBill($data, $isUpdate = false)
    {
        $billNumber = $data['billNumber'] ?? 0;
        $eventId = (int) ($data['eventId'] ?? 0);
        $addHour = (float) ($data['addHour'] ?? 1);
        $addHourAmt = (int) ($data['addHourAmt'] ?? 0);
        $outdoorDtl = $data['outdoorDtl'] ?? '';
        $outdoorVenue = $data['outdoorVenue'] ?? '';
        $outdoorPhoto = (int) ($data['outdoorPhoto'] ?? 0);
        $outdoorCinemeto = (int) ($data['outdoorCinemeto'] ?? 0);
        $outdoorDrone = (int) ($data['outdoorDrone'] ?? 0);
        $outdoorAmt = (float) ($data['outdoorAmt'] ?? 0);

        $addTime = "";

        $timeExist = $this->Exists("SELECT 1 FROM `event_addition_time` WHERE `event_id` = '$eventId' AND `bill_num` = '$billNumber' LIMIT 1");
        if ($addHourAmt > 0) {
            $addTime = $addHour . "|" . $addHourAmt;

            $timeSql = "";
            if ($timeExist) {
                $timeSql = "UPDATE `event_addition_time` SET `duration_hours` = '$addHour', `amount` = '$addHourAmt' WHERE `event_id` = '$eventId' AND `bill_num` = '$billNumber'";
            } else {
                $timeSql = "INSERT INTO `event_addition_time` (`event_id`, `bill_num`, `duration_hours`, `amount`) VALUES ('$eventId','$billNumber','$addHour','$addHourAmt')";
            }

            try {
                $this->ExecuteStatement($timeSql);
            } catch (mysqli_sql_exception $e) {
                return $e->getMessage();
            }
        } else {
            if ($timeExist) {
                $this->ExecuteStatement("DELETE FROM `event_addition_time` WHERE `event_id` = '$eventId' AND `bill_num` = '$billNumber'");
            }
        }

        $outExist = $this->Exists("SELECT 1 FROM `event_outdoor` WHERE `event_id` = '$eventId' AND `bill_num` = '$billNumber' LIMIT 1");
        if ($outdoorAmt > 0) {
            $outSql = "";
            if ($outExist) {
                $outSql = "UPDATE `event_outdoor` SET `hours` = '$outdoorDtl', `venue` = '$outdoorVenue', `photog` = '$outdoorPhoto', `cinemetog` = '$outdoorCinemeto', `drone` = '$outdoorDrone', `amt` = '$outdoorAmt' WHERE `event_id` = '$eventId' AND `bill_num` = '$billNumber'";
            } else {
                $outSql = "INSERT INTO `event_outdoor` (`event_id`, `bill_num`, `hours`, `venue`, `photog`, `cinemetog`, `drone`, `amt`) 
                VALUES ('$eventId','$billNumber','$outdoorDtl','$outdoorVenue','$outdoorPhoto','$outdoorCinemeto','$outdoorDrone','$outdoorAmt')";
            }

            try {
                $this->ExecuteStatement($outSql);
            } catch (mysqli_sql_exception $e) {
                return $e->getMessage();
            }
        } else {
            if ($outExist) {
                $this->ExecuteStatement("DELETE FROM `event_outdoor` WHERE `event_id` = '$eventId' AND `bill_num` = '$billNumber'");
            }
        }

        if ($eventId > 0) {
            $eventDtl = $this->GetEventById($eventId);

            if ($eventDtl) {
                $event_amount = (int) ($eventDtl["amount"] ?? 0);
                $event_total = (int) ($eventDtl["total"] ?? 0);
                $adjAmt = (int) ($data['adjustment'] ?? 0);
                $afterAdj = $event_amount + $adjAmt;
                $total = $event_total + $adjAmt;

                $bill_num = $billNumber;
                $event_id = $eventId;
                $event_name = $eventDtl["event_name"];
                $event_date_time = $eventDtl["event_date_time"];
                $package_id = $eventDtl["package_id"];
                $package_name = $eventDtl["package_name"];
                $package_dtl = "";
                $duration = eventDuration($event_date_time, $event_id, $package_id);
                $add_time = $addTime;
                $outdoor_amt = $outdoorAmt;
                $addons = "";
                $event_amt = $event_amount;
                $adjustment = $adjAmt;
                $after_adj = $afterAdj;
                $total_amt = $total;


                $addOnsDtlList = $this->GetEventAddOns($eventId, 'ei');
                $addonsArray = [];
                if ($addOnsDtlList) {
                    foreach ($addOnsDtlList as $row) {
                        $addonsArray[] = $row['addons_name_qty'] . '|' . $row['amount'];
                    }
                    $addons = implode(',', $addonsArray);
                }

                $packageDtlList = $this->GetPackageDetails($package_id, true, true);

                $packageDtlArr = [];
                if ($packageDtlList) {
                    foreach ($packageDtlList as $row) {
                        $packageDtlArr[] = $row["title"] . "|" . $row["details"];
                    }

                    $package_dtl = implode(',', $packageDtlArr);
                }

                $billDtlSql = "";

                if ($isUpdate) {
                    $billDtlId = (int) ($data['billDtlId'] ?? 0);
                    if ($billDtlId > 0) {
                        $billDtlSql = "UPDATE `bill_dtl` SET `event_name` = '$event_name', `event_date_time` = '$event_date_time', `package_id` = '$package_id', `package_name` = '$package_name', `package_dtl` = '$package_dtl', `duration` = '$duration', `add_time` = '$add_time', `outdoor_amt` = '$outdoor_amt', `addons` = '$addons', `event_amt` = '$event_amt', `adjustment` = '$adjustment', `after_adj` = '$after_adj', `total_amt` = '$total_amt' WHERE `id` = '$billDtlId'";
                    } else {
                        return false;
                    }
                } else {
                    $billDtlSql = "INSERT INTO `bill_dtl` (`id`, `bill_num`, `event_id`, `event_name`, `event_date_time`, `package_id`, `package_name`, `package_dtl`, `duration`, `add_time`, `outdoor_amt`, `addons`, `event_amt`, `adjustment`, `after_adj`, `total_amt`) VALUES (NULL, '$bill_num', '$event_id', '$event_name', '$event_date_time', '$package_id', '$package_name', '$package_dtl', '$duration', '$add_time', '$outdoor_amt', '$addons', '$event_amt', '$adjustment', '$after_adj', '$total_amt')";
                }

                $dtlFlag = null;
                try {
                    $dtlFlag = $this->ExecuteStatement($billDtlSql);
                } catch (mysqli_sql_exception $e) {
                    return $e->getMessage();
                }

                if ($dtlFlag) {
                    return $this->UpdateBillMst($billNumber, true);
                }

                return false;

            }
        }

        return false;

    }

    public function UpdateBillMst($data, $baseBillDtl = false)
    {
        if ($baseBillDtl) {
            $billNumber = (int) ($data ?? 0);
            $billMst = $this->GetBillMst(false, $billNumber);

            if (!empty($billMst)) {
                $discount = (int) $billMst["discount"] ?? 0;

                $bill_amt = $this->FetchSingleValue("SELECT SUM(ifnull(total_amt, 0)) AS total_amt FROM bill_dtl WHERE bill_num = '$billNumber'", true);

                $ttl_bill_amt = $bill_amt - $discount;

                $sqlMst = "UPDATE `bill_mst` SET `bill_amt` = '$bill_amt', `ttl_bill_amt` = '$ttl_bill_amt' WHERE `bill_num` = '$billNumber';";

                try {
                    return $this->ExecuteStatement($sqlMst);
                } catch (mysqli_sql_exception $e) {
                    return $e->getMessage();
                }
            }
        } else {
            $billNumber = (int) ($data["billNumber"] ?? 0);
            $billDiscount = (int) ($data["billDiscount"] ?? 0);
            $billerName = $data["billerName"] ?? '';

            if ($billerName != '' && $billerName != null && $billDiscount >= 0) {
                $billMst = $this->GetBillMst(false, $billNumber);
                $mst_bill_amt = (int) $billMst["bill_amt"];

                $ttl_bill_amt = ($mst_bill_amt - $billDiscount);
                $update_by = User::UserName();

                $mstSql = "UPDATE `bill_mst` SET `name` = '$billerName', `discount` = '$billDiscount', `ttl_bill_amt` = '$ttl_bill_amt', `update_by` = '$update_by', `update_at` = current_timestamp() WHERE `bill_num` = '$billNumber';";

                try {
                    return $this->ExecuteStatement($mstSql);
                } catch (mysqli_sql_exception $e) {
                    return $e->getMessage();
                }
            }
        }

        return false;
    }

    public function EditDeleteBillDtl($data)
    {
        $billDtlId = (int) ($data['billDtlId'] ?? 0);
        $deleteDtl = $data['delete'] ?? false;

        if ($billDtlId > 0) {

            if ($deleteDtl) {
                $bill_number = 0;
                $pay = $this->Exists("SELECT 1 FROM event_payment AS p WHERE p.event_id IN (SELECT bd.event_id FROM bill_dtl AS bd WHERE bd.id = $billDtlId) LIMIT 1");
                if ($pay) {
                    return 'pay';
                } else {
                    $billDtl = $this->GetBillDtlById($billDtlId);
                    $bill_number = $billDtl["bill_num"];
                    $event_id = $billDtl["event_id"];

                    $dtlFlag = null;
                    try {
                        $this->ExecuteStatement("DELETE FROM `event_addition_time` WHERE `event_id` = '$event_id' AND `bill_num` = '$bill_number'");
                        $this->ExecuteStatement("DELETE FROM `event_outdoor` WHERE `event_id` = '$event_id' AND `bill_num` = '$bill_number'");
                        $dtlFlag = $this->ExecuteStatement("DELETE FROM `bill_dtl` WHERE `id` = '$billDtlId'");
                    } catch (mysqli_sql_exception $e) {
                        return $e->getMessage();
                    }

                    if ($dtlFlag) {
                        $flag = $this->UpdateBillMst($bill_number, true);
                        if ($flag) {
                            return $bill_number;
                        }
                    }
                }
            } else {
                return $this->AddUpdateEventInBill($data, true);
            }
        }

        return false;
    }

    /**
     * Summary of GetEventOutdoor
     * @param mixed $data
     * @param bool $billNum If true, then $data is billNumber; otherwise $data is EventId.
     * @return array|bool|null
     */
    public function GetEventOutdoor($data, bool $billNum = false)
    {
        $where = "";

        if ($billNum) {
            $where = " bill_num = $data";
        } else {
            $where = " event_id = $data LIMIT 1";
        }

        $sql = "SELECT * FROM event_outdoor WHERE $where";

        if ($billNum) {
            return $this->FetchAllAssoc($sql);
        } else {
            return $this->FetchOneAssoc($sql);
        }

    }

    public function DeleteBill($data)
    {
        $billNumber = (int) ($data["billNumber"] ?? 0);

        if ($billNumber > 0) {
            $pay = $this->Exists("SELECT 1 FROM event_payment AS p WHERE p.event_id IN (SELECT bd.event_id FROM bill_dtl AS bd WHERE bd.bill_num = $billNumber) LIMIT 1");
            if ($pay) {
                return 'pay';
            } else {
                try {
                    $this->ExecuteStatement("DELETE FROM `event_addition_time` WHERE `bill_num` = '$billNumber'");
                    $dtlFlag = $this->ExecuteStatement("DELETE FROM `bill_dtl` WHERE `bill_num` = '$billNumber'");

                    if ($dtlFlag) {
                        $mstFlag = $this->ExecuteStatement("DELETE FROM `bill_mst` WHERE `bill_num` = '$billNumber'");
                        return $mstFlag;
                    }
                } catch (mysqli_sql_exception $e) {
                    return $e->getMessage();
                }
            }
        } else {
            return false;
        }
    }

    /**
     * Retrieves bill records.
     * @param bool $dueBill If true, returns only due; otherwise, returns all bills.
     * @param mixed $data Provide BillNumber or EventID.
     * @param bool $billNum If true, then $data is billNumber; otherwise $data is EventId.
     * @return array|bool|null
     */
    public function GetBillMst(bool $dueBill = false, $data = 0, bool $billNum = true)
    {
        $where = [];

        $billNumEventId = (int) ($data ?? 0);
        if ($billNumEventId > 0) {
            if ($billNum) {
                $where[] = "b.bill_num = '$billNumEventId'";
            } else {
                $where[] = "b.bill_num IN (SELECT d.bill_num from bill_dtl AS d WHERE d.event_id = '$billNumEventId')";
            }
        }

        if ($dueBill) {
            $where[] = "b.due_amount > 0";
        }

        $sql = "SELECT b.* FROM vw_bill AS b" . (empty($where) ? "" : " WHERE " . implode(" AND ", $where));

        if ($billNumEventId > 0) {
            $sql .= " LIMIT 1";
            return $this->FetchOneAssoc($sql);
        } else {
            return $this->FetchAllAssoc($sql);
        }
    }

    public function GetBillDtlByBillNumber($billNumber, $forBillPayment = false)
    {
        $sql = "SELECT * FROM bill_dtl WHERE bill_num = $billNumber ORDER BY event_date_time ASC;";

        if ($forBillPayment) {
            $sql = "SELECT vw.id, vw.package_name, vw.event_name, vw.due_amount 
                    FROM vw_event AS vw
                    WHERE vw.id IN (SELECT bd.event_id FROM bill_dtl AS bd WHERE bd.bill_num = $billNumber)
                    ORDER BY vw.event_date_time ASC;";
        }

        return $this->FetchAllAssoc($sql);
    }

    public function GetBillDtlById($billDtlId)
    {
        $sql = "SELECT * FROM bill_dtl WHERE id = $billDtlId;";

        return $this->FetchOneAssoc($sql);
    }

    /**
     * Summary of GetBillPayByBillNum
     * @param mixed $billNumber
     * @param bool $baseEventId
     * @param bool $includeDiscount
     * @param bool $returnTotal
     * @return array<array|bool|null>|int
     */
    public function GetBillPayByBillNum($billNumber, $baseEventId = false, $includeDiscount = false, $returnTotal = false)
    {
        $sql = "";

        if ($returnTotal) {
            $sql = "SELECT SUM(ifnull(p.payment_amt, 0)) AS total FROM event_payment AS p WHERE ";
        } else {
            $sql = "SELECT p.* FROM event_payment AS p WHERE ";
        }

        if ($baseEventId) {
            $sql .= " p.event_id IN (SELECT bd.event_id FROM bill_dtl AS bd WHERE bd.bill_num = $billNumber)";
        } else {
            $sql .= " p.bill_num = $billNumber";
        }

        if (!$includeDiscount) {
            $sql .= " AND p.invoice > 0";
        }

        $result = null;

        if ($returnTotal) {
            $result = $this->FetchOneAssoc($sql);
            $result = (int) ($result["total"] ?? 0);
        } else {
            $result = $this->FetchAllAssoc($sql);
        }

        return $result;
    }

    public function GetBillPayDtlByBillNum($billNumber)
    {
        $sql = "SELECT pi.* FROM event_payment AS pi WHERE pi.event_id IN (SELECT b.event_id FROM bill_dtl AS b WHERE b.bill_num = '$billNumber') ORDER BY pi.payment_date ASC;";

        return $this->FetchAllAssoc($sql);
    }

    public function GetBillPayByInvoice($invoice, $summary = false)
    {
        $sql = "";
        if ($summary) {
            $sql = "SELECT invoice, bill_num, payment_date, pay_method, received_from, received_by, SUM(ifnull(payment_amt, 0)) AS payment_amt, update_by 
            FROM event_payment WHERE invoice = $invoice 
            GROUP BY invoice, bill_num, payment_date, pay_method, received_from, update_by;";
        }

        if (empty($sql)) {
            return false;
        }

        return $this->FetchOneAssoc($sql);
    }

    public function BillPayment($data)
    {
        $billNum = (int) ($data["billNum"] ?? 0);
        $paymentAmount = (int) ($data["paymentAmount"] ?? 0);
        $received_from = $data["billerName"] ?? "";
        $received_by = $data["receivedBy"];
        $paymentMethod = Payment::Method($data["paymentMethod"]);
        $paymentDate = $data["paymentDate"] ?? "";

        if ($billNum > 0) {

            if (!empty($paymentDate)) {
                $dTime = DateTime::createFromFormat('d-M-Y', $paymentDate);
                if ($dTime instanceof DateTime) {
                    $paymentDateFormat = $dTime->format('Y-m-d');
                } else {
                    return "Invalid date format" + $paymentDate + " dt " + $dTime;
                }
            } else {
                return "Payment Date is not empty.";
            }

            $eventList = $this->FetchAllAssoc("SELECT vw.id AS event_id, ifnull(vw.due_amount, 0) AS due_amount FROM vw_event AS vw WHERE vw.id IN (SELECT ed.event_id FROM bill_dtl AS ed WHERE ed.bill_num = '$billNum')");
            $billDue = 0;

            foreach ($eventList as $row) {
                $billDue += (int) ($row["due_amount"] ?? 0);
            }

            $billDiscAmt = $this->FetchOneAssoc("SELECT ifnull(discount, 0) AS discount FROM bill_mst WHERE bill_num = '$billNum'");
            $billDiscAmt = (int) ($billDiscAmt["discount"] ?? 0);

            $billDiscPay = $this->FetchOneAssoc("SELECT SUM(ifnull(payment_amt, 0)) AS discount FROM event_payment WHERE bill_num = '$billNum' AND invoice = 0 GROUP BY bill_num;");
            $billDiscPay = (int) ($billDiscPay["discount"] ?? 0);

            $billDiscAmt -= $billDiscPay;

            if ($billDue > 0) {

                $invNumber = $this->GenerateInvoice("event_payment", "invoice");
                $invoice = $invNumber;

                $bill_num = $billNum;
                $update_by = User::UserName();
                $rows = [];
                $remainingDiscount = $billDiscAmt;
                $remainingPayAmt = $paymentAmount;

                foreach ($eventList as $row) {
                    $event_id = $row["event_id"];
                    $due_amount = (int) ($row["due_amount"] ?? 0);
                    $comment = 'Bill Payment';

                    if ($due_amount == 0)
                        continue;

                    // Apply discount
                    if ($remainingDiscount > 0) {
                        $pmd = Payment::Method("discount");

                        if ($remainingDiscount >= $due_amount) {
                            $rows[] = "(null, '0', '$bill_num', '$event_id', '$paymentDateFormat', '$pmd', '', '', '$due_amount', 'Bill Discount', '$update_by')";
                            $remainingDiscount -= $due_amount;
                            continue;
                        } else {
                            $rows[] = "(null, '0', '$bill_num', '$event_id', '$paymentDateFormat', '$pmd', '', '', '$remainingDiscount', 'Bill Discount', '$update_by')";
                            $due_amount -= $remainingDiscount;
                            $remainingDiscount = 0;
                        }
                    }

                    // Apply payment
                    if ($remainingPayAmt > 0) {
                        if ($remainingPayAmt >= $due_amount) {
                            $rows[] = "(null, '$invoice', '$bill_num', '$event_id', '$paymentDateFormat', '$paymentMethod', '$received_from', '$received_by', '$due_amount', '$comment', '$update_by')";
                            $remainingPayAmt -= $due_amount;
                        } else {
                            $rows[] = "(null, '$invoice', '$bill_num', '$event_id', '$paymentDateFormat', '$paymentMethod', '$received_from', '$received_by', '$remainingPayAmt', '$comment', '$update_by')";
                            $remainingPayAmt = 0;
                        }
                    }
                }

                if (!empty($rows)) {
                    $sql = "INSERT INTO event_payment (id, invoice, bill_num, event_id, payment_date, pay_method, received_from, received_by, payment_amt, comment, update_by) VALUES " . implode(", ", $rows) . ";";

                    $this->ExecuteStatement($sql);

                    return $invoice;
                }
            } else {
                return 0;
            }
        }

        return false;
    }

    public function AddQuotationMst($data)
    {
        $client_name = trim($data['clientName']);
        $purpose = trim($data['purpose']);
        $amount = (int) ($data['amount'] ?? 0);

        $sql = "INSERT INTO `quote_mst` (`id`, `client_name`, `purpose`, `amount`, `discount`, `total_amt`) VALUES (NULL, '$client_name', '$purpose', '$amount', 0, 0)";

        try {
            return $this->ExecuteStatement($sql, true);
        } catch (mysqli_sql_exception $e) {
            return $e->getMessage();
        }

        return false;
    }

    public function UpdateQuotationMst($data)
    {
        $id = (int) trim($data['id'] ?? 0);
        $client_name = trim($data['clientName'] ?? '');
        $purpose = trim($data['purpose'] ?? '');

        $eventBook = (trim($data['type'] ?? '') === "make_event");
        $make_event = (int) trim($data['make_event'] ?? 0);

        if ($id > 0) {
            $sql = "";

            if ($eventBook) {
                $sql = "UPDATE `quote_mst` SET `make_event` = '$make_event' WHERE `id` = '$id'";
            } else {
                $sql = "UPDATE `quote_mst` SET `client_name` = '$client_name', `purpose` = '$purpose' WHERE `id` = '$id'";
            }

            try {
                return $this->ExecuteStatement($sql);
            } catch (mysqli_sql_exception $e) {
                return $e->getMessage();
            }
        }

        return false;
    }

    public function QuotationDiscount($data)
    {
        $mst_id = (int) ($data["mstId"] ?? 0);
        $discountAmt = (int) ($data["discount"] ?? 0);

        if ($mst_id > 0) {
            $total = $this->FetchSingleValue("SELECT amount FROM quote_mst WHERE id = '$mst_id' LIMIT 1", true);
            $afterDiscount = $total - $discountAmt;

            if ($afterDiscount < 0 || $discountAmt < 0) {
                return ["msg" => "Discount Amount is invalid"];
            }

            $sql = "UPDATE quote_mst SET discount = '$discountAmt', total_amt = '$afterDiscount' WHERE id = '$mst_id'";

            try {
                return $this->ExecuteStatement($sql);
            } catch (mysqli_sql_exception $ex) {
                return $ex->getMessage();
            }
        }

        return false;
    }

    /**
     * Summary of GetQuotation
     * @param mixed $for = '' then AllList, if 'ym' then YearMonthGroup, if 'bl' then Booked List, if 'nbl' then No Booked List
     * @return array<array|bool|null>
     */
    public function GetQuotation($for = '')
    {
        $sql = '';

        switch ($for) {
            case '':
                $sql = "SELECT * FROM quote_mst ORDER BY id DESC";
                break;
            case 'ym':
                $sql = "SELECT YEAR(event_date) AS event_year, MONTH(event_date) AS event_month, COUNT(*) AS total_event FROM quote_details 
                GROUP BY YEAR(event_date), MONTH(event_date) 
                ORDER BY YEAR(event_date) DESC, MONTH(event_date) DESC";
                break;
            case 'bl':
                $sql = "SELECT * FROM quote_mst WHERE make_event = 1 ORDER BY id DESC";
                break;
            case 'nbl':
                $sql = "SELECT * FROM quote_mst WHERE make_event = 0 ORDER BY id DESC";
                break;
            default:
                $sql = "SELECT * FROM quote_mst ORDER BY id DESC";
                break;
        }

        return $this->FetchAllAssoc($sql);
    }

    public function GetQuotationMst(bool $getAll = true, int $mstId = 0)
    {
        $sql = "SELECT * FROM quote_mst";

        if ($getAll) {
            return $this->FetchAllAssoc($sql);
        } else {
            $sql .= " WHERE id=$mstId";
            return $this->FetchOneAssoc($sql);
        }
    }

    public function QuotationDetails($data, bool $update = false)
    {
        $id = (int) trim($data["id"] ?? 0);
        $mst_id = (int) trim($data["quotationId"] ?? 0);
        $event_date = sDateToSql($data["detailsDate"]);
        $location = $data["detailsLocation"] ?? '-';
        $packageDetailsId = trim($data["packageDetailsId"] ?? '');
        $detailsOutdoorAmt = trim($data["detailsOutdoorAmt"] ?? 0);
        $package_id = (int) trim($data["detailsPackage"] ?? 0);
        $package_name = "";
        $package_amt = 0;
        $adjust_amt = trim($data["detailsAdditionalAmt"] ?? 0);
        $out_purp_id = "";
        $out_amt = 0;
        $add_ons_id = (int) trim($data["detailsAddOns"] ?? 0);
        $add_ons_name = "";
        $add_ons_amt = 0;
        $purpose = "";
        $event_time = trim($data["detailsEventTime"] ?? '-');
        $report_time = trim($data["detailsReportTime"] ?? '-');
        $photog = trim($data["detailsPhotog"] ?? '-');
        $cinemetog = trim($data["detailsCinemetog"] ?? '-');
        $drone = trim($data["detailsDrone"] ?? '-');
        $event_type = "";

        if ($add_ons_id > 0) {
            $addOnsDtl = $this->GetAddOns(false, $add_ons_id);
            $add_ons_name = $addOnsDtl["name"];
            $add_ons_amt = (int) ($addOnsDtl["amt"] ?? 0);
        }

        $packageDtlList = "";
        if ($package_id > 0) {
            $packageDtl = $this->GetPackage($package_id);
            $package_name = $packageDtl["name"];
            $package_amt = (int) ($packageDtl["after_discount"] ?? 0);
            $packageDtlList = $this->GetPackageDetails($package_id, true, true);
        } else {
            $package_name = "-";
            $event_type = "Outdoor";
            $out_purp_id = $packageDetailsId;
            $out_amt = $detailsOutdoorAmt;
            $packageDtlList = $this->GetPackageDetails($packageDetailsId);
        }

        if ($packageDtlList) {
            if (isset($packageDtlList["title"])) {
                // Single row, wrap in an array
                $packageDtlList = [$packageDtlList];
            }

            $packageDtlArr = [];
            foreach ($packageDtlList as $row) {
                $packageDtlArr[] = $row["title"] . "|" . $row["details"];
            }

            $purpose = implode(',', $packageDtlArr);
        }

        $total = (int) $package_amt + ((int) $adjust_amt) + (int) $out_amt + (int) $add_ons_amt;

        $sql = "INSERT INTO quote_details (mst_id, event_date, location, package_id, package_name, package_amt, adjust_amt, out_purp_id, out_amt, add_ons_id, add_ons_name, add_ons_amt, purpose, event_time, report_time, photog, cinemetog, drone, event_type, duration, total) VALUES ('$mst_id', '$event_date', '$location', '$package_id', '$package_name', '$package_amt', '$adjust_amt', '$out_purp_id', '$out_amt', '$add_ons_id', '$add_ons_name', '$add_ons_amt', '$purpose', '$event_time', '$report_time', '$photog', '$cinemetog', '$drone', '$event_type', '', '$total');";

        if ($update && $id > 0) {
            $sql = "UPDATE `quote_details` SET `event_date` = '$event_date', `location` = '$location', 
            `package_id` = '$package_id', `package_name` = '$package_name', `package_amt` = '$package_amt', `adjust_amt` = '$adjust_amt', `out_purp_id`  = '$out_purp_id', `out_amt` = '$out_amt', `add_ons_id` = '$add_ons_id', `add_ons_name` = '$add_ons_name', `add_ons_amt` = '$add_ons_amt', `purpose` = '$purpose', `event_time` = '$event_time', `report_time` = '$report_time', `photog` = '$photog', `cinemetog` = '$cinemetog', `drone` = '$drone', `event_type` = '$event_type', `total` = '$total' WHERE `id` = $id";
        }
        try {
            $falg = $this->ExecuteStatement($sql);
            if ($falg) {
                $dtlTotal = $this->FetchSingleValue("SELECT SUM(ifnull(total, 0)) AS total FROM quote_details WHERE mst_id = $mst_id", true);
                $discount = $this->FetchSingleValue("SELECT discount FROM quote_mst WHERE id = $mst_id", true);

                $afterDiscount = (int) $dtlTotal - (int) $discount;

                return $this->ExecuteStatement("UPDATE quote_mst SET amount = $dtlTotal, total_amt = $afterDiscount WHERE id = $mst_id");
            }

        } catch (mysqli_sql_exception $ex) {
            return $ex->getMessage();
        }

        return false;

    }

    /**
     * Summary of QuotationDelete
     * @param bool $allDelete If true, Delete Quotation, otherwise Delete Quotation Details
     * @param mixed $id If $allDelete = true, $id = Quotation ID, otherwise $id = Quotation Details ID
     * @return bool|int|string
     */
    public function QuotationDelete(bool $allDelete = false, $id = 0)
    {
        $flag = false;

        if ($allDelete) {
            if ($this->Exists("SELECT 1 FROM quote_details WHERE mst_id = '$id' LIMIT 1")) {
                $this->ExecuteStatement("DELETE FROM quote_details WHERE mst_id = '$id'");
            }

            if ($this->Exists("SELECT 1 FROM quote_others WHERE mst_id = '$id' LIMIT 1")) {
                $this->ExecuteStatement("DELETE FROM quote_others WHERE mst_id = '$id'");
            }

            $flag = $this->ExecuteStatement("DELETE FROM quote_mst WHERE id = '$id'");
        } else {
            $mst_id = $this->FetchSingleValue("SELECT mst_id FROM quote_details WHERE id = $id", true);

            $flag = $this->ExecuteStatement("DELETE FROM quote_details WHERE id = '$id'");

            if ($flag) {
                if ($mst_id > 0) {
                    $dtlTotal = $this->FetchSingleValue("SELECT SUM(ifnull(total, 0)) AS total FROM quote_details WHERE mst_id = $mst_id", true);
                    $discount = $this->FetchSingleValue("SELECT discount FROM quote_mst WHERE id = $mst_id", true);

                    $afterDiscount = (int) $dtlTotal - (int) $discount;

                    return $this->ExecuteStatement("UPDATE quote_mst SET amount = $dtlTotal, total_amt = $afterDiscount WHERE id = $mst_id");
                }
            }
        }

        return $flag;
    }

    public function GetQuotationDetails($data, bool $dtlId = false, bool $mstId = false, $yearMonthGroup = false)
    {
        $sql = "SELECT * FROM `quote_details` WHERE ";

        if ($dtlId) {
            $sql .= " `id` = $data LIMIT 1";
            return $this->FetchOneAssoc($sql);
        } elseif ($mstId) {
            $sql .= " `mst_id` = $data ORDER BY `event_date` ASC";
            return $this->FetchAllAssoc($sql);
        } elseif ($yearMonthGroup) {
            $fromYear = (int) $data["event_year"];
            $fromMonth = (int) $data["event_month"];
            $toYear = 0;
            $toMonth = 0;

            if ($fromMonth == 12) {
                $toMonth = 1;
                $toYear = $fromYear + 1;
            } else {
                $toYear = $fromYear;
                $toMonth = $fromMonth + 1;
            }

            $fromDate = $fromYear . '-' . $fromMonth . '-' . '1';
            $toDate = $toYear . '-' . $toMonth . '-' . '1';
            $sql .= " `event_date` >= '$fromDate' AND `event_date` < '$toDate' ORDER BY `event_date` ASC";
            return $this->FetchAllAssoc($sql);
        }
    }

    public function QuotationOthers($data)
    {
        $mst_id = (int) trim($data["quotationId"] ?? 0);
        $edited_photo = trim($data["othersEditPhoto"] ?? '-');
        $photo_quality = trim($data["othersPhotoQuality"] ?? '-');
        $promo = trim($data["othersPromo"] ?? '-');
        $video = trim($data["othersVideo"] ?? '-');
        $album = trim($data["othersAlbum"] ?? '-');
        $pf_10l = trim($data["othersPhotoFrame10l"] ?? '-');
        $reels = trim($data["othersReels"] ?? '-');

        $exist = $this->Exists("SELECT 1 FROM quote_others WHERE mst_id = $mst_id LIMIT 1");

        $sql = "";

        if ($exist) {
            $sql = "UPDATE quote_others SET edited_photo = '$edited_photo', 
            photo_quality = '$photo_quality', promo = '$promo', video = '$video', album = '$album', 
            pf_10l = '$pf_10l', reels = '$reels' WHERE mst_id = $mst_id";
        } else {
            $sql = "INSERT INTO 
            quote_others (mst_id, edited_photo, photo_quality, promo, video, 
            album, pf_10l, reels) 
            VALUES ('$mst_id', '$edited_photo', '$photo_quality', '$promo', '$video', 
            '$album', '$pf_10l', '$reels')";
        }

        try {
            return $this->ExecuteStatement($sql);
        } catch (mysqli_sql_exception $ex) {
            return $ex->getMessage();
        }

        return false;
    }

    public function GetQuotationOthers($mstId)
    {
        return $this->FetchOneAssoc("SELECT * FROM quote_others WHERE mst_id = $mstId");
    }

    public function UpdateQuotationCostBreakdown($data)
    {
        $dtlId = (int) ($data["dtlId"] ?? 0);
        $event_type = $data["breakdownEventType"] ?? "";
        $duration = $data["breakdownDuration"] ?? "";

        if ($dtlId > 0 && !empty($event_type) && !empty($duration)) {
            $sql = "UPDATE quote_details SET event_type = '$event_type', duration = '$duration' WHERE id = $dtlId";
            return $this->ExecuteStatement($sql);
        }

        return false;
    }

    /**
     * Summary of GetUserRole
     * @param mixed $id = 0 (return all), 1 (return only specific row)
     * @param bool $reverse = true (return reverse result [id = 1 (return all without 1)])
     * @return array<array|bool|null>
     */
    public function GetUserRole(int $id = 0, bool $reverse = false)
    {
        $sql = "SELECT * FROM `user_role` ORDER BY `id`";

        if ($id > 0) {
            if ($reverse) {
                $sql = "SELECT * FROM `user_role` WHERE `id` != $id ORDER BY `id`";
            } else {
                return $this->FetchOneAssoc("SELECT * FROM `user_role` WHERE `id` = $id ORDER BY `id`");
            }
        }

        return $this->FetchAllAssoc($sql);
    }

    public function GetMenu()
    {
        return $this->FetchAllAssoc("SELECT * FROM `menus` ORDER BY `parent_id`, `order`");
    }

    public function GetMenuPermissions($roleId = null)
    {
        $roleId = $roleId ?? User::UserRole();
        $roleId = intval($roleId);

        if ($roleId === 1) {
            $sql = "SELECT m.*, 1 AS `view`, 1 AS `add`, 1 AS `edit`, 1 AS `delete`
                FROM menus m
                WHERE m.is_active = 1
                ORDER BY m.`order`";
        } else {
            $sql = "SELECT m.*, p.view, p.add, p.edit, p.delete
                FROM menus m
                LEFT JOIN permissions p 
                    ON p.menu_id = m.id 
                    AND p.role_id = {$roleId}
                WHERE m.is_active = 1
                ORDER BY m.`order`";
        }

        return $this->FetchAllAssoc($sql);
    }

    public function SetRolePermissions($values)
    {
        if (empty($values)) {
            return false;
        }

        $sql = "INSERT INTO `permissions` (`role_id`, `menu_id`, `view`, `add`, `edit`, `delete`) VALUES " . implode(", ", $values);

        return $this->ExecuteStatement($sql);
    }

    public function DeleteRolePermissions($role_id)
    {
        return $this->ExecuteStatement("DELETE FROM `permissions` WHERE `role_id` = $role_id");
    }

    public function sAccess($key, $type)
    {
        if ($key == "profile") {
            return true;
        }

        $roleId = intval(User::UserRole());

        if ($roleId === 1) {
            $sql = "SELECT 1 AS `view`, 1 AS `add`, 1 AS `edit`, 1 AS `delete`";
        } else {
            $sql = "SELECT p.view, p.add, p.edit, p.delete
                FROM menus m
                JOIN permissions p ON p.menu_id = m.id
                WHERE p.role_id = $roleId AND m.key = '$key' LIMIT 1";
        }

        $perm = $this->FetchOneAssoc($sql);

        if (!$perm) {
            return false;
        }

        switch ($type) {
            case 'view':
                return (bool) $perm['view'];
            case 'add':
                return (bool) $perm['add'];
            case 'edit':
                return (bool) $perm['edit'];
            case 'delete':
                return (bool) $perm['delete'];
            default:
                return false;
        }
    }

    public function RenderMenu($menus = null, $parent_id = 0)
    {
        $menus = $menus ?? $this->GetMenuPermissions();

        $html = '';
        foreach ($menus as $menu) {
            if ($menu['parent_id'] == $parent_id && $menu['view']) {
                $children = array_filter($menus, fn($m) => $m['parent_id'] == $menu['id']);
                if ($children) {
                    $html .= '<li class="nav-item dropdown">';
                    $html .= '<a class="nav-link dropdown-toggle text-uppercase" data-bs-toggle="dropdown" href="#">' . $menu['name'] . '</a>';
                    $html .= '<ul class="dropdown-menu">';
                    $html .= $this->RenderMenu($menus, $menu['id']);
                    $html .= '</ul></li>';
                } else {
                    $html .= '<li class="' . ($parent_id == 0 ? "nav-item" : "") . '"><a class="' . ($parent_id == 0 ? "nav-link" : "dropdown-item") . ' text-uppercase" href="' . BASE_URL . $menu['url'] . '">' . $menu['name'] . '</a></li>';
                }
            }
        }
        return $html;
    }





}