<?php

namespace App\Models;

use App\Controllers\Books;
use CodeIgniter\Model;


class InvoiceModel extends Model
{
    protected $table = 'invoice';
    protected $primaryKey = 'invoice_id';
    // protected $allowedFields = ['invoice_id','invoice_number','customer_id','invoice_date','event_id','business_id','created_on','business_id'];
    protected $allowedFields = ['invoice_id', 'invoice_number', 'customer_id', 'notes', 'invoice_date', 'due_date', 'subtotal', 'tax', 'dis_type', 'discount', 'shipping_charge', 'shipping_label', 'total_amount', 'payment_status', 'order_number', 'invoice_type', 'payment_method', 'event_id', 'business_id', 'shipping_address_id', 'billing_address_id', 'created_on', 'created_by', 'updated_on', 'updated_by', 'category', 'isactive', 'billing_address', 'shipping_address', 'status', 'payment_note', 'exact_total_amount'];

    public function saveInvoiceItemDetails($data)
    {
        $i = 0;
        $statement = [];
        foreach ($data as $row) {
            $id = isset($row['invoice_child_id']) ? $row['invoice_child_id'] : '';
            if ($id != '') {
                unset($row['invoice_child_id']); // Remove the id from the data to avoid updating it
                unset($row['created_by']); // bcoz here data are Updating here.

                $this->db->table('invoiceitems')->where('invoice_child_id', $id)->update($row); // Update the row with the specified id
                $affectedRows = $this->db->affectedRows();
                $statement[$i] = "Invoice Item - " . $id . " " . $affectedRows ? " Updated" : " Not Updated";
            } else {
                if (!empty($row['updated_by'])) {
                    unset($row['updated_by']); // bcoz here data are inserting here.
                }
                $this->db->table('invoiceitems')->insert($row);
                $insertID = $this->db->insertID();
                $statement[$i] = "Invoice Item - " . $insertID . " Inserted";
            }
        }
        return $statement;
    }


    public function inactiveMissingInvoiceItemDetails($where, $missingValues)
    {
        $dataToUpdate = ['isactive' => 0];
        $this->db->table('invoiceitems')->where($where)->whereIn('invoice_child_id', $missingValues)->update($dataToUpdate);
    }

    public function deleteMissingInvoiceItemDetails($where, $missingValues)
    {
        $this->db->table('invoiceitems')
            ->where($where)
            ->whereIn('invoice_child_id', $missingValues)
            ->delete();
    }

    public function updateData($table, $data, $where)
    {
        $this->db->table($table)->update($data, $where);
        $affected_rows = $this->db->affectedRows();
        return $affected_rows;
    }

    public function InactiveSubscriptionDraftDetails($where, $update_by_id)
    {
        $result = $this->getJoinedData($where);
        $inactive_invoice_ids = array();
        foreach ($result as $item) {
            $inactive_invoice_ids[] = $item['invoice_id'];
        }

        if (count($inactive_invoice_ids) > 0) {
            $this->db->table('subscription')
                ->whereIn('invoice_id', $inactive_invoice_ids)
                ->set('isactive', 0)
                ->set('updated_by', $update_by_id)
                ->update();
        }

        return $inactive_invoice_ids;
    }

    public function deleteSubscriptionDraftDetails($where, $update_by_id)
    {
        $result = $this->getJoinedData($where);
        $delete_invoice_ids = array();
        foreach ($result as $item) {
            $delete_invoice_ids[] = $item['invoice_id'];
        }
        if (count($delete_invoice_ids) > 0) {
            $this->db->table('subscription')
                ->whereIn('invoice_id', $delete_invoice_ids)
                ->delete();
        }
        return $delete_invoice_ids;
    }

    ## check the customer the scheme already Exist 
    public function existsSubscriptionDetails($where)
    {
        $result = $this->getJoinedData($where);
        if (count($result) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    public function getSubscriptionInvoiceDetail($where)
    {
        $result = $this->getJoinedData($where);
        // print_r($result);die;
        if (!empty($result)) {
            foreach ($result as $i => $item) {
                $invoiceId = $item['invoice_id'];
                $schemes = [];
                $invoiceItems = $this->getInvoiceItems($invoiceId, '');
                foreach ($invoiceItems as $j => $child) {
                    if (strtolower($child->category_name) == strtolower('Membership')) {
                        $schemes[] = [
                            'scheme_name' => $child->title,
                            'scheme_code' => !empty($child->short_code) ? $child->short_code : $child->title,
                        ];
                    }
                }
                $result[$i]['scheme_name'] = !empty($schemes) ? implode(", ", array_column($schemes, 'scheme_name')) : '';
                $result[$i]['scheme_code'] = !empty($schemes) ? implode(", ", array_column($schemes, 'scheme_code')) : '';

                $result[$i]['invoice_items_details'] = $invoiceItems;
            }
        }
        return $result;
    }

    public function getJoinedData($where, $orderby = [])
    {
        // dd($where);
        $query = $this->db->table($this->table . ' as I')
            ->join('customers as C', 'C.customer_id = I.customer_id', 'left')
            ->join('subscription as S', 'S.invoice_id = I.invoice_id', 'left')
            ->join('events as E', 'E.event_id = I.event_id', 'left')
            ->join('business as B', 'B.business_id = I.business_id', 'left')
            ->join('users as U1', 'U1.user_id = I.created_by', 'left')
            ->join('users as U2', 'U2.user_id = I.updated_by', 'left')
            ->select('I.invoice_id,I.wp_api_order_id,I.invoice_number, I.customer_id,CONCAT_WS(" ", C.first_name, C.last_name) as customer_name ,C.mobile_no, I.invoice_date, I.due_date, I.subtotal, I.tax, I.discount, I.total_amount,I.status, I.payment_status, I.order_number, I.payment_method,I.invoice_type, I.event_id,E.event_name, I.business_id,B.title as business_name,DATE_FORMAT(I.created_on, "%d/%m/%Y") as created_on_format,I.created_on, I.created_by,CONCAT_WS(" ", U1.first_name, U1.last_name) as created_by_name,DATE_FORMAT(I.updated_on, "%d/%m/%Y") as updated_on_format, I.updated_by,CONCAT_WS(" ", U2.first_name, U2.last_name) as updated_by_name, I.isactive,C.email as customer_email,C.mobile_no as customer_mobile,I.shipping_address_id,I.shipping_address,I.billing_address_id,I.billing_address,B.address as business_address,B.city as business_city,B.state as business_state,B.postal_code as business_postal_code,B.email as business_email,B.mobile_no as business_mobile_no,S.from_subscription,S.to_subscription,DATEDIFF(S.to_subscription, S.from_subscription) as subscription_in_days,S.scheme_id,(SELECT is_renew FROM subscription as sub WHERE S.sub_id = sub.is_renew LIMIT 1) as renewed')
            ->where($where);
        // if (!empty($orderby)) {
        //     $query->orderBy($orderby[0], $orderby[1]);
        // } else {
        //     $query->orderBy('invoice_id', 'DESC');
        // }
        $query->orderBy('I.invoice_date', 'DESC');
        $query->orderBy('I.invoice_id', 'DESC');
        // $query->groupBy('I.invoice_id');
        $resultArray = $query->get()->getResultArray();


        // Print the last executed query
        //echo $this->db->getLastQuery();die;

        return $resultArray;
    }


    ## subscription_inactive
    public function subscription_inactive()
    {
        $now = date('Y-m-d');
        $result = $this->db->table('subscription as S')
            ->select('S.sub_id, S.scheme_id, S.customer_id, S.business_id,S.invoice_id,S.mode,S.from_subscription, S.to_subscription, S.is_renew, S.isactive')
            ->where("S.isactive", 1)
            ->where("S.to_subscription < ", $now)
            ->get()
            ->getResultArray();
        // echo "<pre>";print_r($result);echo "</pre>";die;
        $returnMessages = [];
        $i = 0;
        $j = 0;
        if (!empty($result)) {
            foreach ($result as $row) {
                // $this->db->table('invoiceitems')->where(['invoice_id' => $row['invoice_id']])->update(['from_subscription' => NULL,'to_subscription' => NULL]);//first child
                $this->db->table('subscription')->where(['sub_id' => $row['sub_id']])->update(['isactive' => 0]); //second child
                $affectedRows = $this->db->affectedRows();
                if ($affectedRows) {
                    $returnMessages['message'] = "Success for sub_id: " . $row['sub_id'];
                    $returnMessages['success_rating'] = $i++;
                } else {
                    $returnMessages['message'] = 'Failed to update subscription with sub_id ' . $row['sub_id'] . '. No rows were affected.';
                    $returnMessages['error_rating'] = $j++;
                }
            }
            return $returnMessages;
            // return implode("\n", $returnMessages);
        } else {
            return "No data";
        }
    }

    public function getDetailForApproveNotifications($where)
    {
        $result = $this->getJoinedData($where);
        if (!empty($result)) {
            foreach ($result as $object) {
                $invoiceId = $object['invoice_id'];
                $items = $this->getInvoiceItems($invoiceId, '');
            }
        } else {
            $items = [];
        }
        $data['invoice'] = $result;
        $data['item'] = $items;
        return $data;
    }

    public function getData($table, $where = null)
    {
        $query = $this->db->table($table);
        if ($where) {
            $query->where($where);
        }
        return $query->get()->getResult();
    }

    // public function getInvoiceData($id)
    // {
    //     // Fetch invoice data
    //     return $this->db->table('invoice')
    //     ->where('invoice_id', $id)
    //     ->join('customers as C','C.customer_id= invoice.customer_id','left')
    //     ->join('business as B','B.business_id = invoice.business_id','left')
    //     ->join('customer_addresses as CA', 'CA.customer_id = invoice.customer_id AND CA.customer_address_id = invoice.shipping_address_id', 'left')
    //     ->select('invoice.*,DATE_FORMAT(invoice.invoice_date, "%d/%m/%Y") AS formatted_invoice_date,DATE_FORMAT(invoice.due_date, "%d/%m/%Y") AS formatted_due_date ,concat(C.first_name," ",C.last_name) as customer_name,C.mobile_no as customer_mobile')
    //     ->select('B.title as company_name,B.business_logo,B.terms as company_terms,B.address as company_address,B.city as company_city,B.state as company_state,B.postal_code as company_postal_code,B.email as company_email,B.mobile_no as company_mobile_no')
    //     // ->select('COALESCE(NULLIF(states.state_name, ""), A.state) AS customer_bill_state')
    //     // ->select(' customer_bill_country')
    //     ->get()
    //     ->getResult();

    // }
    public function getInvoiceData($id)
    {
        // return $this->db->table('invoice')
        // ->where('invoice_id', $id)
        // ->join('customer_addresses as A','A.customer_address_id=invoice.shipping_address_id ','left')
        // ->select('invoice.*,A.*')
        // ->get()
        // ->getResult();

        // Fetch invoice data
        return $this->db->table('invoice')
            ->where('invoice_id', $id)
            ->join('customers as C', 'C.customer_id= invoice.customer_id', 'left')
            ->join('business as B', 'B.business_id = invoice.business_id', 'left')
            ->join('customer_addresses as A', 'A.customer_address_id=invoice.billing_address_id AND A.address_type = 1', 'left')
            ->join('customer_addresses as S', 'S.customer_address_id=invoice.shipping_address_id AND S.address_type = 2', 'left')
            ->join('states', 'states.state_short_name = A.state AND A.country = "IN"', 'left')
            ->join('countries', 'countries.country_short_name = A.country', 'left')
            ->select('invoice.*,DATE_FORMAT(invoice.invoice_date, "%d/%m/%Y") AS formatted_invoice_date,DATE_FORMAT(invoice.due_date, "%d/%m/%Y") AS formatted_due_date ,CONCAT_WS(" ", C.first_name, C.last_name) as customer_name,C.mobile_no,C.email,A.address_1, A.address_2,A.postal_code,A.city, A.state,C.mobile_no as customer_mobile')
            ->select('B.title as company_name,B.business_logo,B.terms as company_terms,B.address as company_address,B.city as company_city,B.state as company_state,B.postal_code as company_postal_code,B.email as company_email,B.mobile_no as company_mobile_no')
            ->select('COALESCE(NULLIF(states.state_name, ""), A.state) AS customer_bill_state')
            ->select('COALESCE(NULLIF(states.state_name, ""), S.state) AS customer_ship_state')
            ->select('CONCAT_WS(" ", A.first_name, A.last_name) as customer_biller_name,concat(A.address_1," ", A.address_2) as customer_bill_address,A.postal_code as customer_bill_postal_code ,A.city as customer_bill_city, countries.country_name as customer_bill_country,A.country as bcountry,A.state as bstate,A.customer_address_id as baddr_id,A.email as bemail,A.mobile_no as bmobile')
            ->select('CONCAT_WS(" ", S.first_name, S.last_name) as customer_shipper_name,concat(S.address_1," ", S.address_2) as customer_ship_address,S.postal_code as customer_ship_postal_code ,S.city as customer_ship_city, countries.country_name as customer_ship_country,S.country as scountry,S.state as sstate,S.customer_address_id as saddr_id,S.email as semail,S.mobile_no as smobile')
            ->get()
            ->getResult();
    }
    // this function Also Used For Approve Notification.
    public function getInvoiceItems($id, $stringflag)
    {

        if ($stringflag == 'groupby') {
            $select = ' B.*, GROUP_CONCAT(C.name SEPARATOR \',\') as name,invoiceitems.*';
            $group_by = 'invoiceitems.invoice_child_id';
        } else {
            $select = 'invoiceitems.*, B.*, C.name as category_name';
            $group_by = "";
        }

        $data = $this->db->table('invoiceitems')
            ->where(['invoice_id' => $id, 'invoiceitems.isactive' => 1])
            ->join('books as B', 'B.book_id = invoiceitems.product', 'left')
            ->join('book_categories as BC', 'BC.book_id = invoiceitems.product', 'left')
            ->join('category as C', 'C.id = BC.category_id', 'left')
            ->select($select)
            ->groupBy($group_by)  // Fixed the variable name here
            ->get()
            ->getResult();
        //->orderBy("book_img_id", "asc") // Order by book_img_id in ascending order
        //->limit(1,0) 


        // print_r($this->db->getLastQuery());die;
        // print_r($data);die("ends -- here");
        return $data;
    }


    public function getMembershipListForCustomer($category, $id)
    {
        $result = $this->db->table('subscription as S')
            ->join('customers as C', 'C.customer_id = S.customer_id', 'left')
            ->join('books as BK', 'BK.book_id = S.scheme_id', 'left')
            ->join('book_categories as BC', 'BC.book_id = S.scheme_id', 'left')
            ->join('category as CM', 'CM.id = BC.category_id', 'left')
            ->join('invoice as I', 'I.invoice_id = S.invoice_id', 'left')
            ->select('S.customer_id,BK.short_code,S.membership_id,S.status,I.status as invoice_status ,CONCAT_WS(" ", C.first_name, C.last_name) as customer_name, S.sub_id, S.scheme_id, DATE_FORMAT(S.from_subscription, "%d/%m/%Y") AS formatted_from_subscription, DATE_FORMAT(S.to_subscription, "%d/%m/%Y") AS formatted_to_subscription, S.from_subscription, S.to_subscription,S.isactive,S.is_renew, S.created_on, CM.name ,BK.title, BK.book_id,(SELECT is_renew FROM subscription as sub WHERE S.sub_id = sub.is_renew LIMIT 1)as renewed')
            ->where('LOWER(CM.name)', strtolower($category))
            ->where('S.customer_id', $id)
            ->orderBy('S.to_subscription', 'ASC')
            ->groupBy('S.sub_id')
            ->get()
            ->getResultArray();
        return $result;
    }

    public function getProductImgs($productId)
    {
        $data =  $this->db->table('book_images')
            ->where(['book_id' => $productId, 'book_images.isactive' => 1])
            // ->join('books as B', 'B.book_id = invoiceitems.product', 'left')
            // ->join('book_images as BI', 'BI.book_id = invoiceitems.product', 'left')
            ->select('book_images.*')
            //->orderBy("book_img_id", "asc") // Order by book_img_id in ascending order
            //->limit(1,0) 
            ->get()
            ->getResult();

        // print_r($this->db->getLastQuery());
        return $data;
    }


    public function getCategoryBooks($type)
    {


        // $where = ($type == 1) ? ['LOWER(category.name) !=' => strtolower('Membership')] : ['LOWER(category.name)' => strtolower('Membership')];

        // $data = $this->db->table('books')
        //                 ->select('books.book_id,books.business_id,books.wp_api_product_id,books.title,books.publication_date,books.publisher,books.author,books.genre,books.language,books.description,books.short_description,books.page_count,books.price,books.isbn_code,books.tax,books.sku,books.category,books.isactive')
        //                 ->join('book_categories', 'book_categories.book_id= books.book_id', 'left')
        //                 ->join('category','category.id=book_categories.category_id')
        //                 ->where($where)
        //                 ->groupBy('books.book_id')
        //                 ->get()
        //                 ->getResult();
        // print_r($this->db->getLastQuery());die;
        $data = $this->db->table('books')
            ->select('books.book_id,books.business_id,books.wp_api_product_id,books.title,books.publication_date,books.publisher,books.author,books.genre,books.language,books.description,books.short_description,books.page_count,books.price,books.isbn_code,books.tax,books.sku,books.category,books.isactive')
            ->join('book_categories', 'book_categories.book_id = books.book_id  and book_categories.isactive = 1', 'left')
            ->join('category', 'category.id = book_categories.category_id', 'left');

        if ($type == 1) {
            $data->where("LOWER(category.name) != 'membership' OR category.id IS NULL");
        } else {
            $data->where('LOWER(category.name)', 'membership');
        }

        $data = $data->groupBy('books.book_id')
            ->get()
            ->getResult();

        return $data;
    }

    public function insertupdateSubscriptionData($data, $invoice_status)
    {
        if (!empty($data)) {
            $invoice_id = $data['invoice_id'];
            $customer_id = $data['customer_id'];
            $scheme_id = $data['scheme_id'];
            $data['isactive'] = $invoice_status === 'Approved' ? 1 :  0;
            $where = ['invoice_id' => $invoice_id]; // invoice id refer with srinivasan // initally invoice id, scheme id and customer id
            $query = $this->db->table('subscription')->select('sub_id')->where($where)->get()->getRow();

            if (!empty($query)) {
                unset($data['created_by']);
                return $this->db->table('subscription')->where('sub_id', $query->sub_id)->update($data);
            } else {
                unset($data['updated_by']);
                return $this->db->table('subscription')->insert($data);
            }
        }
        return false;
    }

    // ***********************REPORT**********************

    public function get_general_invoice_data($f_date = null, $t_date = null)
    {
        // Fetch invoice data
        $query = $this->db->table('invoice')
            ->select('invoice.*,DATE_FORMAT(invoice.invoice_date, "%d/%m/%Y") AS formatted_invoice_date, customers.*, COUNT(invoiceitems.product) as item_count')
            ->where('invoice.isactive', 1)

            ->where('invoiceitems.isactive', 1);

        // Add date range filter if provided
        if ($f_date !== null && $t_date !== null) {
            $query->where('invoice.invoice_date >=', $f_date)
                ->where('invoice.invoice_date <=', $t_date);
        }

        $result = $query->join('customers', 'customers.customer_id = invoice.customer_id', 'left')
            ->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left')
            ->join('books', 'books.book_id = invoiceitems.product', 'left')
            ->orderBy("invoice.invoice_date", "DESC")
            ->groupBy('invoice.invoice_id') // Assuming invoice_id is the primary key of the invoice table
            ->get()
            ->getResult();

        return $result;
    }


    public function get_mem_invoice_data($f_date = null, $t_date = null)
    {
        // Fetch invoice data
        $query = $this->db->table('invoice')
            ->select('invoice.*, customers.*, COUNT(invoiceitems.product) as item_count,DATE_FORMAT(invoice.invoice_date, "%d/%m/%Y") AS formatted_invoice_date,subscription.from_subscription,subscription.to_subscription,DATE_FORMAT(subscription.from_subscription, "%d/%m/%Y") AS formatted_from_date,DATE_FORMAT(subscription.to_subscription, "%d/%m/%Y") AS formatted_to_date')
            ->join('subscription', 'subscription.invoice_id = invoice.invoice_id', 'left')
            ->where('invoice.isactive', 1)
            ->where('invoice.invoice_type', 2)
            ->where('invoiceitems.isactive', 1);

        // Add date range filter if provided
        if ($f_date !== null && $t_date !== null) {
            $query->where('invoice.invoice_date >=', $f_date)
                ->where('invoice.invoice_date <=', $t_date);
        }

        $result = $query->join('customers', 'customers.customer_id = invoice.customer_id', 'left')
            ->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left')
            ->join('books', 'books.book_id = invoiceitems.product', 'left')
            ->groupBy('invoice.invoice_id') // Assuming invoice_id is the primary key of the invoice table
            ->get()
            ->getResult();

        return $result;
    }

    // public function itemwise_report_data($f_date = null, $t_date = null)
    // {
    //     // Fetch invoice data
    //     $query = $this->db->table('invoice')
    //         ->select('invoice.*, books.* , COUNT(invoiceitems.product) as item_count , sum(invoiceitems.product * invoiceitems.unit_price) as item_cost')
    //         ->where('invoice.isactive', 1)
    //         ->where('books.isactive', 1);

    //         // Add date range filter if provided
    //         if ($f_date !== null && $t_date !== null) {
    //             $query->where('invoice.invoice_date >=', $f_date)
    //                 ->where('invoice.invoice_date <=', $t_date);
    //         }

    //         $result = $query->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left')
    //         ->join('books', 'books.book_id = invoiceitems.product', 'left')
    //         ->groupBy('books.book_id') 
    //         ->get()
    //         ->getResult();

    //         return $result;
    // }
    public function itemwise_report_data($f_date = null, $t_date = null)
    {

        // Fetch invoice data
        $query = $this->db->table('invoice')
            ->select('books.publishers_code, COUNT(invoiceitems.product) as publisher_item_count, books.title as book_name, COUNT(invoiceitems.product) as item_count, SUM(invoiceitems.quantity * invoiceitems.unit_price) as total_cost ,DATE_FORMAT(books.publication_date, "%d/%m/%Y") AS book_publication_date');

        $query->where('invoice.isactive', 1)
            ->where('invoice.invoice_type', 1)
            ->where('books.isactive', 1);

        // Add date range filter if provided
        if ($f_date !== null && $t_date !== null) {
            $newf_date = date("Y-m-d", strtotime($f_date));
            $newt_date = date("Y-m-d", strtotime($t_date));
            $query->where('books.publication_date >=', $newf_date)
                ->where('books.publication_date <=', $newt_date);
        }


        $result = $query->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left')
            ->join('books', 'books.book_id = invoiceitems.product', 'left')
            ->groupBy('books.publishers_code, books.title')
            ->get()
            ->getResult();
        //   print_r($result);

        // // Group by publisher_code and include books
        // var_dump($result);
        // die();
        $groupedResult = [];
        foreach ($result as $row) {
            $publisherCode = $row->publishers_code;
            if (!isset($groupedResult[$publisherCode])) {
                $groupedResult[$publisherCode] = (object)[
                    'publisher_code' => $publisherCode,
                    'publisher_item_count' => 0,
                    'publisher_total_cost' => 0,
                    'books' => [],
                    'book_name' => '',

                ];
            }

            // Add book information to the grouped result
            $groupedResult[$publisherCode]->publisher_item_count += $row->item_count;
            $groupedResult[$publisherCode]->publisher_total_cost += $row->total_cost;
            $groupedResult[$publisherCode]->books[] = (object)[
                'book_name' => $row->book_name,
                'item_count' => $row->item_count,
                'total_cost' => $row->total_cost,
                'book_publication_date' => $row->book_publication_date,
            ];
        }

        return array_values($groupedResult);
    }


    public function getInvoiceIdByMd5($md5Hash)
    {
        $result = $this->db->table($this->table)
            ->select('invoice_id')
            ->get()
            ->getResult();

        foreach ($result as $row) {
            if (md5($row->invoice_id) === $md5Hash) {
                return $row->invoice_id;
            }
        }

        return null;
    }
    // public function getExpiredCustomers($f_date = null, $t_date = null)
    // {
    //     $now = date('Y-m-d');
    //     $futureDate = date('Y-m-d', strtotime($now . ' +30 days'));

    //     $query = $this->db->table('subscription as S'); // Define $query here

    //     if ($f_date !== null && $t_date !== null) {
    //         $query->where('S.to_subscription >=', $f_date)
    //               ->where('S.to_subscription <=', $t_date);
    //     }

    //     $result = $query
    //         ->select('S.customer_id,S.sub_id, S.from_subscription, S.to_subscription, C.first_name, C.last_name, C.email, C.mobile_no, S.scheme_id,B.short_code')
    //         ->join('customers as C', 'C.customer_id = S.customer_id', 'left')
    //         ->join('books as B', 'B.book_id = S.scheme_id', 'left')
    //         ->where('S.isactive', 1)
    //         ->where('S.to_subscription <', $futureDate)
    //         ->get()
    //         ->getResultArray();
    //         // print_r($result);
    //         // echo "<pre>";
    //         // echo $this->db->getLastQuery();
    //         // echo "</pre>";die;
    //         echo $this->db->getLastQuery();
    //         die();

    //     return $result;

    // }
    public function getExpiredCustomers($f_date = null, $t_date = null)
    {
        // Set timezone for accurate date calculation
        date_default_timezone_set('Asia/Kolkata');

        // Calculate the date 30 days from now
        $futureDate = date('Y-m-d', strtotime('+30 days'));

        $query = $this->db->table('subscription as S');

        if (!empty($f_date) && !empty($t_date)) {
            $query->where('S.to_subscription >=', $f_date)
                ->where('S.to_subscription <=', $t_date);
        } else {
            $query->where('S.to_subscription >=', date('Y-m-d'))
                ->where('S.to_subscription <=', $futureDate);
        }

        $subquery = $this->db->table('subscription as S2')
            ->select('S.sub_id')
            ->where('S2.customer_id = S.customer_id')
            ->where('S.sub_id = S2.is_renew');

        $result = $query
            ->select('S.customer_id, S.sub_id, S.from_subscription, S.to_subscription, 
              C.first_name, C.last_name, C.email, C.mobile_no, 
              S.scheme_id, B.short_code, S.membership_id')
            ->join('customers as C', 'C.customer_id = S.customer_id', 'left')
            ->join('books as B', 'B.book_id = S.scheme_id', 'left')
            ->where('S.isactive', 1)
            ->whereNotIn('S.sub_id', $subquery)
            ->orderBy('S.to_subscription', 'ASC')
            ->get();


        // Debugging: output the generated SQL query
        // echo $this->db->getLastQuery();
        // die();

        // Fetch results as an associative array
        return $result->getResultArray();
    }
    public function getActiveMembers()
    {
        $result =
            $this->db->table('subscription as S')
            ->select('S.customer_id, S.sub_id, S.from_subscription, S.to_subscription, 
              C.first_name, C.last_name, C.email, C.mobile_no, 
              S.scheme_id, B.short_code, S.membership_id')
            ->join('customers as C', 'C.customer_id = S.customer_id', 'left')
            ->join('books as B', 'B.book_id = S.scheme_id', 'left')
            ->where('S.isactive', 1)
            ->where('S.to_subscription>now()')
            ->orderBy('S.to_subscription', 'ASC')
            ->get();
        return $result->getResultArray();
    }

    public function itemwise_report_data_with_publish_code($f_date = null, $t_date = null)
    {
        // Fetch invoice data
        $query = $this->db->table('invoice')
            ->select('invoice.*, books.* , COUNT(invoiceitems.product) as item_count , sum(invoiceitems.product * invoiceitems.unit_price) as item_cost')
            ->where('invoice.isactive', 1)
            ->where('books.isactive', 1)
            ->where('books.publishers_code', 'VJB'); // Additional condition for publish_code

        // Add date range filter if provided
        if ($f_date !== null && $t_date !== null) {
            $query->where('invoice.invoice_date >=', $f_date)
                ->where('invoice.invoice_date <=', $t_date);
        }

        $result = $query->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left')
            ->join('books', 'books.book_id = invoiceitems.product', 'left')
            ->groupBy('books.book_id')
            ->get()
            ->getResult();
        // print_r($result);die;

        return $result;
    }

    public function userwise_eventwise_report($f_date = null, $t_date = null) {
        $builder = $this->db->table('invoice');
    
        $builder->select([
            'users.user_id',
            'users.first_name', 
            'users.role',
            'events.event_name', 
            'COUNT(invoice.created_by) AS books_sold', 
            'ABS(SUM(invoice.exact_total_amount)) AS total_amount',
            'invoice.payment_method',
            // 'DATE(invoice.invoice_date) AS invoice_date' // Convert to DATE
            'invoice.invoice_date'
        ]);
    
        $builder->join('users', 'users.user_id = invoice.created_by');
        $builder->join('events', 'events.event_id = invoice.event_id');
        $builder->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id');
        $builder->join('books', 'books.book_id = invoiceitems.product');
    
        $builder->where('invoice.event_id <>', 0);
    
        if ($f_date !== null && $t_date !== null) {
            $builder->where('invoice.invoice_date >=', $f_date)
                    ->where('invoice.invoice_date <=', $t_date);
        }
    
        $builder->groupBy([
            'invoice_date',
            'users.user_id',
            'events.event_name'
        ]);
    
        // Order by the converted date
        $builder->orderBy('invoice_date', 'DESC');
    
        $query = $builder->get();
        
        $result =  $query->getResultArray();
        // dd($result);
        return $result;
    }
    

    public function getPaymentReport($f_date = null, $t_date = null)
    {

        $builder = $this->db->table('invoice');
        $builder->select('invoice.invoice_number, customers.first_name, invoice.total_amount, invoice.payment_status, invoice.payment_method, invoice.invoice_date');
        $builder->join('customers', 'invoice.customer_id = customers.customer_id', 'inner');
        if ($f_date !== null && $t_date !== null) {
            $builder->where('invoice.invoice_date >=', $f_date)
                ->where('invoice.invoice_date <=', $t_date);
        }
        $builder->where('invoice.status', 'Approved');
        $query = $builder->get();
        $result1 = $query->getResultArray();
        $builder->select('payment_method, SUM(exact_total_amount) as total_amount');
        if ($f_date !== null && $t_date !== null) {
            $builder->where('invoice.invoice_date >=', $f_date)
                ->where('invoice.invoice_date <=', $t_date);
        }
        $builder->groupBy('payment_method');
        $query2 = $builder->get();
        $paymentMethodTotals = $query2->getResultArray();
        $paymentMethodMap = [];
        foreach ($paymentMethodTotals as $row) {
            $paymentMethodMap[$row['payment_method']] = $row['total_amount'];
        }
        $result2 = $paymentMethodMap;
        //     log_message('info',json_encode($results));
        //    dd($results); 
        $results = [
            'result1' => $result1,
            'result2' => $result2
        ];
        return $results;
    }
    public function itemwise_report_data_with_payment_method($f_date = null, $t_date = null)
    {
        $builder = $this->db->table('invoice');
        $builder->select('books.publishers_code, 
                    COUNT(invoiceitems.product) AS publisher_item_count, 
                    books.title AS book_name, 
                    COUNT(invoiceitems.product) AS item_count,
                    SUM(invoiceitems.quantity * invoiceitems.unit_price) AS total_cost, 
                    invoice.payment_method, 
                    DATE_FORMAT(invoice.invoice_date, "%d-%m-%Y") AS invoice_date');
        $builder->join('invoiceitems', 'invoiceitems.invoice_id = invoice.invoice_id', 'left');
        $builder->join('books', 'books.book_id = invoiceitems.product', 'left');
        $builder->where('invoice.isactive', 1);
        $builder->where('invoice.invoice_type', 1);
        $builder->where('books.isactive', 1);
        if ($f_date !== null && $t_date !== null) {
            $builder->where('invoice_date >=', $f_date)
                ->where('invoice_date <=', $t_date);
        }
        $builder->groupBy(['books.publishers_code', 'books.title', 'invoice.payment_method']);

        $query = $builder->get();
        $results = $query->getResultArray();

        $groupedResult = [];
        foreach ($results as $row) {
            // Use a unique key for each combination of publisher and payment method
            $publisherCode = $row['publishers_code'];
            $paymentMethod = $row['payment_method'];
            $uniqueKey = $publisherCode . '-' . $paymentMethod; // Unique key for grouping

            // Initialize the group if it does not exist
            if (!isset($groupedResult[$uniqueKey])) {
                $groupedResult[$uniqueKey] = (object)[
                    'publisher_code' => $publisherCode,
                    'publisher_item_count' => 0,
                    'publisher_total_cost' => 0,
                    'books' => [],
                    'payment_method' => $paymentMethod,
                ];
            }

            // Update the group’s cumulative values
            $groupedResult[$uniqueKey]->publisher_item_count += $row['publisher_item_count'];
            $groupedResult[$uniqueKey]->publisher_total_cost += $row['total_cost'];

            // Add the book-specific information to the books array
            $groupedResult[$uniqueKey]->books[] = (object)[
                'book_name' => $row['book_name'],
                'item_count' => $row['item_count'],
                'total_cost' => $row['total_cost'],
                'invoice_date' => $row['invoice_date'],
            ];
        }

        return array_values($groupedResult);
    }


    public function updateInvoiceStatus($invoiceId, $voidReason)
    {
        // Assuming 'invoices' is the name of your table
        $builder = $this->db->table('invoice');

        // Define the data to be updated
        $data = [
            'status' => 'Void', // Set the status to 'Void'
            'reason' => $voidReason, // Set the void reason
            // You can add more fields to update here if needed
        ];

        // Set the where condition to identify the invoice by its ID
        $builder->where('invoice_id', $invoiceId);

        // Perform the update and check if successful
        $updated = $builder->update($data);

        return $updated;
    }
    public function updateInvoiceCancelStatus($invoiceIds, $cancelReason)
    {
        // Assuming 'invoices' is the name of your table
        $builder = $this->db->table('invoice');

        // Define the data to be updated
        $data = [
            'status' => 'Cancelled', // Set the status to 'Canceled'
            'reason' => $cancelReason, // Set the cancel reason
            // You can add more fields to update here if needed
        ];

        // Set the where condition to identify the invoices by their IDs
        $builder->where('invoice_id', $invoiceIds);

        // Perform the update and check if successful
        $updated = $builder->update($data);

        return $updated;
    }
    public function getActiveSchemes()
    {
        $builder = $this->db->table($this->table . ' as I');
        $builder->select('books.short_code');
        $builder->join('subscription', 'subscription.invoice_id = I.invoice_id');
        $builder->join('invoiceitems', 'invoiceitems.invoice_id = I.invoice_id');
        $builder->join('books', 'invoiceitems.product = books.book_id');
        $builder->groupBy('short_code');

        $query = $builder->get();
        $results = $query->getResultArray();

        return $results;
    }
}