<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class MonthlyCompanyFinancial extends Model
{
    use HasFactory;

    protected $table = 'monthly_company_financials';

    protected $fillable = [
        'company_id',
        'year_month',
        'revenue',
        'expenses',
        'loan_deductions',
        'net_profit',
        'profit_swept',
        'available_profit',
    ];

    protected $casts = [
        'revenue'          => 'decimal:2',
        'expenses'         => 'decimal:2',
        'loan_deductions'  => 'decimal:2',
        'net_profit'       => 'decimal:2',
        'profit_swept'     => 'decimal:2',
        'available_profit' => 'decimal:2',
    ];

    public function company()
    {
        return $this->belongsTo(Company::class);
    }

    /**
     * Recalculate and store a single month's financials from actual tables.
     */
    public static function recalculateMonth(int $companyId, string|Carbon|null $month = null)
    {
        if (is_string($month)) {
            $month = Carbon::parse($month . '-01');
        }
        if (!$month) {
            $month = now();
        }
        $yearMonth = $month->format('Y-m');
        $monthStart = $month->copy()->startOfMonth();
        $monthEnd = $month->copy()->endOfMonth();

        // Revenue: sum of receivable invoices (money coming in) for this month
        $revenue = 0;
        try {
            $revenue = (float) DB::table('invoices')
                ->where('company_id', $companyId)
                ->where('type', 'receivable')
                ->whereBetween('invoice_date', [$monthStart, $monthEnd])
                ->whereIn('status', ['sent', 'viewed', 'partial', 'paid'])
                ->sum('total_amount');
        } catch (\Exception $e) {}

        // Add loan disbursements to revenue (money received from loans)
        $loanDisbursements = 0;
        try {
            $loanDisbursements = (float) DB::table('intercompany_loans')
                ->where('borrower_company_id', $companyId)
                ->whereBetween('disbursement_date', [$monthStart, $monthEnd])
                ->where('status', 'approved')
                ->sum('principal_amount');
        } catch (\Exception $e) {}
        $revenue += $loanDisbursements;

        // Expenses: sum of non-rejected expenses + payable invoices (money going out) for this month
        $expenses = 0;
        try {
            $expenses = (float) DB::table('expenses')
                ->where('company_id', $companyId)
                ->whereBetween('expense_date', [$monthStart, $monthEnd])
                ->whereNotIn('status', ['rejected', 'cancelled'])
                ->sum('amount');
        } catch (\Exception $e) {}

        // Add payable invoices to expenses
        $payableInvoices = 0;
        try {
            $payableInvoices = (float) DB::table('invoices')
                ->where('company_id', $companyId)
                ->where('type', 'payable')
                ->whereBetween('invoice_date', [$monthStart, $monthEnd])
                ->whereIn('status', ['sent', 'viewed', 'partial', 'paid'])
                ->sum('total_amount');
        } catch (\Exception $e) {}
        $expenses += $payableInvoices;

        // Add loan repayments to expenses (money paid back on loans)
        $loanRepayments = 0;
        try {
            $loanRepayments = (float) DB::table('loan_repayments')
                ->join('intercompany_loans', 'loan_repayments.loan_id', '=', 'intercompany_loans.id')
                ->where('intercompany_loans.borrower_company_id', $companyId)
                ->whereBetween('loan_repayments.paid_at', [$monthStart, $monthEnd])
                ->where('loan_repayments.status', 'paid')
                ->sum('loan_repayments.total_amount');
        } catch (\Exception $e) {}
        $expenses += $loanRepayments;

        // Staff loan repayments: money coming IN when staff repay their loans
        $staffLoanRepayments = 0;
        try {
            $staffLoanRepayments = (float) DB::table('staff_loan_deductions')
                ->join('staff_loans', 'staff_loan_deductions.staff_loan_id', '=', 'staff_loans.id')
                ->where('staff_loans.company_id', $companyId)
                ->whereBetween('staff_loan_deductions.due_date', [$monthStart, $monthEnd])
                ->where('staff_loan_deductions.status', 'deducted')
                ->sum('staff_loan_deductions.deduction_amount');
        } catch (\Exception $e) {}
        $revenue += $staffLoanRepayments;

        // Loan deductions: sum of staff loan deductions marked as deducted for this month
        $loanDeductions = 0;
        try {
            $loanDeductions = (float) DB::table('staff_loan_deductions')
                ->join('staff_loans', 'staff_loan_deductions.staff_loan_id', '=', 'staff_loans.id')
                ->where('staff_loans.company_id', $companyId)
                ->whereBetween('staff_loan_deductions.due_date', [$monthStart, $monthEnd])
                ->where('staff_loan_deductions.status', 'deducted')
                ->sum('staff_loan_deductions.deduction_amount');
        } catch (\Exception $e) {}

        // Profit swept this month
        $profitSwept = 0;
        try {
            $profitSwept = (float) DB::table('profit_sweep_transactions')
                ->where('from_company_id', $companyId)
                ->whereBetween('created_at', [$monthStart, $monthEnd])
                ->sum('actual_amount');
        } catch (\Exception $e) {}

        // No manual overrides - revenue and expenses are calculated purely from invoices and expenses
        $finalRevenue  = $revenue;
        $finalExpenses = $expenses + $loanDeductions;

        $netProfit       = round($finalRevenue - $finalExpenses, 2);
        $availableProfit = round($netProfit - $profitSwept, 2);

        // Try to persist to DB; if table doesn't exist (migration not run), return plain object
        try {
            $record = self::updateOrCreate(
                ['company_id' => $companyId, 'year_month' => $yearMonth],
                [
                    'revenue'          => $finalRevenue,
                    'expenses'         => $finalExpenses,
                    'loan_deductions'  => $loanDeductions,
                    'net_profit'       => $netProfit,
                    'profit_swept'     => $profitSwept,
                    'available_profit' => $availableProfit,
                ]
            );
            return $record;
        } catch (\Exception $e) {
            // Table not found — return plain stdClass object with all properties
            return (object) [
                'company_id'        => $companyId,
                'year_month'        => $yearMonth,
                'revenue'           => $revenue,
                'expenses'          => $expenses,
                'loan_deductions'   => $loanDeductions,
                'net_profit'        => $netProfit,
                'profit_swept'      => $profitSwept,
                'available_profit'  => $availableProfit,
                'id'                => null,
                'created_at'        => null,
                'updated_at'        => null,
            ];
        }
    }

    /**
     * Get the current month's financial record (recalculates first).
     */
    public static function currentMonth(int $companyId): object
    {
        $yearMonth = now()->format('Y-m');
        return self::recalculateMonth($companyId, $yearMonth);
    }
}
