<?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)
    // {
    //     $invFrm = date('y') . date('m');

    //     $sql = "SELECT invoice FROM $tableName WHERE id IN ( SELECT MAX(id) FROM $tableName WHERE invoice != 0)";

    //     $result = $this->conn->query($sql);

    //     if ($result->num_rows == 1) {
    //         $row = $result->fetch_assoc();
    //         $lastInv = $row['invoice'];
    //         if ($lastInv != '' || $lastInv != null || $lastInv != 0) {
    //             $invFiext = substr($lastInv, 0, 4);
    //             $lastNum = (int) substr($lastInv, strlen($invFrm));
    //             $nextNum = $lastNum + 1;
    //             if ($invFrm == $invFiext) {
    //                 return $invFiext . $nextNum;
    //             } else {
    //                 return $invFrm . '1';
    //             }
    //         } else {
    //             return $invFrm . '1';
    //         }
    //     } else {
    //         return $invFrm . '1';
    //     }
    // }


    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;
                return $invFrm . $nextNum;
            }
        }

        return $invFrm . '1';
    }

    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);
        $desigCode = $user['desig'];
        $status = $user['status'];

        $sql = "INSERT INTO `users` (`id`,`username`, `password`, `full_name`, `desig_code`, `status`) ";
        $sql .= "VALUES (NULL,'$userName','$pass','$fullName','$desigCode','$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);
        $desigCode = $user['desig'];
        $status = $user['status'];

        if ($id > 0 || $id != '') {
            $sql = "UPDATE `users` SET `full_name`='$fullName', `desig_code`='$desigCode', `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);
    }

    public function UserList()
    {
        $sql = "SELECT * FROM `users`";

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

    public function GetUserByUserName($userName)
    {
        $sql = "SELECT * FROM users WHERE username='$userName'";

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

    public function GetUserById($id)
    {
        $sql = "SELECT * FROM users WHERE id= $id";

        return $this->FetchOneAssoc($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 = $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);

        $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',
        ];

        //CAST(CONCAT('Payment ($InvoicePrefix', ep.invoice, CASE WHEN ep.remarks='' THEN '' ELSE ' / ' END, ep.remarks, ')') AS VARCHAR(255)) AS remarks, 

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

    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";
        } else if ($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);
    }

    private function ExportTableToSQL($tableName)
    {
        try {
            $sql = "";

            if ($tableName === 'v') {
                $result = $this->conn->query("SHOW CREATE VIEW `vw_event`");
                $row = $result->fetch_assoc();
                $sql .= $row['Create View'] . ";\n\n";
            } else {
                $result = $this->conn->query("SHOW CREATE TABLE `$tableName`");
                $row = $result->fetch_assoc();
                $sql .= $row['Create Table'] . ";\n\n";

                $result = $this->conn->query("SELECT * FROM `$tableName`");
                $columns = array();
                $values = array();

                while ($row = $result->fetch_assoc()) {
                    if (empty($columns)) {
                        $columns = array_keys($row);
                    }
                    $escapedValues = array_map([$this->conn, 'real_escape_string'], array_values($row));
                    $values[] = "('" . implode("', '", $escapedValues) . "')";
                }

                if (!empty($values)) {
                    $sql .= "INSERT INTO `$tableName` (`" . implode('`, `', $columns) . "`) VALUES " . implode(', ', $values) . ";\n";
                }
            }

            return $sql;
        } catch (Exception $exception) {
            //echo "Error exporting t! Maybe t dose't exist";
        }
    }

    public function DownloadSQL($tableName)
    {
        $sql = $this->ExportTableToSQL($tableName);

        if ($sql) {
            header('Content-Type: application/sql');
            header('Content-Disposition: attachment; filename="' . $tableName . '_' . date('y') . date('m') . date('d') . '.sql"');
            echo $sql;
            exit();
        }
    }

    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 = '';
        $name = $data['name'];
        $amt = $data['amount'];
        $status = $data['status'];

        $sql = "INSERT INTO `addons` (`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 AddOnsList($onlyActive = false)
    {
        $sql = "SELECT * FROM addons";

        if ($onlyActive) {
            $sql .= " WHERE status=1";
        }
        $sql .= " ORDER BY status DESC";

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

    public function GetAddOnsById($id)
    {
        $sql = "SELECT * FROM addons WHERE id= $id";

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

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

        if ($id > 0 || $id != '') {
            $sql = "UPDATE `addons` 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 AddEventAddOns($addOnsId, $eventId)
    {
        $addOnsIdArr = explode(',', $addOnsId);
        sort($addOnsIdArr);

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

            foreach ($addOnsIdArr as $id) {
                $addOnsData = $this->GetAddOnsById($id);

                if (!$addOnsData || !isset($addOnsData['amt'], $addOnsData['name'])) {
                    continue;
                }

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

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

            if (!empty($values)) {
                $addOnsInsertSql = "INSERT INTO event_addons (id, event_id, addons_id, addons_name, amount) VALUES " . implode(", ", $values) . ";";
                $updateEventSql = "UPDATE `event` AS e SET e.add_ons_amt = (SELECT SUM(ifnull(ea.amount, 0)) AS amount FROM event_addons AS ea WHERE ea.event_id = $eventId) WHERE e.id = $eventId;";
                try {
                    $result = $this->ExecuteStatement($addOnsInsertSql);
                    $eventUpdate = $this->ExecuteStatement($updateEventSql);
                    return $result;
                } catch (mysqli_sql_exception $e) {
                    return $e->getMessage();
                }
            }
        }
    }

    public function GetEventAddOnsById($id)
    {
        $sql = "SELECT * FROM event_addons WHERE id= $id";

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

    public function GetEventAddOnsByEventId($eventId)
    {
        $sql = "SELECT * FROM event_addons WHERE event_id = $eventId ORDER BY 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'];
        $packageName = $packageDtl['name'];

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

        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";
            }
        }

        $event_type_id = $eventTypeId;
        $package_id = '';
        $package_name = '';
        $client_name = $clientName;
        $phone = $contactNo;
        $email = $emailAddress;
        $groom = '';
        $bride = '';
        $birthday_person = '';
        $org_name = '';
        $venue = $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
        ) {
            $package_id = $packageId;
            $package_name = $packageName;
            $groom = $groomName;
            $bride = $brideName;
            $amount = $packageAmount;
        } else if ($eventTypeId == 10) { // Birthday
            $package_id = $packageId;
            $package_name = $packageName;
            $birthday_person = $birthdayPerson;
            $amount = $packageAmount;
        } else if (
            $eventTypeId == 11 ||  // Architectural
            $eventTypeId == 12     // Corporate
        ) {
            $org_name = $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
        ) {
            $song = $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 {
                $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);

        $addTime = "";

        $timeExist = $this->Exists("SELECT * FROM `event_addition_time` WHERE `event_id` = '$eventId' AND `bill_num` = '$billNumber'");
        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'");
            }
        }

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

            if ($eventDtl) {
                $event_amount = (int) $eventDtl["amount"];
                $event_total_amt = (int) $eventDtl["total_amt"];

                $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);
                $add_time = $addTime;
                $addons = "";
                $amount = $event_amount;
                $total_amt = $event_total_amt;

                $addOnsDtlList = $this->GetEventAddOnsByEventId($eventId);
                $addonsArray = [];
                if ($addOnsDtlList) {
                    foreach ($addOnsDtlList as $row) {
                        $addonsArray[] = $row['addons_name'] . '|' . $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', `addons` = '$addons', `amount` = '$amount', 
                        `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`, `addons`, `amount`, `total_amt`) 
                    VALUES (NULL, '$bill_num','$event_id', '$event_name', '$event_date_time', '$package_id','$package_name', '$package_dtl', '$duration','$add_time','$addons', '$amount', '$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);
            $billDtl = $this->GetBillDtlByBillNumber($billNumber);

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

                foreach ($billDtl as $row) {
                    $bill_amt += (int) $row["total_amt"] ?? 0;
                }

                $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'");
                        $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;
    }

    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']);
        $client_name = trim($data['clientName']);
        $purpose = trim($data['purpose']);

        if ($id > 0) {
            $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;
    }

    public function GetQuotation(bool $yearMonthGroup = false)
    {
        $sql = "SELECT * FROM quote_mst ORDER BY id DESC";
        if ($yearMonthGroup) {
            $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";
        }
        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 AddQuotationDetails($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;
        $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"] ?? '-');
        $event_type = "";

        if ($add_ons_id > 0) {
            $addOnsDtl = $this->GetAddOnsById($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) $out_amt + (int) $add_ons_amt;

        $sql = "INSERT INTO 
        quote_details (mst_id, event_date, location, package_id, package_name, package_amt, out_purp_id, out_amt, add_ons_id, add_ons_name, add_ons_amt, purpose, event_time, report_time, photog, cinemetog, event_type, duration, total) VALUES ('$mst_id', '$event_date', '$location', '$package_id', '$package_name', '$package_amt', '$out_purp_id', '$out_amt', '$add_ons_id', '$add_ons_name', '$add_ons_amt', '$purpose', '$event_time', '$report_time', '$photog', '$cinemetog', '$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', `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', `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"] ?? '-');
        $printed_photo = trim($data["othersPrintPhoto"] ?? '-');
        $photo_quality = trim($data["othersPhotoQuality"] ?? '-');
        $promo = trim($data["othersPromo"] ?? '-');
        $video = trim($data["othersVideo"] ?? '-');
        $album = trim($data["othersAlbum"] ?? '-');
        $pf_10l = trim($data["othersPhotoFrame10l"] ?? '-');
        $drone = trim($data["othersDrone"] ?? '-');

        $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', printed_photo = '$printed_photo', 
            photo_quality = '$photo_quality', promo = '$promo', video = '$video', album = '$album', 
            pf_10l = '$pf_10l', drone = '$drone' WHERE mst_id = $mst_id";
        } else {
            $sql = "INSERT INTO 
            quote_others (mst_id, edited_photo, printed_photo, photo_quality, promo, video, 
            album, pf_10l, drone) 
            VALUES ('$mst_id', '$edited_photo', '$printed_photo', '$photo_quality', '$promo', '$video', 
            '$album', '$pf_10l', '$drone')";
        }

        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;
    }
}