<?php

namespace Modules\Reports\Models;

use core\Model;
use core\Database;
use core\Auth;

class Report extends Model
{
    protected string $table = '';

    private function branchFilter(string $alias = ''): string
    {
        $branchId = Auth::branchId();
        if (!$branchId) {
            return '';
        }
        $a = $alias ? $alias . '.' : '';
        return " AND {$a}branch_id = " . (int)$branchId;
    }

    public function getDailySales(string $date): array
    {
        $db = Database::getInstance();
        $sql = "SELECT i.invoice_number, i.total, i.discount, i.net_total, i.payment_method, i.status,
                       i.created_at, c.first_name as customer_first_name, c.last_name as customer_last_name,
                       s.name as service_name, e.first_name as employee_first_name, e.last_name as employee_last_name
                FROM invoices i
                LEFT JOIN customers c ON i.customer_id = c.id
                LEFT JOIN invoice_items ii ON i.id = ii.invoice_id
                LEFT JOIN services s ON ii.service_id = s.id
                LEFT JOIN employees e ON i.employee_id = e.id
                WHERE DATE(i.created_at) = :date" . $this->branchFilter('i') . "
                ORDER BY i.created_at DESC";
        return $db->query($sql, [':date' => $date]);
    }

    public function getDailySalesSummary(string $date): array
    {
        $db = Database::getInstance();
        $sql = "SELECT COUNT(*) as total_invoices, 
                       COALESCE(SUM(total), 0) as total_sales,
                       COALESCE(SUM(discount), 0) as total_discounts,
                       COALESCE(SUM(net_total), 0) as net_total
                FROM invoices 
                WHERE DATE(created_at) = :date" . $this->branchFilter() . "
                AND status IN ('paid', 'partial')";
        $result = $db->query($sql, [':date' => $date]);
        return $result ? (array)$result[0] : ['total_invoices' => 0, 'total_sales' => 0, 'total_discounts' => 0, 'net_total' => 0];
    }

    public function getMonthlySales(string $month, string $year): array
    {
        $db = Database::getInstance();
        $sql = "SELECT DATE(created_at) as date, COUNT(*) as total_invoices,
                       COALESCE(SUM(total), 0) as total_sales,
                       COALESCE(SUM(discount), 0) as total_discounts,
                       COALESCE(SUM(net_total), 0) as net_total,
                       payment_method
                FROM invoices
                WHERE MONTH(created_at) = :month AND YEAR(created_at) = :year" . $this->branchFilter() . "
                AND status IN ('paid', 'partial')
                GROUP BY DATE(created_at), payment_method
                ORDER BY DATE(created_at) ASC";
        return $db->query($sql, [':month' => $month, ':year' => $year]);
    }

    public function getYearlySales(string $year): array
    {
        $db = Database::getInstance();
        $sql = "SELECT DATE_FORMAT(created_at, '%Y-%m') as month,
                       COUNT(*) as total_invoices,
                       COALESCE(SUM(total), 0) as total_sales,
                       COALESCE(SUM(discount), 0) as total_discounts,
                       COALESCE(SUM(net_total), 0) as net_total
                FROM invoices
                WHERE YEAR(created_at) = :year" . $this->branchFilter() . "
                AND status IN ('paid', 'partial')
                GROUP BY DATE_FORMAT(created_at, '%Y-%m')
                ORDER BY month ASC";
        return $db->query($sql, [':year' => $year]);
    }

    public function getProfitLoss(string $startDate, string $endDate): array
    {
        $db = Database::getInstance();
        $bf = $this->branchFilter();

        $revenue = $db->query(
            "SELECT 'Revenue' as category, 'Sales Income' as type, COALESCE(SUM(net_total), 0) as amount
             FROM invoices WHERE DATE(created_at) BETWEEN :start1 AND :end1 AND status IN ('paid', 'partial')" . $bf,
            [':start1' => $startDate, ':end1' => $endDate]
        );

        $expenses = $db->query(
            "SELECT 'Expense' as category, ec.name as type, COALESCE(SUM(e.amount), 0) as amount
             FROM expenses e
             LEFT JOIN expense_categories ec ON e.category_id = ec.id
             WHERE DATE(e.created_at) BETWEEN :start2 AND :end2" . str_replace('branch_id', 'e.branch_id', $bf) . "
             GROUP BY ec.name ORDER BY amount DESC",
            [':start2' => $startDate, ':end2' => $endDate]
        );

        $otherIncome = $db->query(
            "SELECT 'Other Income' as category, ic.name as type, COALESCE(SUM(i.amount), 0) as amount
             FROM income i
             LEFT JOIN income_categories ic ON i.category_id = ic.id
             WHERE DATE(i.created_at) BETWEEN :start3 AND :end3" . str_replace('branch_id', 'i.branch_id', $bf) . "
             GROUP BY ic.name ORDER BY amount DESC",
            [':start3' => $startDate, ':end3' => $endDate]
        );

        return [
            'revenue' => $revenue,
            'expenses' => $expenses,
            'other_income' => $otherIncome,
            'start_date' => $startDate,
            'end_date' => $endDate,
        ];
    }

    public function getInventoryReport(?int $categoryId = null): array
    {
        $db = Database::getInstance();
        $sql = "SELECT p.id, p.name, p.sku, p.current_stock as quantity, p.reorder_level, p.cost_price as unit_cost,
                       (p.current_stock * p.cost_price) as total_value,
                       c.name as category_name
                FROM products p
                LEFT JOIN product_categories c ON p.category_id = c.id
                WHERE p.deleted_at IS NULL";
        $bindings = [];
        if ($categoryId) {
            $sql .= " AND p.category_id = :category_id";
            $bindings[':category_id'] = $categoryId;
        }
        if ($branchId = Auth::branchId()) {
            $sql .= " AND p.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $sql .= " ORDER BY p.name ASC";
        return $db->query($sql, $bindings);
    }

    public function getEmployeeReport(string $startDate, string $endDate, ?int $employeeId = null): array
    {
        $db = Database::getInstance();
        $sql = "SELECT e.id, e.first_name, e.last_name, e.email, e.phone,
                       COUNT(DISTINCT a.id) as total_appointments,
                       COUNT(DISTINCT aps.id) as total_services,
                       COALESCE(SUM(a.total), 0) as total_revenue,
                       COALESCE(SUM(c.commission_amount), 0) as total_commission,
                       COALESCE(AVG(a.rating), 0) as avg_rating
                FROM employees e
                LEFT JOIN appointment_services aps ON e.id = aps.employee_id
                LEFT JOIN appointments a ON aps.appointment_id = a.id AND a.deleted_at IS NULL
                   AND DATE(a.start_time) BETWEEN :start1 AND :end1
                LEFT JOIN employee_commissions c ON e.id = c.employee_id 
                   AND DATE(c.created_at) BETWEEN :start2 AND :end2
                WHERE e.deleted_at IS NULL";
        $bindings = [':start1' => $startDate, ':end1' => $endDate, ':start2' => $startDate, ':end2' => $endDate];
        if ($employeeId) {
            $sql .= " AND e.id = :employee_id";
            $bindings[':employee_id'] = $employeeId;
        }
        if ($branchId = Auth::branchId()) {
            $sql .= " AND e.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $sql .= " GROUP BY e.id, e.first_name, e.last_name, e.email, e.phone
                  ORDER BY total_revenue DESC";
        return $db->query($sql, $bindings);
    }

    public function getCustomerReport(string $startDate, string $endDate): array
    {
        $db = Database::getInstance();
        $sql = "SELECT c.id, c.first_name, c.last_name, c.email, c.phone, c.created_at as registration_date,
                       COUNT(DISTINCT a.id) as total_visits,
                       COALESCE(SUM(a.total), 0) as total_spent,
                       MAX(a.start_time) as last_visit
                FROM customers c
                LEFT JOIN appointments a ON c.id = a.customer_id AND a.deleted_at IS NULL
                   AND a.status = 'completed'
                   AND DATE(a.start_time) BETWEEN :start1 AND :end1
                WHERE c.deleted_at IS NULL";
        $bindings = [':start1' => $startDate, ':end1' => $endDate];
        if ($branchId = Auth::branchId()) {
            $sql .= " AND c.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $sql .= " GROUP BY c.id, c.first_name, c.last_name, c.email, c.phone, c.created_at
                  ORDER BY total_spent DESC";
        return $db->query($sql, $bindings);
    }

    public function getAppointmentReport(string $startDate, string $endDate, ?string $status = null): array
    {
        $db = Database::getInstance();
        $sql = "SELECT a.id, a.start_time, a.end_time, a.status, a.total, a.rating,
                       c.first_name as customer_first_name, c.last_name as customer_last_name, c.phone as customer_phone,
                       GROUP_CONCAT(DISTINCT s.name SEPARATOR ', ') as service_name,
                       GROUP_CONCAT(DISTINCT CONCAT(e.first_name, ' ', e.last_name) SEPARATOR ', ') as employee_name
                FROM appointments a
                LEFT JOIN customers c ON a.customer_id = c.id
                LEFT JOIN appointment_services aps ON a.id = aps.appointment_id
                LEFT JOIN services s ON aps.service_id = s.id
                LEFT JOIN employees e ON aps.employee_id = e.id
                WHERE DATE(a.start_time) BETWEEN :start1 AND :end1 AND a.deleted_at IS NULL";
        $bindings = [':start1' => $startDate, ':end1' => $endDate];
        if ($status) {
            $sql .= " AND a.status = :status";
            $bindings[':status'] = $status;
        }
        if ($branchId = Auth::branchId()) {
            $sql .= " AND a.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $sql .= " GROUP BY a.id, a.start_time, a.end_time, a.status, a.total, a.rating,
                         c.first_name, c.last_name, c.phone
                  ORDER BY a.start_time DESC";
        return $db->query($sql, $bindings);
    }

    public function getCashFlow(string $startDate, string $endDate): array
    {
        $db = Database::getInstance();
        $bf = $this->branchFilter();

        $inflow = $db->query(
            "SELECT DATE(created_at) as date, 'Income' as type, 'Sales' as category, 
                    payment_method, net_total as amount, invoice_number as reference
             FROM invoices 
             WHERE DATE(created_at) BETWEEN :start1 AND :end1 AND status IN ('paid', 'partial')" . $bf,
            [':start1' => $startDate, ':end1' => $endDate]
        );

        $outflow = $db->query(
            "SELECT DATE(created_at) as date, 'Expense' as type, ec.name as category,
                    e.payment_method, e.amount, e.reference
             FROM expenses e
             LEFT JOIN expense_categories ec ON e.category_id = ec.id
             WHERE DATE(e.created_at) BETWEEN :start2 AND :end2" . str_replace('branch_id', 'e.branch_id', $bf),
            [':start2' => $startDate, ':end2' => $endDate]
        );

        return [
            'inflow' => $inflow,
            'outflow' => $outflow,
            'start_date' => $startDate,
            'end_date' => $endDate,
        ];
    }

    public function getExpenseReport(string $startDate, string $endDate, ?int $categoryId = null): array
    {
        $db = Database::getInstance();
        $sql = "SELECT e.id, e.description, e.amount, e.payment_method, e.reference, e.created_at as date,
                       ec.name as category_name
                FROM expenses e
                LEFT JOIN expense_categories ec ON e.category_id = ec.id
                WHERE DATE(e.created_at) BETWEEN :start1 AND :end1";
        $bindings = [':start1' => $startDate, ':end1' => $endDate];
        if ($categoryId) {
            $sql .= " AND e.category_id = :category_id";
            $bindings[':category_id'] = $categoryId;
        }
        if ($branchId = Auth::branchId()) {
            $sql .= " AND e.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $sql .= " ORDER BY e.created_at DESC";
        return $db->query($sql, $bindings);
    }

    public function getPurchaseReport(string $startDate, string $endDate, ?int $supplierId = null): array
    {
        $db = Database::getInstance();
        $sql = "SELECT po.id, po.po_number, po.order_date, po.total, po.status,
                       s.name as supplier_name,
                       (SELECT COUNT(*) FROM purchase_order_items WHERE purchase_order_id = po.id) as total_items
                FROM purchase_orders po
                LEFT JOIN suppliers s ON po.supplier_id = s.id
                WHERE DATE(po.order_date) BETWEEN :start1 AND :end1";
        $bindings = [':start1' => $startDate, ':end1' => $endDate];
        if ($supplierId) {
            $sql .= " AND po.supplier_id = :supplier_id";
            $bindings[':supplier_id'] = $supplierId;
        }
        if ($branchId = Auth::branchId()) {
            $sql .= " AND po.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $sql .= " ORDER BY po.order_date DESC";
        return $db->query($sql, $bindings);
    }

    public function getStockMovement(string $startDate, string $endDate, ?int $productId = null): array
    {
        $db = Database::getInstance();
        $sql = "SELECT sm.id, sm.quantity, sm.type, sm.reference, sm.created_at,
                       i.name as product_name,
                       u.first_name as user_name
                FROM stock_movements sm
                LEFT JOIN products i ON sm.product_id = i.id
                LEFT JOIN users u ON sm.user_id = u.id
                WHERE DATE(sm.created_at) BETWEEN :start1 AND :end1";
        $bindings = [':start1' => $startDate, ':end1' => $endDate];
        if ($productId) {
            $sql .= " AND sm.product_id = :product_id";
            $bindings[':product_id'] = $productId;
        }
        if ($branchId = Auth::branchId()) {
            $sql .= " AND sm.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $sql .= " ORDER BY sm.created_at DESC";
        return $db->query($sql, $bindings);
    }

    public function getTopServices(string $startDate, string $endDate, int $limit = 10): array
    {
        $db = Database::getInstance();
        $sql = "SELECT s.id, s.name, s.price,
                       COUNT(DISTINCT a.id) as total_count,
                       COALESCE(SUM(ai.total), 0) as total_revenue,
                       AVG(s.price) as avg_price
                FROM appointment_services ai
                INNER JOIN services s ON ai.service_id = s.id
                INNER JOIN appointments a ON ai.appointment_id = a.id
                WHERE DATE(a.start_time) BETWEEN :start1 AND :end1
                AND a.deleted_at IS NULL AND a.status = 'completed'";
        $bindings = [':start1' => $startDate, ':end1' => $endDate];
        if ($branchId = Auth::branchId()) {
            $sql .= " AND a.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $sql .= " GROUP BY s.id, s.name, s.price ORDER BY total_revenue DESC LIMIT " . (int)$limit;
        return $db->query($sql, $bindings);
    }

    public function getTopEmployees(string $startDate, string $endDate, int $limit = 10): array
    {
        $db = Database::getInstance();
        $sql = "SELECT e.id, e.first_name, e.last_name,
                       COUNT(DISTINCT a.id) as total_appointments,
                       COUNT(DISTINCT aps.id) as total_services,
                       COALESCE(SUM(a.total), 0) as total_revenue,
                       COALESCE(SUM(c.commission_amount), 0) as total_commission
                FROM employees e
                LEFT JOIN appointment_services aps ON e.id = aps.employee_id
                LEFT JOIN appointments a ON aps.appointment_id = a.id AND a.deleted_at IS NULL
                   AND a.status = 'completed'
                   AND DATE(a.start_time) BETWEEN :start1 AND :end1
                LEFT JOIN employee_commissions c ON e.id = c.employee_id
                   AND DATE(c.created_at) BETWEEN :start2 AND :end2
                WHERE e.deleted_at IS NULL";
        $bindings = [':start1' => $startDate, ':end1' => $endDate, ':start2' => $startDate, ':end2' => $endDate];
        if ($branchId = Auth::branchId()) {
            $sql .= " AND e.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $sql .= " GROUP BY e.id, e.first_name, e.last_name
                  ORDER BY total_revenue DESC LIMIT " . (int)$limit;
        return $db->query($sql, $bindings);
    }

    public function getTopCustomers(string $startDate, string $endDate, int $limit = 10): array
    {
        $db = Database::getInstance();
        $sql = "SELECT c.id, c.first_name, c.last_name, c.email, c.phone,
                       COUNT(DISTINCT a.id) as total_visits,
                       COALESCE(SUM(a.total), 0) as total_spent,
                       COALESCE(SUM(a.total) / NULLIF(COUNT(DISTINCT a.id), 0), 0) as avg_per_visit,
                       MAX(a.start_time) as last_visit
                FROM customers c
                INNER JOIN appointments a ON c.id = a.customer_id AND a.deleted_at IS NULL
                   AND a.status = 'completed'
                   AND DATE(a.start_time) BETWEEN :start1 AND :end1
                WHERE c.deleted_at IS NULL";
        $bindings = [':start1' => $startDate, ':end1' => $endDate];
        if ($branchId = Auth::branchId()) {
            $sql .= " AND c.branch_id = :branch_id";
            $bindings[':branch_id'] = $branchId;
        }
        $sql .= " GROUP BY c.id, c.first_name, c.last_name, c.email, c.phone
                  ORDER BY total_spent DESC LIMIT " . (int)$limit;
        return $db->query($sql, $bindings);
    }
}
