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

class Reports extends CI_Controller {

    public function __construct() {
        parent::__construct();
        $this->_check_login();
        $this->load->model(['Purchase_entry_model', 'Sales_billing_model', 'Tailoring_order_model', 'Product_model', 'Customer_model', 'Category_model']);
        $this->load->library('session');
        $this->load->helper('url');
    }

    private function _check_login() {
        if (!$this->session->userdata('admin_logged_in')) {
            redirect('Auth');
        }
    }

    /* ── STOCK REPORT ── */
    public function stock() {
        $data['page_title'] = 'Stock Report';
        $data['breadcrumb'] = [['label' => 'Reports'], ['label' => 'Stock Report', 'active' => true]];

        // Category + Product multiselect filter (empty = show all)
        $category_ids = $this->input->get('category_id') ?: [];
        $product_ids  = $this->input->get('product_id')  ?: [];
        $data['categories']          = $this->Category_model->getActive();
        $data['products']            = $this->Product_model->getActive();
        $data['selected_categories'] = $category_ids;
        $data['selected_products']   = $product_ids;

        /*
         * DB Schema:
         *   purchase_entry_items  → purchase_id, product_id, qty
         *   sales_bill_items      → bill_id, product_id, qty
         */
        $this->db->select('
            p.id, p.product_name, p.sale_rate, p.status,
            c.category_name, u.unit_name,
            COALESCE(SUM(pei.qty), 0)          AS total_purchased,
            COALESCE(sale_data.sold_qty, 0)    AS total_sold
        ');
        $this->db->from('product_master p');
        $this->db->join('category_master c', 'c.id = p.category_id', 'left');
        $this->db->join('unit_master u',     'u.id = p.unit_id',     'left');

        // Purchase qty from purchase_entry_items
        $this->db->join('purchase_entry_items pei', 'pei.product_id = p.id', 'left');

        // Sales qty sub-query from sales_bill_items
        $this->db->join(
            "(SELECT product_id, SUM(qty) AS sold_qty FROM sales_bill_items GROUP BY product_id) sale_data",
            'sale_data.product_id = p.id', 'left'
        );

        if (!empty($category_ids)) {
            $this->db->where_in('p.category_id', $category_ids);
        }
        if (!empty($product_ids)) {
            $this->db->where_in('p.id', $product_ids);
        }

        $this->db->group_by('p.id');
        $this->db->order_by('p.product_name');
        $data['rows'] = $this->db->get()->result();

        // Computed stock column available in view as:
        // $row->total_purchased - $row->total_sold

        $this->load->view('layout/header',  $data);
        $this->load->view('layout/sidebar', $data);
        $this->load->view('reports/stock',  $data);
        $this->load->view('layout/footer',  $data);
    }

    /* ── SALES REPORT ── */
    public function sales_report() {
        $data['page_title'] = 'Sales Report';
        $data['breadcrumb'] = [['label' => 'Reports'], ['label' => 'Sales Report', 'active' => true]];

        // Date filter (default: current month)
        $from = $this->input->get('from') ?: date('Y-m-01');
        $to   = $this->input->get('to')   ?: date('Y-m-d');

        /*
         * DB Schema:
         *   sales_bill_master  → id, bill_no, customer_id, bill_date,
         *                        subtotal, discount, net_payable
         *   sales_bill_items   → bill_id, product_id, qty, rate, amount, total
         *
         * One bill can have multiple items, so we join items and group by bill
         * to get one representative product per bill — OR list item-level rows.
         * Here we list item-level rows (one row per product line in each bill).
         */
        $this->db->select('
            sbm.id, sbm.bill_no, sbm.bill_date AS sale_date, sbm.subtotal,
            sbm.discount, sbm.net_payable,
            c.customer_name,
            p.product_name,
            sbi.qty, sbi.rate AS sale_rate, sbi.gst_percent,
            sbi.amount AS line_amount, sbi.total AS net_amount
        ');
        $this->db->from('sales_bill_master sbm');
        $this->db->join('sales_bill_items sbi', 'sbi.bill_id = sbm.id',     'left');
        $this->db->join('customer_master c',    'c.id = sbm.customer_id',   'left');
        $this->db->join('product_master p',     'p.id = sbi.product_id',    'left');
        $this->db->where('DATE(sbm.bill_date) >=', $from);
        $this->db->where('DATE(sbm.bill_date) <=', $to);
        $this->db->order_by('sbm.bill_date', 'DESC');
        $data['rows'] = $this->db->get()->result();

        $data['from']           = $from;
        $data['to']             = $to;

        // Totals: use net_payable from master (avoid duplicates from multi-item bills)
        // Get unique bills for summary totals
        $this->db->select('sbm.id, sbm.net_payable, sbm.discount');
        $this->db->from('sales_bill_master sbm');
        $this->db->where('DATE(sbm.bill_date) >=', $from);
        $this->db->where('DATE(sbm.bill_date) <=', $to);
        $bills = $this->db->get()->result();

        $data['total_bills']    = count($bills);
        $data['total_amount']   = array_sum(array_column($bills, 'net_payable'));
        $data['total_discount'] = array_sum(array_column($bills, 'discount'));

        // Monthly summary from model
        $data['monthly'] = $this->Sales_billing_model->getMonthly();

        $this->load->view('layout/header',  $data);
        $this->load->view('layout/sidebar', $data);
        $this->load->view('reports/sales',  $data);
        $this->load->view('layout/footer',  $data);
    }
}