desk-moloni/modules/desk_moloni/models/Desk_moloni_invoice_model.php

/**
 * Descomplicar® Crescimento Digital
 * https://descomplicar.pt
 */

<?php

defined('BASEPATH') or exit('No direct script access allowed');

/**
 * Desk-Moloni Invoice Model
 * 
 * Manages invoice data, synchronization, and business logic
 * Handles invoice operations between Perfex CRM and Moloni
 * 
 * @package    DeskMoloni
 * @subpackage Models
 * @version    3.0.0
 * @author     Descomplicar®
 */
class Desk_moloni_invoice_model extends CI_Model
{
    private $table = 'tblinvoices';
    private $moloni_invoice_table = 'tbldeskmoloni_invoices';
    
    /**
     * Constructor
     */
    public function __construct()
    {
        parent::__construct();
        
        // Create Moloni invoice mapping table if it doesn't exist
        $this->create_moloni_invoice_table();
    }
    
    /**
     * Create Moloni invoice mapping table
     */
    private function create_moloni_invoice_table()
    {
        if (!$this->db->table_exists($this->moloni_invoice_table)) {
            $this->db->query("
                CREATE TABLE IF NOT EXISTS `{$this->moloni_invoice_table}` (
                    `id` int(11) NOT NULL AUTO_INCREMENT,
                    `perfex_invoice_id` int(11) NOT NULL,
                    `moloni_invoice_id` int(11) DEFAULT NULL,
                    `moloni_document_id` varchar(255) DEFAULT NULL,
                    `moloni_document_number` varchar(100) DEFAULT NULL,
                    `moloni_document_type` varchar(50) DEFAULT 'invoice',
                    `sync_status` enum('pending','synced','failed','partial') DEFAULT 'pending',
                    `last_sync_at` datetime DEFAULT NULL,
                    `sync_error` text DEFAULT NULL,
                    `moloni_data` longtext DEFAULT NULL,
                    `pdf_url` varchar(500) DEFAULT NULL,
                    `pdf_downloaded` tinyint(1) DEFAULT 0,
                    `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                    `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    PRIMARY KEY (`id`),
                    UNIQUE KEY `uk_perfex_invoice` (`perfex_invoice_id`),
                    KEY `idx_moloni_invoice` (`moloni_invoice_id`),
                    KEY `idx_sync_status` (`sync_status`),
                    KEY `idx_last_sync` (`last_sync_at`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            ");
        }
    }
    
    /**
     * Get invoice with Moloni mapping data
     * 
     * @param int $invoice_id Perfex invoice ID
     * @return array|null Invoice data with Moloni mapping
     */
    public function get_invoice_with_moloni_data($invoice_id)
    {
        $this->db->select('i.*, mi.*');
        $this->db->from("{$this->table} i");
        $this->db->join("{$this->moloni_invoice_table} mi", 'mi.perfex_invoice_id = i.id', 'left');
        $this->db->where('i.id', $invoice_id);
        
        $query = $this->db->get();
        return $query->row_array();
    }
    
    /**
     * Get invoices for synchronization
     * 
     * @param array $filters Filtering options
     * @return array Invoices needing sync
     */
    public function get_invoices_for_sync($filters = [])
    {
        $this->db->select('i.*, mi.sync_status, mi.moloni_invoice_id, mi.last_sync_at');
        $this->db->from("{$this->table} i");
        $this->db->join("{$this->moloni_invoice_table} mi", 'mi.perfex_invoice_id = i.id', 'left');
        
        // Default filters
        if (!isset($filters['include_synced'])) {
            $this->db->where("(mi.sync_status IS NULL OR mi.sync_status != 'synced')");
        }
        
        // Status filter
        if (isset($filters['status'])) {
            $this->db->where('i.status', $filters['status']);
        }
        
        // Date range filter
        if (isset($filters['date_from'])) {
            $this->db->where('i.date >=', $filters['date_from']);
        }
        
        if (isset($filters['date_to'])) {
            $this->db->where('i.date <=', $filters['date_to']);
        }
        
        // Client filter
        if (isset($filters['clientid'])) {
            $this->db->where('i.clientid', $filters['clientid']);
        }
        
        // Sync status filter
        if (isset($filters['sync_status'])) {
            $this->db->where('mi.sync_status', $filters['sync_status']);
        }
        
        // Limit
        if (isset($filters['limit'])) {
            $this->db->limit($filters['limit']);
        }
        
        $this->db->order_by('i.date', 'DESC');
        
        $query = $this->db->get();
        return $query->result_array();
    }
    
    /**
     * Create or update Moloni invoice mapping
     * 
     * @param int $perfex_invoice_id Perfex invoice ID
     * @param array $moloni_data Moloni invoice data
     * @return bool Success status
     */
    public function save_moloni_mapping($perfex_invoice_id, $moloni_data)
    {
        $mapping_data = [
            'perfex_invoice_id' => $perfex_invoice_id,
            'moloni_invoice_id' => $moloni_data['document_id'] ?? null,
            'moloni_document_id' => $moloni_data['document_id'] ?? null,
            'moloni_document_number' => $moloni_data['number'] ?? null,
            'moloni_document_type' => $moloni_data['document_type'] ?? 'invoice',
            'sync_status' => $moloni_data['sync_status'] ?? 'synced',
            'last_sync_at' => date('Y-m-d H:i:s'),
            'moloni_data' => json_encode($moloni_data),
            'pdf_url' => $moloni_data['pdf_url'] ?? null
        ];
        
        // Check if mapping exists
        $existing = $this->db->get_where($this->moloni_invoice_table, 
                                       ['perfex_invoice_id' => $perfex_invoice_id])->row_array();
        
        if ($existing) {
            $mapping_data['updated_at'] = date('Y-m-d H:i:s');
            $this->db->where('perfex_invoice_id', $perfex_invoice_id);
            return $this->db->update($this->moloni_invoice_table, $mapping_data);
        } else {
            $mapping_data['created_at'] = date('Y-m-d H:i:s');
            return $this->db->insert($this->moloni_invoice_table, $mapping_data);
        }
    }
    
    /**
     * Update sync status for invoice
     * 
     * @param int $perfex_invoice_id Perfex invoice ID
     * @param string $status Sync status
     * @param string|null $error Error message if failed
     * @return bool Success status
     */
    public function update_sync_status($perfex_invoice_id, $status, $error = null)
    {
        $update_data = [
            'sync_status' => $status,
            'last_sync_at' => date('Y-m-d H:i:s'),
            'updated_at' => date('Y-m-d H:i:s')
        ];
        
        if ($error) {
            $update_data['sync_error'] = $error;
        } else {
            $update_data['sync_error'] = null;
        }
        
        // Check if mapping exists
        $existing = $this->db->get_where($this->moloni_invoice_table, 
                                       ['perfex_invoice_id' => $perfex_invoice_id])->row_array();
        
        if ($existing) {
            $this->db->where('perfex_invoice_id', $perfex_invoice_id);
            return $this->db->update($this->moloni_invoice_table, $update_data);
        } else {
            $update_data['perfex_invoice_id'] = $perfex_invoice_id;
            $update_data['created_at'] = date('Y-m-d H:i:s');
            return $this->db->insert($this->moloni_invoice_table, $update_data);
        }
    }
    
    /**
     * Get invoice line items with product mapping
     * 
     * @param int $invoice_id Invoice ID
     * @return array Line items with mapping data
     */
    public function get_invoice_items_with_mapping($invoice_id)
    {
        $this->db->select('ii.*, pm.moloni_product_id, pm.mapping_data');
        $this->db->from('tblinvoiceitems ii');
        $this->db->join('tbldeskmoloni_mapping pm', 
                       "pm.perfex_id = ii.rel_id AND pm.entity_type = 'product'", 'left');
        $this->db->where('ii.rel_type', 'invoice');
        $this->db->where('ii.rel_id', $invoice_id);
        $this->db->order_by('ii.item_order', 'ASC');
        
        $query = $this->db->get();
        return $query->result_array();
    }
    
    /**
     * Calculate invoice totals for validation
     * 
     * @param int $invoice_id Invoice ID
     * @return array Invoice totals
     */
    public function calculate_invoice_totals($invoice_id)
    {
        $invoice = $this->get_invoice_with_moloni_data($invoice_id);
        $items = $this->get_invoice_items_with_mapping($invoice_id);
        
        $subtotal = 0;
        $tax_total = 0;
        $discount_total = 0;
        
        foreach ($items as $item) {
            $line_subtotal = $item['qty'] * $item['rate'];
            $line_discount = 0;
            
            if ($item['item_discount_type'] == 'percent') {
                $line_discount = $line_subtotal * ($item['item_discount'] / 100);
            } else {
                $line_discount = $item['item_discount'];
            }
            
            $line_subtotal_after_discount = $line_subtotal - $line_discount;
            
            // Calculate tax
            $tax_rate = 0;
            if ($item['taxname']) {
                // Get tax rate from tax name
                $tax_rate = $this->get_tax_rate_by_name($item['taxname']);
            }
            
            $line_tax = $line_subtotal_after_discount * ($tax_rate / 100);
            
            $subtotal += $line_subtotal;
            $discount_total += $line_discount;
            $tax_total += $line_tax;
        }
        
        // Apply invoice-level discount
        if ($invoice['discount_percent'] > 0) {
            $additional_discount = $subtotal * ($invoice['discount_percent'] / 100);
            $discount_total += $additional_discount;
        } else if ($invoice['discount_total'] > 0) {
            $discount_total += $invoice['discount_total'];
        }
        
        $total = $subtotal - $discount_total + $tax_total;
        
        return [
            'subtotal' => round($subtotal, 2),
            'discount_total' => round($discount_total, 2),
            'tax_total' => round($tax_total, 2),
            'total' => round($total, 2),
            'currency' => $invoice['currency_name'] ?? get_base_currency()->name
        ];
    }
    
    /**
     * Get tax rate by tax name
     * 
     * @param string $tax_name Tax name
     * @return float Tax rate
     */
    private function get_tax_rate_by_name($tax_name)
    {
        $this->db->select('taxrate');
        $this->db->from('tbltaxes');
        $this->db->where('name', $tax_name);
        
        $query = $this->db->get();
        $result = $query->row_array();
        
        return $result ? (float) $result['taxrate'] : 0;
    }
    
    /**
     * Validate invoice data for Moloni sync
     * 
     * @param array $invoice Invoice data
     * @return array Validation result
     */
    public function validate_for_moloni_sync($invoice)
    {
        $issues = [];
        $warnings = [];
        
        // Required fields validation
        if (empty($invoice['clientid'])) {
            $issues[] = 'Invoice must have a valid client';
        }
        
        if (empty($invoice['date'])) {
            $issues[] = 'Invoice must have a valid date';
        }
        
        if (empty($invoice['number'])) {
            $issues[] = 'Invoice must have a number';
        }
        
        // Invoice items validation
        $items = $this->get_invoice_items_with_mapping($invoice['id']);
        if (empty($items)) {
            $issues[] = 'Invoice must have at least one line item';
        }
        
        // Client mapping validation
        $this->load->model('desk_moloni/desk_moloni_mapping_model', 'mapping_model');
        $client_mapping = $this->mapping_model->get_mapping('client', $invoice['clientid']);
        if (!$client_mapping) {
            $warnings[] = 'Client is not mapped to Moloni - will attempt auto-mapping';
        }
        
        // Product mapping validation
        $unmapped_products = 0;
        foreach ($items as $item) {
            if (empty($item['moloni_product_id'])) {
                $unmapped_products++;
            }
        }
        
        if ($unmapped_products > 0) {
            $warnings[] = "{$unmapped_products} product(s) not mapped to Moloni";
        }
        
        // Currency validation
        if (empty($invoice['currency_name'])) {
            $warnings[] = 'Invoice currency not specified - will use base currency';
        }
        
        // Status validation
        if ($invoice['status'] != 2) { // Status 2 = Sent
            $warnings[] = 'Invoice status is not "Sent" - may not be ready for sync';
        }
        
        return [
            'is_valid' => empty($issues),
            'issues' => $issues,
            'warnings' => $warnings,
            'items_count' => count($items),
            'total_amount' => $invoice['total'] ?? 0
        ];
    }
    
    /**
     * Get invoice sync statistics
     * 
     * @param string $period Period for statistics
     * @return array Sync statistics
     */
    public function get_sync_statistics($period = '30days')
    {
        $date_condition = '';
        switch ($period) {
            case '7days':
                $date_condition = "DATE(mi.created_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)";
                break;
            case '30days':
                $date_condition = "DATE(mi.created_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)";
                break;
            case '90days':
                $date_condition = "DATE(mi.created_at) >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)";
                break;
            default:
                $date_condition = "1=1";
        }
        
        // Overall statistics
        $overall_query = "
            SELECT 
                COUNT(*) as total_invoices,
                COUNT(mi.id) as mapped_invoices,
                SUM(CASE WHEN mi.sync_status = 'synced' THEN 1 ELSE 0 END) as synced_invoices,
                SUM(CASE WHEN mi.sync_status = 'failed' THEN 1 ELSE 0 END) as failed_invoices,
                SUM(CASE WHEN mi.sync_status = 'pending' THEN 1 ELSE 0 END) as pending_invoices,
                AVG(i.total) as avg_invoice_amount
            FROM {$this->table} i 
            LEFT JOIN {$this->moloni_invoice_table} mi ON mi.perfex_invoice_id = i.id
            WHERE {$date_condition}
        ";
        
        $overall_stats = $this->db->query($overall_query)->row_array();
        
        // Daily statistics
        $daily_query = "
            SELECT 
                DATE(mi.created_at) as sync_date,
                COUNT(*) as invoices_synced,
                SUM(CASE WHEN mi.sync_status = 'synced' THEN 1 ELSE 0 END) as successful_syncs,
                SUM(CASE WHEN mi.sync_status = 'failed' THEN 1 ELSE 0 END) as failed_syncs
            FROM {$this->moloni_invoice_table} mi
            WHERE {$date_condition}
            GROUP BY DATE(mi.created_at)
            ORDER BY sync_date DESC
            LIMIT 30
        ";
        
        $daily_stats = $this->db->query($daily_query)->result_array();
        
        return [
            'period' => $period,
            'overall' => $overall_stats,
            'daily' => $daily_stats,
            'sync_rate' => $overall_stats['total_invoices'] > 0 ? 
                          round(($overall_stats['synced_invoices'] / $overall_stats['total_invoices']) * 100, 2) : 0
        ];
    }
    
    /**
     * Get invoices with sync errors
     * 
     * @param int $limit Number of records to return
     * @return array Invoices with errors
     */
    public function get_sync_errors($limit = 50)
    {
        $this->db->select('i.id, i.number, i.date, i.clientid, i.total, mi.sync_error, mi.last_sync_at');
        $this->db->from("{$this->table} i");
        $this->db->join("{$this->moloni_invoice_table} mi", 'mi.perfex_invoice_id = i.id');
        $this->db->where('mi.sync_status', 'failed');
        $this->db->where('mi.sync_error IS NOT NULL');
        $this->db->order_by('mi.last_sync_at', 'DESC');
        $this->db->limit($limit);
        
        $query = $this->db->get();
        return $query->result_array();
    }
    
    /**
     * Mark invoice for re-sync
     * 
     * @param int $invoice_id Invoice ID
     * @return bool Success status
     */
    public function mark_for_resync($invoice_id)
    {
        $update_data = [
            'sync_status' => 'pending',
            'sync_error' => null,
            'updated_at' => date('Y-m-d H:i:s')
        ];
        
        $this->db->where('perfex_invoice_id', $invoice_id);
        return $this->db->update($this->moloni_invoice_table, $update_data);
    }
    
    /**
     * Clean up old sync records
     * 
     * @param int $days_old Records older than this many days
     * @return int Number of records cleaned
     */
    public function cleanup_old_sync_records($days_old = 90)
    {
        $this->db->where('sync_status', 'synced');
        $this->db->where('created_at <', date('Y-m-d H:i:s', strtotime("-{$days_old} days")));
        
        // Keep the mapping but clear the detailed data
        $update_data = [
            'moloni_data' => null,
            'sync_error' => null
        ];
        
        $this->db->update($this->moloni_invoice_table, $update_data);
        
        return $this->db->affected_rows();
    }
}