<?php 
namespace App\Models;
use CodeIgniter\Model;


class SubscriptionModel extends Model 
{
protected $table = 'subscription';
protected $primaryKey = 'sub_id';
protected $allowedFields = ['sub_id','invoice_id','scheme_id','customer_id','to_subscription','from_subscription','mode','created_on','created_by','updated_by','updated_on','business_id','status'];


public function getAllSubscribersWithNames($where)
{
    
    $builder = $this->db->table('subscription'); 
    $builder->select('subscription.*, books.title,books.short_code, books.isactive, CONCAT_WS(" ", customers.first_name, customers.last_name) as customer_name,(SELECT is_renew FROM subscription as sub WHERE subscription.sub_id = sub.is_renew LIMIT 1)as renewed');
    $builder->join('books', 'books.book_id = subscription.scheme_id');
    $builder->join('customers', 'customers.customer_id = subscription.customer_id');
    $builder->where($where);

    // $query = $this->db->getLastQuery();
    // dd($query);
    return $builder->get()->getResultArray();
}
public function getCustomerNameBySchemeName($schemeName)
{
    $builder = $this->db->table('subscription');
    $builder->select('customers.first_name, customers.last_name');
    $builder->select('customer_addresses.address_1, customer_addresses.address_2');
    $builder->join('customer_addresses', 'customer_addresses.customer_id = subscription.customer_id');
    $builder->join('customers', 'customers.customer_id = subscription.customer_id');
    $builder->join('schemes', 'schemes.scheme_id = subscription.scheme_id');
    $builder->where('schemes.scheme_name', $schemeName);

    $query = $builder->get();

    if ($query->getNumRows() > 0) {
        $row = $query->getRow();
        return $row->first_name . ' ' . $row->last_name;
    } else {
        return 'Customer not found'; // You can return a default value or handle the case where the customer is not found.
    }
}

public function getCustomerAddressesBySchemeName($schemeName)
{
    $builder = $this->db->table('subscription');
    $builder->select('CONCAT_WS(" ", customers.first_name, customers.last_name) as customer_name, customer_addresses.address_1, customer_addresses.address_2, customer_addresses.city, customer_addresses.state,customer_addresses.postal_code, customer_addresses.address_type,business.address,business.city,business.state,business.postal_code,business.country');
    $builder->join('customers', 'customers.customer_id = subscription.customer_id');
    $builder->join('customer_addresses', 'customer_addresses.customer_id = subscription.customer_id');
    $builder->join('business', 'business.business_id = subscription.business_id');
    $builder->join('books', 'books.book_id = subscription.scheme_id');
    $builder->where('books.title', $schemeName);

    $query = $builder->get();

    $addresses = [];

    if ($query->getNumRows() > 0) {
        foreach ($query->getResult() as $row) {
            $addressType = $row->address_type;
            $addresses[$addressType] = [
                'customer_name' => $row->customer_name,
                'address_1' => $row->address_1,
                'address_2' =>$row->address_2,
                'city' => $row->city,
                'state' => $row->state,
                'postal_code'=>$row->postal_code,
            ];
        }
    } else {
        $addresses = [
            'billing' => [
                'customer_name' => 'Customer not found',
                'address_line' => 'Billing Address not found',
                'city' => '',
                'state' => '',
            ],
            'shipping' => [
                'customer_name' => 'Customer not found',
                'address_line' => 'Shipping Address not found',
                'city' => '',
                'state' => '',
            ],
        ];
    }

    return $addresses;
}
public function getAllCustomerDetailsBySchemeName($schemeName)
{
    // echo "addresses - Customer";
    $currentDate = date('Y-m-d');
    $builder = $this->db->table('subscription');
    $builder->select('customers.customer_id, CONCAT(customers.first_name, " ", customers.last_name) as customer_name,customer_addresses.mobile_no, customer_addresses.address_1,customer_addresses.address_2, customer_addresses.city, customer_addresses.state, customer_addresses.postal_code,business.title as business_name,business.address as business_address,business.city as business_city,business.state as business_state,business.postal_code as business_postal_code,books.title,from_subscription,to_subscription,business.title as business_name,business.mobile_no as business_mobile_no,states.state_name','subscription.membership_id');
    $builder->join('customers', 'customers.customer_id = subscription.customer_id');
    $builder->join('customer_addresses', 'customer_addresses.customer_id = customers.customer_id AND customer_addresses.address_type = 2','left');
    $builder->join('business', 'business.business_id = subscription.business_id');
    $builder->join('books', 'books.book_id = subscription.scheme_id');
    $builder->join('states', 'states.state_short_name = customer_addresses.state');
    $builder->where('books.title', $schemeName);
    $builder->where('subscription.to_subscription >=', $currentDate); 
    // $builder->where('customer_addresses.address_type', 2); // Filter by address_type 2
    $builder->orderBy('customers.customer_id', 'ASC'); // Order by customer_id to group by customers

    // Fetch the query result
    $query = $builder->get();
    // $lastQuery = $this->db->getLastQuery();
    // $finalQueryString = $lastQuery->getQuery();
    // echo $finalQueryString;

    $customerDetails = [];

    if ($query->getNumRows() > 0) {
        $currentCustomerID = null;
        $shippingAddress = [];

        foreach ($query->getResultArray() as $row) {
            $customerID = $row['customer_id'];

            if ($currentCustomerID !== $customerID) {
                // New customer, add the previous customer's details (if any)
                if (!empty($shippingAddress)) {
                    $customerDetails[] = $shippingAddress;
                }

                // Start a new customer's details
                $shippingAddress = [
                    'customer_name' => $row['customer_name'],
                    'address_1' => $row['address_1'],
                    'address_2' => $row['address_2'],
                    'mobile_no'=>$row['mobile_no'],
                    'city' => $row['city'],
                    'state' => $row['state'],
                    'state_name' => $row['state_name'] != "" ? $row['state_name'] : $row['state'],
                    'postal_code' => $row['postal_code'],
                    'title' => $row['title'],
                    'to_subscription'=>$row['to_subscription'],
                    'company_name'=>$row['business_name'],
                    'company_mobile_no'=>$row['business_mobile_no'],
                    'company_address' => $row['business_address'],
                    'company_city' => $row['business_city'],
                    'company_state' => $row['business_state'],
                    'company_postal_code' => $row['business_postal_code'],
                    
                ];

                $currentCustomerID = $customerID;
            }
        }

        // Add the last customer's details (if any)
        if (!empty($shippingAddress)) {
            $customerDetails[] = $shippingAddress;
        }
    }

    return $customerDetails;
}

public function getAllExpiredCustomerDetailsByScheme($schemeName)
{
    $currentDate = date('Y-m-d');

    $builder = $this->db->table('subscription');
    $builder->select('customers.customer_id, CONCAT(customers.first_name, " ", customers.last_name) as customer_name, customer_addresses.mobile_no, customer_addresses.address_1, customer_addresses.address_2, customer_addresses.city, customer_addresses.state, customer_addresses.postal_code, business.title as business_name, books.title, from_subscription, to_subscription, business.mobile_no as business_mobile_no, states.state_name','subscription.membership_id');
    $builder->join('customers', 'customers.customer_id = subscription.customer_id');
    $builder->join('customer_addresses', 'customer_addresses.customer_id = customers.customer_id And customer_addresses.address_type = 2','left');
    $builder->join('business', 'business.business_id = subscription.business_id');
    $builder->join('books', 'books.book_id = subscription.scheme_id');
    $builder->join('states', 'states.state_short_name = customer_addresses.state','left');
    $builder->where('books.title', $schemeName);
    $builder->where('subscription.to_subscription <', $currentDate); // Expired subscriptions only
    // $builder->where('customer_addresses.address_type', 2); // Filter by address_type 2
    $builder->orderBy('customers.customer_id', 'ASC'); // Order by customer_id to group by customers
    $builder->groupBy('subscription.sub_id');
    // Fetch the query result
    $query = $builder->get();

    $customerDetails = [];

    if ($query->getNumRows() > 0) {
        $currentCustomerID = null;
        $shippingAddress = [];

        foreach ($query->getResultArray() as $row) {
            $customerID = $row['customer_id'];

            if ($currentCustomerID !== $customerID) {
                // New customer, add the previous customer's details (if any)
                if (!empty($shippingAddress)) {
                    $customerDetails[] = $shippingAddress;
                }

                // Start a new customer's details
                $shippingAddress = [
                    'customer_name' => $row['customer_name'],
                    'address_1' => $row['address_1'],
                    'address_2' => $row['address_2'],
                    'mobile_no' => $row['mobile_no'],
                    'city' => $row['city'],
                    'state' => $row['state'],
                    'state_name' => $row['state_name'] != "" ? $row['state_name'] : $row['state'],
                    'postal_code' => $row['postal_code'],
                    'title' => $row['title'],
                    'to_subscription' => $row['to_subscription'],
                    'company_name' => $row['business_name'],
                    'company_mobile_no' => $row['business_mobile_no'],
                ];

                $currentCustomerID = $customerID;
            }
        }

        // Add the last customer's details (if any)
        if (!empty($shippingAddress)) {
            $customerDetails[] = $shippingAddress;
        }
    }

    return  $customerDetails;
}

public function getQueryforSubscriptionDetailsBySchemeName($flag,$condition, $value,$selectedScheme){

        $builder = $this->db->table('subscription as s');
        
        $builder->select("
                CONCAT_WS(' ', c.first_name, c.last_name) as customer_name, 
                c.customer_id, c.mobile_no, c.email, DATEDIFF(s.to_subscription, CURDATE()) AS remaining_days,
                s.from_subscription, s.to_subscription, s.scheme_id, bs.title, s.membership_id,s.sub_id,bs.title, 
                i.invoice_id, i.invoice_date, i.shipping_address_id, 
                ca.customer_address_id, ca.address_1, ca.address_2, ca.city, ca.state, ca.postal_code, ca.address_type, st.state_name, 
                b.title as business_name, b.address as business_address, b.city as business_city, 
                b.state as business_state, b.postal_code as business_postal_code, b.mobile_no as business_mobile_no
        ");
        
        $builder->join('customers as c', 's.customer_id = c.customer_id', 'left');
        $builder->join('invoice as i', 's.invoice_id = i.invoice_id', 'left');
        $builder->join('customer_addresses as ca', 'ca.customer_id = c.customer_id AND i.shipping_address_id = ca.customer_address_id AND ca.address_type = 2', 'left');//sir told to map the ID 
        // $builder->join('customer_addresses as ca', 'ca.customer_id = c.customer_id and ca.address_type = 2','left');
        $builder->join('books as bs', 'bs.book_id = s.scheme_id', 'left');
        $builder->join('states as st', 'st.state_short_name = ca.state', 'left');
        $builder->join('business as b', 'b.business_id = s.business_id', 'left');
        $builder->whereIn('bs.book_id', $selectedScheme);
        if($flag){
            $builder->where($condition, $value);
        }
        $builder->orderBy('s.to_subscription', 'asc');
        $query = $builder->get();
        $result = $query->getResultArray();
        $lastQuery = $this->db->getLastQuery();
        $finalQueryString = $lastQuery->getQuery();
        // dd($result);        
        return $result;
    }
    public function getSubscriptionDetailsBySchemeName($selectedScheme, $downloadType){

        $customerDetails = [];
        $currentDate = date('Y-m-d');

        if ($downloadType === 'active') {
            $result1 = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.to_subscription >=', $currentDate,$selectedScheme);
            $result2 = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.isactive', 1,$selectedScheme);
            $result3 = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.from_subscription <=', $currentDate,$selectedScheme);
            $approvedResult = $this->getQueryforSubscriptionDetailsBySchemeName(1,'i.status','Approved',$selectedScheme);
            // Flatten the arrays for comparison
            $flattened1 = array_map('serialize', $result1);
            $flattened2 = array_map('serialize', $result2);
            $flattened3 = array_map('serialize', $result3);
            $flattened4 = array_map('serialize',$approvedResult);

            // Find common elements
            $commonElements0 = array_intersect($flattened1, $flattened2);
            $commonElements1 = array_intersect($commonElements0, $flattened3);
            $commonElements = array_intersect($commonElements1, $flattened4);

            // Unserialize to get back the original array format
            $result = array_map('unserialize', $commonElements);

        } 
        if ($downloadType === 'expired') {
            $expiredResult = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.to_subscription <', $currentDate, $selectedScheme);
            $activeResult = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.to_subscription >=', $currentDate, $selectedScheme);
            
            $activeCustomerIDs = array_column($activeResult, 'customer_id');
            $filteredExpiredResult = array_filter($expiredResult, function($row) use ($activeCustomerIDs) {
                return !in_array($row['customer_id'], $activeCustomerIDs);
            });
            
            $result = $filteredExpiredResult;
        } 
        if ($downloadType === 'both') {
            $result0 = $this->getQueryforSubscriptionDetailsBySchemeName(0,'','',$selectedScheme);
            $result1 = $this->getQueryforSubscriptionDetailsBySchemeName(1,'s.from_subscription <=', $currentDate,$selectedScheme);
            $approvedResult = $this->getQueryforSubscriptionDetailsBySchemeName(1,'i.status','Approved',$selectedScheme);


            $flattened1 = array_map('serialize', $result0);
            $flattened2 = array_map('serialize', $result1);
            $flattened3 = array_map('serialize', $approvedResult);

            $commonElements0 = array_intersect($flattened1, $flattened2);            
            $commonElements = array_intersect($commonElements0, $flattened3); 

            $result = array_map('unserialize', $commonElements);

        //  dd($this->db->getLastQuery());    
        }
        $customerDetails = [];
        $processedCustomerIDs = [];
        $subscriptionIDs = [];
        
    
        // Process the results if not empty
        if (!empty($result)) {
            foreach ($result as $row) {
             // Check if the customer ID has already been processed
             //  if (!in_array($row['customer_id'], $processedCustomerIDs)) { } // previous if condition
             
            if (!in_array($row['sub_id'], $subscriptionIDs)) { 
                 $customerDetails[] = [
                     'customer_id' => $row['customer_id'],
                     'invoice_id' => $row['invoice_id'],
                     'invoice_date' => $row['invoice_date'],
                     'customer_name' => $row['customer_name'],
                     'membership_id' =>$row['membership_id'],
                     'from_subscription' => $row['from_subscription'],
                     'address_1' => $row['address_1'],
                     'address_2' => $row['address_2'],
                     'mobile_no' => $row['mobile_no'],
                     'email' => $row['email'],
                     'city' => $row['city'],
                     'state' => $row['state'],
                     'state_name' => $row['state_name'] != "" ? $row['state_name'] : $row['state'],
                     'postal_code' => $row['postal_code'],
                     'title' => $row['title'],
                     'to_subscription' => $row['to_subscription'],
                     'company_name' => $row['business_name'],
                     'company_mobile_no' => $row['business_mobile_no'],
                     'company_address' => $row['business_address'],
                     'company_city' => $row['business_city'],
                     'company_state' => $row['business_state'],
                     'company_postal_code' => $row['business_postal_code'],
                     'membership_id' => $row['membership_id']
                 ];
 
                 // Add the customer ID to the set of processed IDs
                 $processedCustomerIDs[] = $row['customer_id'];
            }
         }
 
         usort($customerDetails, function($a, $b) {
             return strtotime($a['invoice_date']) - strtotime($b['invoice_date']);
         });
     }
    //  echo $downloadType." == > ".count($customerDetails);die;
     return $customerDetails;
 }
    
 
 public function getBooksByCategory($categoryName)
 {
    //  $builder = $this->db->table('books');
    //  $builder->select('books.book_id, books.title');
    //  $builder->join('book_categories', 'books.book_id = book_categories.book_id', 'left');
    //  $builder->join('category', 'category.id = book_categories.category_id', 'left');
    //  $builder->where('LOWER(category.name)', strtolower($categoryName));
     
     $builder = $this->db->table('books');
    //  $builder->select('GROUP_CONCAT(books.book_id ORDER BY books.book_id) AS book_ids');
     $builder->select('GROUP_CONCAT(DISTINCT books.book_id ORDER BY books.book_id) AS book_ids');
     $builder->select('books.short_code, books.title');
     $builder->join('book_categories', 'books.book_id = book_categories.book_id', 'left');
     $builder->join('category', 'category.id = book_categories.category_id', 'left');
     $builder->join('invoiceitems', 'books.book_id = invoiceitems.product', 'left');
     $builder->where('LOWER(category.name)', strtolower($categoryName));
     $builder->where('invoiceitems.product IS NOT NULL', null, false);
     $builder->groupBy('books.short_code');

     $query = $builder->get();
     return $query->getResult();
 }
 public function get_subscription_details_by_membership_id($membership_id)
{
    $builder = $this->db->table('subscription');
    $builder->select('subscription.from_subscription, subscription.to_subscription, subscription.scheme_id,books.price ,books.short_code');
    $builder->join('books', 'subscription.scheme_id = books.book_id');
    $builder->where('subscription.membership_id', $membership_id);
    $builder->where('subscription.status',1);
    $result = $builder->get()->getRowArray(); 

    return $result;
}
    public function check_existing_membership_id($customer_id){
        $builder = $this->db->table('subscription');
        $builder->select('membership_id');
        $builder->where('customer_id', $customer_id);
        $builder->where('isactive',1);
        $builder->orderBy('sub_id', 'DESC'); 
        $builder->limit(1);
        $output = $builder->get()->getResult();
        return $output;
    }

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


}