<?php

namespace App\Models;

use CodeIgniter\Model;


class CustomerModel extends Model
{
    protected $table = 'customers';
    protected $primaryKey = 'customer_id ';
    // protected $allowedFields = ['customer_id ','first_name','city','last_name','email','mobile_no','country','state','postal_code' ,'address','profile_picture','date_of_birth','gender','mode','isactive','business_id'];
    protected $allowedFields = ['customer_id ','first_name','city','last_name','email','mobile_no','country','state','postal_code' ,'address','profile_picture','date_of_birth','gender','mode','type','isactive','business_id','created_by','updated_by'];

    public function insertCustomer($data)
    {
    return $this->insert($data);
 }
 public function getCustomerNamesByBusiness($business_id)
 {
     $this->builder()->select('customer_id,CONCAT_WS(" ", first_name, last_name) as full_name', false);
     $this->builder()->where('business_id ', $business_id); // Filter by business_id
     $query = $this->builder()->get(); // Use findAll() instead of get()
     return ($query->getResult());
 }
     
    //  $customerNames = [];

    //  foreach ($query->getResult() as $row) {
    //      $customerNames[] = [ $row->full_name ];
    //  }
    //  print_r($customerNames);
    //  die();
     
 
//  public function getCustomerIdByName($customerName, $businessId)
// {
//     $customer = $this->builder()
//         ->select('customer_id')
//         ->where('CONCAT(first_name, " ", last_name)', $customerName)
//         ->where('business_id', $businessId)
//         ->get()
//         ->getRow();

//     if ($customer) {
//         return $customer->customer_id;
//     }

//     return null; // Customer not found
// }


    public function insertAddress($addressData) {
        $this->db->table('customer_addresses')->insert($addressData);
        return $this->db->insertID(); // Return the last inserted ID
    }

    public function updateAddress($id, $addressData, $addressType) {


        if ($addressType == 'b') {
            $addressTypeValue = 1;
        } else {
            $addressTypeValue = 2;
        }
        $isActive = 1;
    
        // Check if an active address of the specified type already exists for the customer
        $existingAddress = $this->db->table('customer_addresses')
                                    ->where('customer_id', $id)
                                    ->where('isactive', $isActive)
                                    ->where('address_type', $addressTypeValue)
                                    ->get()
                                    ->getRow();
    
        // If an active address of the specified type exists, update it
        if ($existingAddress) {
            $this->db->table('customer_addresses')
                     ->where('customer_id', $id)
                     ->where('address_type', $addressTypeValue)
                     ->update($addressData);
            return $id; // Return the customer ID
        } else {
            // If no active address of the specified type exists, insert a new one
            $addressData['customer_id'] = $id;
            $addressData['address_type'] = $addressTypeValue;
            $addressData['isactive'] = $isActive;
            $this->db->table('customer_addresses')->insert($addressData);
            return $this->db->insertID(); // Return the last inserted ID
        }
    }
    
    
    public function insertAddressBatch($addressesDataArray) {
        $this->db->table('customer_addresses')->insertBatch($addressesDataArray);
        return $this->db->insertID(); // Note: insertID() might not be applicable for batch inserts
    }

    public function saveAddressDetails($data){
        $i = 0;
        $statement = [];
        foreach ($data as $row) {
            $id = $row['customer_address_id'];
            if($id != ''){
                unset($row['customer_address_id']); // Remove the id from the data to avoid updating it
                unset($row['created_by']); // bcoz here data Updating here.
                $this->db->table('customer_addresses')->where('customer_address_id', $id)->update($row);// Update the row with the specified id
                $affectedRows = $this->db->affectedRows();
                $statement[$i] = "address - ".$id." ". ($affectedRows ? " Updated":" Nothing to Updated");
            }else{
                $this->db->table('customer_addresses')->insert($row);
                $insertID = $this->db->insertID();
                $statement[$i] = "address - ".$insertID." Inserted";
            }
            $i++;
        }
        return $statement;
    }


    public function inactiveMissingAddressDetails($where,$missingValues){
        $dataToUpdate = ['isactive' => 0];
        $this->db->table('customer_addresses')->where($where)->whereIn('customer_address_id',$missingValues)->update($dataToUpdate);
    }
    public function getInvoicesByCustomerId($customer_id)
{
    return $this->db->table('invoice')
        ->select('invoice_number, subtotal,total_amount, invoice_date,DATE_FORMAT(invoice_date, "%d/%m/%Y") AS formatted_invoice_date,invoice_type')
        ->where('customer_id', $customer_id)
        ->where('invoice.invoice_type = "1"')
        ->get()
        ->getResult();
        // print_r($this->db->getLastQuery());die;
}
// public function getSubscriberDataByCustomerId($customerId)
// {
//     // Default invoice type is 2 (subscription).
//     $where = ['subscription.customer_id'=> $customerId,'invoice.invoice_type' => 2,'subscription.isactive' => 1];
//     // Assuming 'subscription' is the table name where subscriber data is stored.
//     $builder = $this->db->table('subscription');
//     $builder->join('books', 'books.book_id = subscription.scheme_id');
//     $builder->join('invoice', 'subscription.customer_id = invoice.customer_id', 'left');
//     // Define the columns you want to select from the subscription table.
//     $builder->select('subscription.*,books.title,DATE_FORMAT(subscription.from_subscription, "%d/%m/%Y") AS formatted_from_subscription,DATE_FORMAT(subscription.to_subscription, "%d/%m/%Y") AS formatted_to_subscription,DATEDIFF(subscription.to_subscription, subscription.from_subscription) as subscription_in_days');
//     // Add a where condition to filter results based on customer ID.
//     $builder->where($where);
//     $builder->groupBy('subscription.sub_id');
//     // Execute the query and return the result as an array of objects.
//     return $builder->get()->getResult();
// }

public function getSubscriberDataByCustomerId($customerId)
{
    // Default invoice type is 2 (subscription).
    $where = ['subscription.customer_id'=> $customerId,'invoice.invoice_type' => 2];
    $builder = $this->db->table('subscription');
    $builder->select('subscription.sub_id,subscription.scheme_id,subscription.customer_id,subscription.invoice_id,subscription.isactive,invoice.invoice_number,invoice.status,books.title, subscription.from_subscription,subscription.to_subscription,DATE_FORMAT(subscription.from_subscription, "%d/%m/%Y") AS formatted_from_subscription, DATE_FORMAT(subscription.to_subscription, "%d/%m/%Y") AS formatted_to_subscription, DATEDIFF(subscription.to_subscription, subscription.from_subscription) as subscription_in_days');
    $builder->join('books', 'books.book_id = subscription.scheme_id', 'left');
    $builder->join('invoice', 'subscription.invoice_id = invoice.invoice_id', 'left');
    $builder->where($where);
    // $builder->groupBy('subscription.sub_id');
    return $builder->get()->getResult();
    // print_r($this->db->getLastQuery());die;
}

public function saveGroupDetails($data){
    $id = $data['group_id'];
    if($id != ''){
        unset($data['group_id']); // Remove the id from the data to avoid updating it
        unset($data['created_by']); // bcoz here data Updating here.
        if ($this->db->table('customer_groups')->where('group_id', $id)->update($data)) {
            $affectedRows = $this->db->affectedRows();
            $statement['success'] = 'Customer Group has been updated successfully.';
            $statement['error'] = "";
            $statement['log'] = "Customer Group: has been updated successfully. Updated Customer Group ID = " .$id." Affected Rows = ".$affectedRows;
        } else {
            $statement['success'] = "";
            $statement['error'] = 'Customer group update failed. Please try again.';
            $statement['log'] = "Customer Group:  Err Failed to update ID = " .$id ;
        }
    }else{
        unset($data['updated_by']);
        if ($this->db->table('customer_groups')->insert($data)) {
            $insertID = $this->db->insertID();
            $statement['success'] = 'Customer Group has been Inserted successfully.';
            $statement['error'] = "";
            $statement['log'] = "Customer Group : has been added successfully. Inserted ID = " .$insertID ;
        } else {
            $statement['success'] = "";
            $statement['error'] = "Customer could not be added. Please try again..";
            $statement['log'] = "Customer: Err could not be added. Please try again.";
        }
    }
return $statement;
}

public function getGroupDetails()
{
return $this->db->table('customer_groups as CG' )
->join('users as U1', 'U1.user_id = CG.created_by', 'left')
->join('users as U2', 'U2.user_id = CG.updated_by', 'left')
->select('CG.group_id,CG.group_name,CG.created_on,CG.created_by,CG.updated_on,CG.updated_by,DATE_FORMAT(CG.created_on, "%d/%m/%Y %h:%i %p") AS formatted_created_on,CONCAT_WS(" ", U1.first_name, U1.last_name) as created_by_name,CONCAT_WS(" ", U2.first_name, U2.last_name) as updated_by_name,CG.isactive')
->where(['CG.group_name != ' => 'EXPIRYDATE'])
// ->where(['CG.isactive' => 1])
->get()
->getResultArray();
}

public function storeCustomerAddresses($billingAddress, $shippingAddress)
{
    // $this->db->transStart(); // Begin transaction
    
    // Insert billing address
    if(!empty($billingAddress['billing_address1'])){
    $billingData = [
        'customer_id' => $billingAddress['customer_id'],
        'first_name'=> $billingAddress['first_name'],
        'last_name'=> $billingAddress['last_name'],
        'address_1' => $billingAddress['billing_address1'],
        'address_2' => $billingAddress['billing_address2'],
        'city' => $billingAddress['billing_city'],
        'state' => $billingAddress['billing_state'],
        'country' => $billingAddress['billing_country'],
        'postal_code' => $billingAddress['billing_pincode'],
        'address_type' => 1 // Billing address type
    ];
    $this->db->table('customer_addresses')->insert($billingData);
    $billing_addr_id = $this->db->insertID();
    }else{
        $billing_addr_id = NULL;
    }

    if(!empty($shippingAddress['shipping_address1'])){
    // Insert shipping address
    $shippingData = [
        'customer_id' => $shippingAddress['customer_id'],
        'first_name'=> $shippingAddress['first_name'],
        'last_name'=> $shippingAddress['last_name'],
        'address_1' => $shippingAddress['shipping_address1'],
        'address_2' => $shippingAddress['shipping_address2'],
        'city' => $shippingAddress['shipping_city'],
        'state' => $shippingAddress['shipping_state'],
        'country' => $shippingAddress['shipping_country'],
        'postal_code' => $shippingAddress['shipping_pincode'],
        'address_type' => 2 // Shipping address type
    ];
    $this->db->table('customer_addresses')->insert($shippingData);
    $shipping_addr_id = $this->db->insertID();
    }else{
        $shipping_addr_id = NULL;
    }
    return ["billing_addr_id"=>$billing_addr_id,"shipping_addr_id"=>$shipping_addr_id]; // Return true indicating successful insertion
}
public function getType(){
    return $this->db->table('customers as C' )
    ->select('C.type as value')
    ->groupBy('C.type')
    ->get()
    ->getResultArray();
}
public function getMode(){
    return $this->db->table('customers as C' )
    ->select('C.mode as value')
    ->where(['C.mode != ' => ''])
    ->groupBy('C.mode')
    ->get()
    ->getResultArray();
}
public function getCategory(){
    return $this->db->table('category as C' )
    ->select('C.name as value')
    ->where(['C.name != ' => '','C.isactive' => 1])
    ->groupBy('C.name')
    ->get()
    ->getResultArray();
}


public function get_customer_details_by_customer_id($customer_id)
{
        $builder = $this->db->table('customers'); // Specify the main table
        $builder->select('customers.customer_id, customers.mobile_no,customers.first_name, customers.last_name,customers.mobile_no,customer_addresses.address_1, customer_addresses.city, customer_addresses.state, customer_addresses.postal_code, customer_addresses.country',customer);
        $builder->join('customer_addresses', 'customers.customer_id = customer_addresses.customer_id');
        $builder->where('customers.customer_id', $customer_id);

    $result = $builder->get()->getRowArray(); // Fetch the first matching record as an associative array

    return $result;
}
}
?>