<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Sales_billing_model extends CI_Model {

    protected $table        = 'sales_bill_master';
    protected $table_items  = 'sales_bill_items';

    // ── LIST ─────────────────────────────────────────────────────────────────
    public function getAll() {
        $this->db->select('sbm.*, c.customer_name, c.mobile');
        $this->db->from('sales_bill_master sbm');
        $this->db->join('customer_master c', 'c.id = sbm.customer_id', 'left');
        $this->db->order_by('sbm.bill_date', 'DESC');
        return $this->db->get()->result();
    }

    // ── ONE HEADER ───────────────────────────────────────────────────────────
    public function getOne($id) {
        $this->db->select('sbm.*, c.customer_name, c.mobile, c.address');
        $this->db->from('sales_bill_master sbm');
        $this->db->join('customer_master c', 'c.id = sbm.customer_id', 'left');
        $this->db->where('sbm.id', $id);
        return $this->db->get()->row();
    }

    // ── ITEMS FOR A BILL ─────────────────────────────────────────────────────
    public function getItems($bill_id) {
        $this->db->select('sbi.*, p.product_name, u.unit_name');
        $this->db->from('sales_bill_items sbi');
        $this->db->join('product_master p', 'p.id = sbi.product_id', 'left');
        $this->db->join('unit_master u',    'u.id = p.unit_id',      'left');
        $this->db->where('sbi.bill_id', $bill_id);
        return $this->db->get()->result();
    }

    // ── BILL NO DUPLICATE CHECK ──────────────────────────────────────────────
    // Excludes $exclude_id so a bill can keep its own bill_no while editing.
    public function isBillNoDuplicate($bill_no, $exclude_id = null) {
        $this->db->where('bill_no', $bill_no);
        if ($exclude_id) $this->db->where('id !=', $exclude_id);
        return $this->db->get($this->table)->num_rows() > 0;
    }

    // ── GENERATE BILL NO ─────────────────────────────────────────────────────
    public function generateBillNo() {
        $last = $this->db->select('id')->order_by('id', 'DESC')->limit(1)->get($this->table)->row();
        $next = $last ? ($last->id + 1) : 1;
        return 'BILL-' . str_pad($next, 4, '0', STR_PAD_LEFT);
    }

    // ── INSERT HEADER + ITEMS ────────────────────────────────────────────────
    public function insertWithItems($header, $items) {
        $this->db->trans_start();

        $this->db->insert($this->table, $header);
        $bill_id = $this->db->insert_id();

        foreach ($items as $item) {
            $item['bill_id'] = $bill_id;
            // Deduct stock
            $this->db->query(
                "UPDATE product_master SET current_stock = current_stock - ? WHERE id = ?",
                [(int)$item['qty'], (int)$item['product_id']]
            );
            $this->db->insert($this->table_items, $item);
        }

        $this->db->trans_complete();
        return $this->db->trans_status() ? $bill_id : false;
    }

    // ── UPDATE HEADER + ITEMS ────────────────────────────────────────────────
    // Returns TRUE on success, or an array ['error' => 'message'] on failure
    // so the controller can show the person what actually went wrong instead
    // of silently reporting success.
    public function updateWithItems($id, $header, $items) {
        $this->db->trans_start();

        // Restore old stock
        $old_items = $this->getItems($id);
        foreach ($old_items as $oi) {
            $this->db->query(
                "UPDATE product_master SET current_stock = current_stock + ? WHERE id = ?",
                [(float)$oi->qty, (int)$oi->product_id]
            );
        }

        $this->db->update($this->table, $header, ['id' => $id]);
        if ($this->db->error()['code'] != 0) {
            $this->db->trans_complete();
            return ['error' => 'Header update failed: ' . $this->db->error()['message']];
        }

        $this->db->delete($this->table_items, ['bill_id' => $id]);
        if ($this->db->error()['code'] != 0) {
            $this->db->trans_complete();
            return ['error' => 'Deleting old items failed: ' . $this->db->error()['message']];
        }

        foreach ($items as $item) {
            $item['bill_id'] = $id;
            $this->db->query(
                "UPDATE product_master SET current_stock = current_stock - ? WHERE id = ?",
                [(float)$item['qty'], (int)$item['product_id']]
            );
            $this->db->insert($this->table_items, $item);
            if ($this->db->error()['code'] != 0) {
                $this->db->trans_complete();
                return ['error' => 'Inserting item failed: ' . $this->db->error()['message']];
            }
        }

        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return ['error' => 'Transaction rolled back. Please check server error log / product stock values.'];
        }

        return TRUE;
    }

    // ── DELETE ───────────────────────────────────────────────────────────────
    public function delete($id) {
        $this->db->trans_start();

        $items = $this->getItems($id);
        foreach ($items as $item) {
            $this->db->query(
                "UPDATE product_master SET current_stock = current_stock + ? WHERE id = ?",
                [(int)$item->qty, (int)$item->product_id]
            );
        }

        $this->db->delete($this->table, ['id' => $id]);

        $this->db->trans_complete();
        return $this->db->trans_status();
    }

    // ── TOTAL SALES ───────────────────────────────────────────────────────────
    // Fix: column is net_payable, not grand_total
    public function getTotalSales($where = []) {
        if (!empty($where)) $this->db->where($where);
        $this->db->select_sum('net_payable');
        $row = $this->db->get($this->table)->row();
        return $row->net_payable ?? 0;
    }

    // ── MONTHLY SUMMARY ───────────────────────────────────────────────────────
    // Returns last 12 months: month label, bill count, total sales
    public function getMonthly() {
        $this->db->select("
            DATE_FORMAT(bill_date, '%b %Y')  AS month_label,
            DATE_FORMAT(bill_date, '%Y-%m')  AS month_sort,
            COUNT(id)                        AS total_bills,
            SUM(net_payable)                 AS total_sales,
            SUM(discount)                    AS total_discount
        ");
        $this->db->from($this->table);
        $this->db->where('bill_date >=', date('Y-m-d', strtotime('-11 months', strtotime(date('Y-m-01')))));
        $this->db->group_by("DATE_FORMAT(bill_date, '%Y-%m')");
        $this->db->order_by('month_sort', 'ASC');
        return $this->db->get()->result();
    }
}