<?php

namespace App\Models;

use CodeIgniter\Model;

class SaleModel extends Model
{
    protected $table      = 'sales';
    protected $primaryKey = 'id';

    protected $allowedFields = [
        'kode',
        'tanggal',
        'customer_id',
        'total',
        'pajak_persen',
        'pajak_nominal',
        'total_bayar',
        'dibayar',
        'kembalian',
        'payment_method',
        'user_id',
        'diskon_persen',
        'diskon_nominal',
        'jenis_pesan', 
        'no_meja'
    ];

    public function getJoinAll($filters = [], $limit = null, $offset = null)
{
    $builder = $this->select('sales.*, customers.nama AS customer_name, users.username AS user_name')
                    ->join('customers', 'customers.id = sales.customer_id', 'left')
                    ->join('users', 'users.id = sales.user_id', 'left')
                    ->orderBy('sales.id', 'DESC');

    // Filter berdasarkan tanggal (start & end)
    if (!empty($filters['start_date']) && !empty($filters['end_date'])) {
        $builder->where('DATE(sales.tanggal) >=', $filters['start_date']);
        $builder->where('DATE(sales.tanggal) <=', $filters['end_date']);
    }

    // Filter berdasarkan user (kasir)
    if (!empty($filters['user_id'])) {
        $builder->where('sales.user_id', $filters['user_id']);
    }

    if ($limit !== null && $offset !== null) {
        $builder->limit($limit, $offset);
    }

    return $builder->findAll();
}


    public function getJoinById($id)
    {
        return $this->select('sales.*, customers.nama AS customer_name, users.username AS user_name')
                    ->join('customers', 'customers.id = sales.customer_id', 'left')
                    ->join('users', 'users.id = sales.user_id', 'left')
                    ->where('sales.id', $id)
                    ->first();
    }

    public function getWithUserAndCustomer($id)
    {
        return $this->select('sales.*, customers.nama as customer_name, users.username as user_name')
                    ->join('customers', 'customers.id = sales.customer_id', 'left')
                    ->join('users', 'users.id = sales.user_id', 'left')
                    ->where('sales.id', $id)
                    ->first();
    }

    public function getFilteredSales($start = null, $end = null, $kasir = null)
{
    $builder = $this->select('sales.*, customers.nama AS customer_name, users.username AS user_name')
                    ->join('customers', 'customers.id = sales.customer_id', 'left')
                    ->join('users', 'users.id = sales.user_id', 'left');

    if ($start && $end) {
        $builder->where('tanggal >=', $start)
                ->where('tanggal <=', $end);
    }

    if ($kasir) {
        $builder->where('sales.user_id', $kasir);
    }

    return $builder->orderBy('sales.id', 'DESC')->findAll();
}

public function getJoinAllPaginated($filters = [], $limit = 15, $offset = 0)
{
    $builder = $this->select('sales.*, customers.nama AS customer_name, users.username AS user_name')
                    ->join('customers', 'customers.id = sales.customer_id', 'left')
                    ->join('users', 'users.id = sales.user_id', 'left')
                    ->orderBy('sales.id', 'DESC');

    // Filter berdasarkan tanggal (start & end)
    if (!empty($filters['start_date']) && !empty($filters['end_date'])) {
        $builder->where('DATE(sales.tanggal) >=', $filters['start_date']);
        $builder->where('DATE(sales.tanggal) <=', $filters['end_date']);
    }

    // Filter berdasarkan user (kasir)
    if (!empty($filters['user_id'])) {
        $builder->where('sales.user_id', $filters['user_id']);
    }

    // Pagination manual
    return $builder->get($limit, $offset)->getResultArray();
}
public function getSalesPerMonth()
{
    $builder = $this->db->table('sales');
    $builder->select("DATE_FORMAT(date, '%Y-%m') as bulan, SUM(grand_total) as total");
    $builder->groupBy("DATE_FORMAT(date, '%Y-%m')");
    $builder->orderBy("bulan", 'ASC');
    return $builder->get()->getResult();
}


}
