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

class Expiry_notification_model extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    // Move expired quantities to dead stock (via negative stock adjustment) and zero out sellable stock
    public function move_expired_to_dead_stock($store_id = null, $warehouse_id = null){
        $this->db->trans_begin();
        try{
            $store_id = !empty($store_id) ? $store_id : get_current_store_id();

            // Fetch expired batches with remaining quantities
            $this->db->select('ib.item_id, ib.warehouse_id, SUM(ib.remaining_quantity) as qty_to_remove');
            $this->db->from('db_item_batches ib');
            $this->db->join('db_items i', 'i.id = ib.item_id');
            $this->db->where('ib.store_id', $store_id);
            $this->db->where('ib.status', 1);
            $this->db->where('ib.remaining_quantity >', 0);
            $this->db->where('ib.expiry_date IS NOT NULL');
            $this->db->where('ib.expiry_date <', date('Y-m-d'));
            $this->db->where('i.expiry_enabled', 1);
            if(!empty($warehouse_id)){
                $this->db->where('ib.warehouse_id', $warehouse_id);
            }
            $this->db->group_by(array('ib.item_id','ib.warehouse_id'));
            $expired_groups = $this->db->get()->result();

            if(empty($expired_groups)){
                $this->db->trans_commit();
                return array('status'=>'success','updated_items'=>0,'message'=>'No expired stock to move');
            }

            $this->load->model('items_model');

            $affected_item_ids = array();

            foreach($expired_groups as $row){
                $item_id = (int)$row->item_id;
                $wh_id = !empty($row->warehouse_id) ? (int)$row->warehouse_id : get_store_warehouse_id();
                $qty_to_remove = (float)$row->qty_to_remove;

                if($qty_to_remove <= 0){ continue; }

                // Create negative stock adjustment entry (reason: Expired -> Dead Stock)
                $adjustment_ok = $this->items_model->add_opening_stock(array(
                    'store_id' => $store_id,
                    'item_id' => $item_id,
                    'warehouse_id' => $wh_id,
                    'adjustment_qty' => -$qty_to_remove,
                ));
                if(!$adjustment_ok){
                    throw new Exception('Failed to insert stock adjustment for expired item '.$item_id);
                }

                // Zero out remaining_quantity for these expired batches for this item and warehouse
                $this->db->set('remaining_quantity', 0, false);
                $this->db->set('status', 0);
                $this->db->where('item_id', $item_id);
                $this->db->where('store_id', $store_id);
                $this->db->where('expiry_date <', date('Y-m-d'));
                $this->db->where('remaining_quantity >', 0);
                $this->db->where('expiry_date IS NOT NULL');
                $this->db->where('status', 1);
                if(!empty($warehouse_id)){
                    $this->db->where('warehouse_id', $wh_id);
                }
                $this->db->update('db_item_batches');

                $affected_item_ids[] = $item_id;
            }

            // Update warehouse wise items qty for affected items
            if(!empty($affected_item_ids)){
                $unique_ids = array_values(array_unique($affected_item_ids));
                $pairs = array();
                foreach($unique_ids as $id){ $pairs[] = array($id); }
                $q7 = update_warehouse_items($pairs);
                if(!$q7){
                    throw new Exception('Failed to update warehouse stock for expired items');
                }
            }

            $this->db->trans_commit();
            return array('status'=>'success','updated_items'=>count(array_unique($affected_item_ids)));
        }
        catch(Exception $e){
            $this->db->trans_rollback();
            log_message('error','Dead stock move failed: '.$e->getMessage());
            return array('status'=>'error','message'=>$e->getMessage());
        }
    }

    // Create batch record when item is purchased
    public function create_batch_record($purchase_item_data) {
        $this->db->trans_begin();
        
        try {
            // Check if item has batch tracking enabled
            $item = $this->db->select('batch_tracking_enabled, expiry_enabled, tracking_type, expiry_notification_days')
                            ->from('db_items')
                            ->where('id', $purchase_item_data['item_id'])
                            ->get()->row();
            
            if (!$item || !$item->batch_tracking_enabled) {
                return true; // No batch tracking needed
            }
            
            // Create batch record
            $batch_data = array(
                'item_id' => $purchase_item_data['item_id'],
                'batch_number' => $purchase_item_data['batch_number'],
                'manufacturing_date' => $purchase_item_data['manufacturing_date'],
                'expiry_date' => $purchase_item_data['expiry_date'],
                'quantity' => $purchase_item_data['purchase_qty'],
                'remaining_quantity' => $purchase_item_data['purchase_qty'],
                'purchase_price' => $purchase_item_data['price_per_unit'],
                'store_id' => $purchase_item_data['store_id'],
                'warehouse_id' => $purchase_item_data['warehouse_id'] ?? null,
                'created_date' => date('Y-m-d'),
                'created_time' => date('H:i:s'),
                'created_by' => get_current_user_id(),
                'status' => 1
            );
            
            $this->db->insert('db_item_batches', $batch_data);
            $batch_id = $this->db->insert_id();
            
            // Create expiry alert if expiry date is set
            if (!empty($purchase_item_data['expiry_date']) && $item->expiry_enabled) {
                $this->create_expiry_alert($batch_id, $purchase_item_data['item_id'], $purchase_item_data['expiry_date'], $item->expiry_notification_days);
            }
            
            $this->db->trans_commit();
            return true;
            
        } catch (Exception $e) {
            $this->db->trans_rollback();
            log_message('error', 'Error creating batch record: ' . $e->getMessage());
            return false;
        }
    }

    // Create expiry alert record
    public function create_expiry_alert($batch_id, $item_id, $expiry_date, $notification_days = 30) {
        $days_until_expiry = $this->calculate_days_until_expiry($expiry_date);
        
        if ($days_until_expiry <= $notification_days) {
            $alert_data = array(
                'item_id' => $item_id,
                'batch_id' => $batch_id,
                'store_id' => get_current_store_id(),
                'expiry_date' => $expiry_date,
                'days_until_expiry' => $days_until_expiry,
                'alert_sent' => 0,
                'created_at' => date('Y-m-d H:i:s')
            );
            
            $this->db->insert('db_expiry_alerts', $alert_data);
        }
    }

    // Calculate days until expiry
    public function calculate_days_until_expiry($expiry_date) {
        $expiry = new DateTime($expiry_date);
        $today = new DateTime();
        $diff = $today->diff($expiry);
        
        if ($expiry < $today) {
            return -$diff->days; // Negative for expired items
        }
        
        return $diff->days;
    }

    // Get expiring items for notifications
    public function get_expiring_items($days_ahead = 30) {
        $this->db->select('ib.*, i.item_name, i.item_code, s.store_name');
        $this->db->from('db_item_batches ib');
        $this->db->join('db_items i', 'i.id = ib.item_id');
        $this->db->join('db_store s', 's.id = ib.store_id', 'left');
        $this->db->where('ib.status', 1);
        $this->db->where('ib.remaining_quantity >', 0);
        $this->db->where('ib.expiry_date IS NOT NULL');
        $this->db->where('DATEDIFF(ib.expiry_date, CURDATE()) <=', $days_ahead);
        $this->db->where('DATEDIFF(ib.expiry_date, CURDATE()) >=', 0);
        $this->db->order_by('ib.expiry_date', 'ASC');
        
        return $this->db->get()->result();
    }

    // Get expired items
    public function get_expired_items() {
        $this->db->select('ib.*, i.item_name, i.item_code, s.store_name');
        $this->db->from('db_item_batches ib');
        $this->db->join('db_items i', 'i.id = ib.item_id');
        $this->db->join('db_store s', 's.id = ib.store_id', 'left');
        $this->db->where('ib.status', 1);
        $this->db->where('ib.remaining_quantity >', 0);
        $this->db->where('ib.expiry_date IS NOT NULL');
        $this->db->where('ib.expiry_date <', date('Y-m-d'));
        $this->db->order_by('ib.expiry_date', 'ASC');
        
        return $this->db->get()->result();
    }

    // Send expiry notifications
    public function send_expiry_notifications() {
        $notifications_sent = 0;
        
        // Get expiring items (next 30 days)
        $expiring_items = $this->get_expiring_items(30);
        
        foreach ($expiring_items as $item) {
            $days_until_expiry = $this->calculate_days_until_expiry($item->expiry_date);
            
            // Check if notification already sent for this batch
            $existing_alert = $this->db->select('id, alert_sent')
                                     ->from('db_expiry_alerts')
                                     ->where('batch_id', $item->id)
                                     ->where('alert_sent', 1)
                                     ->get()->row();
            
            if ($existing_alert) {
                continue; // Already sent
            }
            
            // Create notification
            $notification_data = array(
                'user_id' => null, // System notification
                'store_id' => $item->store_id,
                'title' => 'Item Expiring Soon',
                'message' => "Item '{$item->item_name}' (Batch: {$item->batch_number}) will expire in {$days_until_expiry} days on {$item->expiry_date}",
                'type' => $days_until_expiry <= 7 ? 'error' : 'warning',
                'notification_type' => 'expiry_alert',
                'reference_id' => $item->id,
                'reference_table' => 'db_item_batches',
                'is_read' => 0,
                'created_at' => date('Y-m-d H:i:s')
            );
            
            $this->db->insert('db_notifications', $notification_data);
            
            // Update expiry alert
            $this->db->where('batch_id', $item->id)
                    ->update('db_expiry_alerts', array(
                        'alert_sent' => 1,
                        'alert_sent_date' => date('Y-m-d H:i:s')
                    ));
            
            $notifications_sent++;
        }
        
        return $notifications_sent;
    }

    // Get notification count for dashboard
    public function get_expiry_notification_count() {
        $this->db->select('COUNT(*) as count');
        $this->db->from('db_notifications');
        $this->db->where('notification_type', 'expiry_alert');
        $this->db->where('is_read', 0);
        $this->db->where('store_id', get_current_store_id());
        
        $result = $this->db->get()->row();
        return $result ? $result->count : 0;
    }

    // Mark notification as read
    public function mark_notification_read($notification_id) {
        $this->db->where('id', $notification_id)
                ->update('db_notifications', array(
                    'is_read' => 1,
                    'read_at' => date('Y-m-d H:i:s')
                ));
    }

    // Update batch quantity when item is sold
    public function update_batch_quantity($item_id, $quantity_sold, $batch_number = null) {
        if (empty($batch_number)) {
            // Use FIFO (First In, First Out) method
            $this->db->select('id, remaining_quantity');
            $this->db->from('db_item_batches');
            $this->db->where('item_id', $item_id);
            $this->db->where('remaining_quantity >', 0);
            $this->db->where('expiry_date IS NOT NULL');
            $this->db->order_by('expiry_date', 'ASC');
            $this->db->order_by('created_date', 'ASC');
            
            $batches = $this->db->get()->result();
        } else {
            // Use specific batch
            $this->db->select('id, remaining_quantity');
            $this->db->from('db_item_batches');
            $this->db->where('item_id', $item_id);
            $this->db->where('batch_number', $batch_number);
            $this->db->where('remaining_quantity >', 0);
            
            $batches = $this->db->get()->result();
        }
        
        $remaining_qty = $quantity_sold;
        
        foreach ($batches as $batch) {
            if ($remaining_qty <= 0) break;
            
            $available_qty = $batch->remaining_quantity;
            $deduct_qty = min($remaining_qty, $available_qty);
            
            $this->db->where('id', $batch->id)
                    ->update('db_item_batches', array(
                        'remaining_quantity' => $available_qty - $deduct_qty
                    ));
            
            $remaining_qty -= $deduct_qty;
        }
        
        return $remaining_qty; // Return any remaining quantity that couldn't be allocated
    }

    // Get batch details for sales
    public function get_available_batches($item_id) {
        $this->db->select('id, batch_number, manufacturing_date, expiry_date, remaining_quantity');
        $this->db->from('db_item_batches');
        $this->db->where('item_id', $item_id);
        $this->db->where('remaining_quantity >', 0);
        $this->db->where('status', 1);
        $this->db->order_by('expiry_date', 'ASC');
        
        return $this->db->get()->result();
    }
}
