<?php

class FinancialReport {
    private $conn;
    private $tableCache = [];
    private $columnCache = [];

    public function __construct($conn) { $this->conn = $conn; }

    private function hasTable($table) {
        if (!array_key_exists($table, $this->tableCache)) {
            try {
                $this->conn->query("SELECT 1 FROM `{$table}` LIMIT 0");
                $this->tableCache[$table] = true;
            } catch (Throwable $error) {
                $this->tableCache[$table] = false;
            }
        }
        return $this->tableCache[$table];
    }

    private function hasColumn($table, $column) {
        $key = $table.'.'.$column;
        if (!array_key_exists($key, $this->columnCache)) {
            try {
                $this->conn->query("SELECT `{$column}` FROM `{$table}` LIMIT 0");
                $this->columnCache[$key] = true;
            } catch (Throwable $error) {
                $this->columnCache[$key] = false;
            }
        }
        return $this->columnCache[$key];
    }

    private function rows($sql, array $params) {
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function monthly($month, $branchId, $grossProfitRate) {
        $date = DateTime::createFromFormat('!Y-m', (string)$month);
        if (!$date || $date->format('Y-m') !== $month) throw new InvalidArgumentException('Invalid month.');
        $start = $date->format('Y-m-d');
        $end = (clone $date)->modify('first day of next month')->format('Y-m-d');
        $params = [':start'=>$start, ':end'=>$end];
        $branchSql = '';
        if ($branchId) { $branchSql = ' AND si.branch_id=:branch'; $params[':branch']=(int)$branchId; }
        $payment = $this->hasColumn('sold_items','payment_method') ? "COALESCE(si.payment_method,'cash')" : "'cash'";
        $incomeOnly = $this->hasColumn('sold_items','is_ims_income') ? ' AND COALESCE(si.is_ims_income,1)=1' : '';
        $sales = $this->rows("SELECT si.sale_id,si.sold_at,si.transaction_id,si.customer_name,si.total_price,
            {$payment} payment_method,b.branch_id,b.branch_name,i.item_name,i.item_details,u.username
            FROM sold_items si LEFT JOIN items i ON i.item_id=si.item_id
            LEFT JOIN branches b ON b.branch_id=si.branch_id LEFT JOIN users u ON u.user_id=si.sold_by
            WHERE si.sold_at>=:start AND si.sold_at<:end{$incomeOnly}{$branchSql}
            ORDER BY si.sold_at,si.sale_id", $params);

        $transactions = [];
        foreach ($sales as $sale) {
            $key = $sale['transaction_id'] ?: 'SALE-'.$sale['sale_id'];
            if (!isset($transactions[$key])) $transactions[$key] = [
                'reference'=>$key,'date'=>$sale['sold_at'],'branch_id'=>$sale['branch_id'],
                'branch_name'=>$sale['branch_name'] ?: 'Unassigned','customer'=>$sale['customer_name'] ?: 'Walk-in',
                'payment_method'=>$sale['payment_method'] ?: 'cash','items'=>[],'amount'=>0
            ];
            $transactions[$key]['items'][] = trim((string)($sale['item_name'] ?: 'Item') . (!empty($sale['item_details']) ? ' - ' . $sale['item_details'] : ''));
            $transactions[$key]['amount'] += (float)$sale['total_price'];
        }
        $transactions = array_values($transactions);

        $expenseReport = $this->expenseData($start, $end, $branchId);
        $expenses = $expenseReport['details'];
        $receipts = [];
        $deferred = ['credit','laybuy','lay-by','lay by','order','unpaid'];
        foreach ($transactions as $tx) {
            $method = strtolower(trim($tx['payment_method']));
            $isDeferred = false;
            foreach ($deferred as $term) {
                if (strpos($method, $term) !== false) { $isDeferred = true; break; }
            }
            if (!$isDeferred) {
                $receipts[] = ['type'=>'Sale receipt','reference'=>$tx['reference'],'date'=>$tx['date'],
                    'branch_id'=>$tx['branch_id'],'branch_name'=>$tx['branch_name'],'method'=>$tx['payment_method'],
                    'description'=>$tx['customer'],'amount'=>$tx['amount']];
            }
        }
        $this->addOptionalReceipts($receipts, 'laybuy_items', 'Lay-by payment', 'id', 'payment_date', 'body_amount', 'notes', 'record_id', $start, $end, $branchId);
        $this->addOptionalReceipts($receipts, 'credit_payments', 'Credit repayment', 'payment_id', 'payment_date', 'amount', 'notes', 'credit_id', $start, $end, $branchId);
        $this->addOptionalReceipts($receipts, 'order_payments', 'Order payment', 'payment_id', 'payment_date', 'amount', 'notes', 'order_id', $start, $end, $branchId);
        usort($receipts, function($a,$b){ return strcmp($a['date'],$b['date']); });

        $depositParams = [':start'=>$start, ':end'=>$end];
        $depositBranch = '';
        if ($branchId) { $depositBranch=' AND d.branch_id=:branch'; $depositParams[':branch']=(int)$branchId; }
        $deposits = [];
        if ($this->hasTable('deposits')) {
            try {
                $deposits = $this->rows("SELECT d.id,d.deposit_date,d.amount,d.notes,b.branch_name
                    FROM deposits d LEFT JOIN branches b ON b.branch_id=d.branch_id
                    WHERE d.deposit_date>=:start AND d.deposit_date<:end{$depositBranch} ORDER BY d.deposit_date,d.id", $depositParams);
            } catch (Throwable $error) {
                error_log('Financial report skipped deposits: '.$error->getMessage());
            }
        }

        return $this->assemble($month, (float)$grossProfitRate, $transactions, $expenses, $receipts, $deposits, $expenseReport['categories']);
    }

    private function expenseData($start, $end, $branchId) {
        $params = [':start'=>$start, ':end'=>$end];
        $branchSql = '';
        if ($branchId) { $branchSql=' AND e.branch_id=:branch'; $params[':branch']=(int)$branchId; }
        $from = " FROM operational_expenses e
            LEFT JOIN expense_categories c ON c.category_id=e.expense_category_id
            LEFT JOIN branches b ON b.branch_id=e.branch_id
            LEFT JOIN users u ON u.user_id=e.user_id
            WHERE e.expense_date>=:start AND e.expense_date<:end{$branchSql}";
        $details = $this->rows(
            'SELECT e.expense_id,e.expense_date,e.description,e.amount,e.created_at,'
            .'c.category_name,b.branch_name,u.username'.$from
            .' ORDER BY e.expense_date,e.expense_id',
            $params
        );
        $categories = $this->rows(
            "SELECT COALESCE(c.category_name,'Uncategorised') label,"
            .'COUNT(*) expense_count,SUM(e.amount) total_amount'.$from
            .' GROUP BY e.expense_category_id,c.category_name ORDER BY total_amount DESC',
            $params
        );
        return ['details'=>$details,'categories'=>$categories];
    }

    private function addOptionalReceipts(&$receipts, $table, $type, $id, $date, $amount, $description, $reference, $start, $end, $branchId) {
        if (!$this->hasTable($table)) return;
        $method = $this->hasColumn($table,'payment_method') ? "COALESCE(p.payment_method,'cash')" : "'cash'";
        $params = [':start'=>$start, ':end'=>$end];
        $branchSql = '';
        if ($branchId) { $branchSql=' AND p.branch_id=:branch'; $params[':branch']=(int)$branchId; }
        try {
            $rows = $this->rows("SELECT p.`{$id}` id,p.`{$date}` receipt_date,p.`{$amount}` amount,
                p.`{$description}` description,p.`{$reference}` reference_id,p.branch_id,{$method} method,
                b.branch_name FROM `{$table}` p LEFT JOIN branches b ON b.branch_id=p.branch_id
                WHERE p.`{$date}`>=:start AND p.`{$date}`<:end{$branchSql}", $params);
        } catch (Throwable $error) {
            error_log("Financial report skipped {$table}: ".$error->getMessage());
            return;
        }
        foreach ($rows as $row) $receipts[] = ['type'=>$type,'reference'=>strtoupper(substr($type,0,3)).'-'.$row['reference_id'],
            'date'=>$row['receipt_date'],'branch_id'=>$row['branch_id'],'branch_name'=>$row['branch_name'] ?: 'Unassigned',
            'method'=>$row['method'],'description'=>$row['description'] ?: $type,'amount'=>(float)$row['amount']];
    }

    private function assemble($month, $rate, $sales, $expenses, $receipts, $deposits, $byCategory) {
        $revenue=array_sum(array_column($sales,'amount')); $expenseTotal=array_sum(array_column($expenses,'amount'));
        $cashIn=array_sum(array_column($receipts,'amount')); $depositTotal=array_sum(array_column($deposits,'amount'));
        $grossProfit=$revenue*$rate; $costOfSales=$revenue-$grossProfit; $netProfit=$grossProfit-$expenseTotal;
        $branches=[]; $payments=[]; $daily=[];
        $add=function(&$bucket,$key,$field,$amount){ if(!isset($bucket[$key]))$bucket[$key]=[]; $bucket[$key][$field]=($bucket[$key][$field]??0)+(float)$amount; };
        foreach($sales as $row){$name=$row['branch_name'];$add($branches,$name,'revenue',$row['amount']);$add($daily,substr($row['date'],0,10),'revenue',$row['amount']);}
        foreach($expenses as $row){$name=$row['branch_name']?:'Unassigned';$add($branches,$name,'expenses',$row['amount']);$add($daily,substr($row['expense_date'],0,10),'expenses',$row['amount']);}
        foreach($receipts as $row){$add($branches,$row['branch_name'],'cash_in',$row['amount']);$add($payments,ucwords($row['method']?:'Unspecified'),'amount',$row['amount']);$add($daily,substr($row['date'],0,10),'cash_in',$row['amount']);}
        foreach($branches as &$row){$row+=['revenue'=>0,'expenses'=>0,'cash_in'=>0];$row['gross_profit']=$row['revenue']*$rate;$row['net_profit']=$row['gross_profit']-$row['expenses'];}
        unset($row); ksort($daily);
        foreach($daily as &$row){$row+=['revenue'=>0,'expenses'=>0,'cash_in'=>0];$row['net_cash']=$row['cash_in']-$row['expenses'];} unset($row);
        return ['month'=>$month,'rate'=>$rate,'revenue'=>$revenue,'cost_of_sales'=>$costOfSales,'gross_profit'=>$grossProfit,
            'expenses_total'=>$expenseTotal,'net_profit'=>$netProfit,'profit_margin'=>$revenue?($netProfit/$revenue*100):0,
            'cash_in'=>$cashIn,'cash_out'=>$expenseTotal,'net_cash'=>$cashIn-$expenseTotal,'bank_deposits'=>$depositTotal,
            'sales'=>$sales,'expenses'=>$expenses,'receipts'=>$receipts,'deposits'=>$deposits,
            'expense_categories'=>$byCategory,'branches'=>$branches,'payments'=>$payments,'daily'=>$daily];
    }
}
