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

class Quotation_model extends CI_Model {

    protected $table        = 'quotation_master';
    protected $table_items  = 'quotation_items';

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

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

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

    // ── 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 'QT-' . 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;
            // Quotation is only an estimate — stock is NOT deducted here.
            $this->db->insert($this->table_items, $item);
        }

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

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

        // Quotation is only an estimate — stock is NOT touched here.
        $this->db->update($this->table, $header, ['id' => $id]);
        $this->db->delete($this->table_items, ['bill_id' => $id]);

        foreach ($items as $item) {
            $item['bill_id'] = $id;
            $this->db->insert($this->table_items, $item);
        }

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

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

        // Quotation never touched stock, so nothing to restore here either.
        $this->db->delete($this->table_items, ['bill_id' => $id]);
        $this->db->delete($this->table, ['id' => $id]);

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

    // ── TOTAL SALES (quotations) ────────────────────────────────────────────
    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 ───────────────────────────────────────────────────────
    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();
    }
}