<?php

declare(strict_types=1);

namespace App\Modules\Reporting\Application\Services;

use PDO;

final class PurchaseReportService
{
    public function __construct(private PDO $pdo)
    {
    }

    public function ensureSchema(): void
    {
        if (!table_exists($this->pdo, 'purchase_invoices') || !table_exists($this->pdo, 'purchase_invoice_lines')) {
            throw new \RuntimeException('مطلوب تشغيل ملف التحديث: sql/migrate_v1_23_purchases.sql');
        }
    }

    /** @return array{date_from:string,date_to:string,party_id:int,warehouse_id:int,warehouses:array<int,array<string,mixed>>,supplier:?array<string,mixed>,rows:array<int,array<string,mixed>>,summary:array<string,float|int>,supplier_name:string,warehouse_name:string} */
    public function period(string $dateFrom, string $dateTo, int $partyId, int $warehouseId): array
    {
        [$dateFrom, $dateTo] = $this->normalizeDates($dateFrom, $dateTo);
        $partyId = max(0, $partyId);
        $warehouseId = max(0, $warehouseId);

        $warehouses = $this->fetchWarehouses();
        $supplier = $this->loadParty($partyId);
        $warehouseName = $warehouseId > 0 ? $this->loadWarehouseName($warehouseId) : '';

        $params = [$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59'];
        $where = "WHERE pi.status='POSTED' AND pi.inv_date >= ? AND pi.inv_date <= ?";
        if ($partyId > 0) {
            $where .= ' AND COALESCE(pi.party_id, pi.supplier_id)=?';
            $params[] = $partyId;
        }
        if ($warehouseId > 0) {
            $where .= ' AND pi.warehouse_id=?';
            $params[] = $warehouseId;
        }

        $sql = "SELECT pi.*, s.name supplier_name, w.name warehouse_name
                FROM purchase_invoices pi
                JOIN parties s ON s.id = COALESCE(pi.party_id, pi.supplier_id)
                JOIN warehouses w ON w.id = pi.warehouse_id
                {$where}
                ORDER BY pi.inv_date DESC, pi.id DESC";
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        $rows = $st->fetchAll() ?: [];

        $summary = [
            'count' => 0,
            'subtotal' => 0.0,
            'discount' => 0.0,
            'net' => 0.0,
            'vat' => 0.0,
            'wht' => 0.0,
            'total' => 0.0,
        ];
        foreach ($rows as $row) {
            $summary['count']++;
            $summary['subtotal'] += (float) ($row['subtotal'] ?? 0);
            $summary['discount'] += (float) ($row['discount_amount'] ?? 0);
            $summary['net'] += (float) ($row['subtotal'] ?? 0) - (float) ($row['discount_amount'] ?? 0);
            $summary['vat'] += (float) ($row['tax_amount'] ?? 0);
            $summary['wht'] += (float) ($row['wht_amount'] ?? 0);
            $summary['total'] += (float) ($row['total'] ?? 0);
        }

        return [
            'date_from' => $dateFrom,
            'date_to' => $dateTo,
            'party_id' => $partyId,
            'warehouse_id' => $warehouseId,
            'warehouses' => $warehouses,
            'supplier' => $supplier,
            'rows' => $rows,
            'summary' => $summary,
            'supplier_name' => (string) ($supplier['name'] ?? ''),
            'warehouse_name' => $warehouseName,
        ];
    }

    /** @return array{date_from:string,date_to:string,warehouse_id:int,warehouses:array<int,array<string,mixed>>,rows:array<int,array<string,mixed>>,totals:array<string,float|int>,warehouse_name:string} */
    public function bySupplier(string $dateFrom, string $dateTo, int $warehouseId): array
    {
        [$dateFrom, $dateTo] = $this->normalizeDates($dateFrom, $dateTo);
        $warehouseId = max(0, $warehouseId);
        $warehouses = $this->fetchWarehouses();
        $warehouseName = $warehouseId > 0 ? $this->loadWarehouseName($warehouseId) : '';

        $params = [$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59'];
        $where = "WHERE pi.status='POSTED' AND pi.inv_date >= ? AND pi.inv_date <= ?";
        if ($warehouseId > 0) {
            $where .= ' AND pi.warehouse_id=?';
            $params[] = $warehouseId;
        }

        $sql = "SELECT s.id party_id, s.name supplier_name,
                       COUNT(*) inv_count,
                       COALESCE(SUM(pi.subtotal),0) subtotal,
                       COALESCE(SUM(pi.discount_amount),0) discount_amount,
                       COALESCE(SUM(pi.tax_amount),0) tax_amount,
                       COALESCE(SUM(pi.wht_amount),0) wht_amount,
                       COALESCE(SUM(pi.total),0) total
                FROM purchase_invoices pi
                JOIN parties s ON s.id = COALESCE(pi.party_id, pi.supplier_id)
                {$where}
                GROUP BY s.id, s.name
                ORDER BY total DESC, s.name";
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        $rows = $st->fetchAll() ?: [];

        $totals = ['inv_count' => 0, 'subtotal' => 0.0, 'discount_amount' => 0.0, 'tax_amount' => 0.0, 'wht_amount' => 0.0, 'total' => 0.0];
        foreach ($rows as $row) {
            $totals['inv_count'] += (int) ($row['inv_count'] ?? 0);
            $totals['subtotal'] += (float) ($row['subtotal'] ?? 0);
            $totals['discount_amount'] += (float) ($row['discount_amount'] ?? 0);
            $totals['tax_amount'] += (float) ($row['tax_amount'] ?? 0);
            $totals['wht_amount'] += (float) ($row['wht_amount'] ?? 0);
            $totals['total'] += (float) ($row['total'] ?? 0);
        }

        return [
            'date_from' => $dateFrom,
            'date_to' => $dateTo,
            'warehouse_id' => $warehouseId,
            'warehouses' => $warehouses,
            'rows' => $rows,
            'totals' => $totals,
            'warehouse_name' => $warehouseName,
        ];
    }

    /** @return array{date_from:string,date_to:string,party_id:int,warehouse_id:int,item_id:int,warehouses:array<int,array<string,mixed>>,supplier:?array<string,mixed>,item:?array<string,mixed>,rows:array<int,array<string,mixed>>,totals:array<string,float>,supplier_name:string,warehouse_name:string,item_name:string} */
    public function items(string $dateFrom, string $dateTo, int $partyId, int $warehouseId, int $itemId): array
    {
        [$dateFrom, $dateTo] = $this->normalizeDates($dateFrom, $dateTo);
        $partyId = max(0, $partyId);
        $warehouseId = max(0, $warehouseId);
        $itemId = max(0, $itemId);

        $warehouses = $this->fetchWarehouses();
        $supplier = $this->loadParty($partyId);
        $item = $this->loadItem($itemId);
        $warehouseName = $warehouseId > 0 ? $this->loadWarehouseName($warehouseId) : '';

        $params = [$dateFrom . ' 00:00:00', $dateTo . ' 23:59:59'];
        $where = "WHERE pi.status='POSTED' AND pi.inv_date >= ? AND pi.inv_date <= ?";
        if ($partyId > 0) {
            $where .= ' AND COALESCE(pi.party_id, pi.supplier_id)=?';
            $params[] = $partyId;
        }
        if ($warehouseId > 0) {
            $where .= ' AND pi.warehouse_id=?';
            $params[] = $warehouseId;
        }
        if ($itemId > 0) {
            $where .= ' AND pil.item_id=?';
            $params[] = $itemId;
        }

        $sql = "SELECT i.id item_id, i.name item_name,
                       COALESCE(SUM(pil.qty),0) qty,
                       COALESCE(SUM(pil.line_total),0) total,
                       CASE WHEN COALESCE(SUM(pil.qty),0) > 0
                            THEN COALESCE(SUM(pil.line_total),0) / COALESCE(SUM(pil.qty),0)
                            ELSE 0 END avg_price
                FROM purchase_invoice_lines pil
                JOIN purchase_invoices pi ON pi.id = pil.invoice_id
                JOIN items i ON i.id = pil.item_id
                {$where}
                GROUP BY i.id, i.name
                ORDER BY total DESC, i.name";
        $st = $this->pdo->prepare($sql);
        $st->execute($params);
        $rows = $st->fetchAll() ?: [];

        $totals = ['qty' => 0.0, 'total' => 0.0];
        foreach ($rows as $row) {
            $totals['qty'] += (float) ($row['qty'] ?? 0);
            $totals['total'] += (float) ($row['total'] ?? 0);
        }

        return [
            'date_from' => $dateFrom,
            'date_to' => $dateTo,
            'party_id' => $partyId,
            'warehouse_id' => $warehouseId,
            'item_id' => $itemId,
            'warehouses' => $warehouses,
            'supplier' => $supplier,
            'item' => $item,
            'rows' => $rows,
            'totals' => $totals,
            'supplier_name' => (string) ($supplier['name'] ?? ''),
            'warehouse_name' => $warehouseName,
            'item_name' => (string) ($item['name'] ?? ''),
        ];
    }

    /** @return array{0:string,1:string} */
    private function normalizeDates(string $dateFrom, string $dateTo): array
    {
        $dateFrom = trim($dateFrom) !== '' ? trim($dateFrom) : date('Y-m-01');
        $dateTo = trim($dateTo) !== '' ? trim($dateTo) : date('Y-m-d');
        return [$dateFrom, $dateTo];
    }

    /** @return array<int,array<string,mixed>> */
    private function fetchWarehouses(): array
    {
        if (!table_exists($this->pdo, 'warehouses')) {
            return [];
        }
        return $this->pdo->query('SELECT id, name FROM warehouses ORDER BY name')->fetchAll() ?: [];
    }

    /** @return ?array<string,mixed> */
    private function loadParty(int $partyId): ?array
    {
        if ($partyId <= 0) {
            return null;
        }
        $st = $this->pdo->prepare('SELECT id, name FROM parties WHERE id=? LIMIT 1');
        $st->execute([$partyId]);
        $row = $st->fetch();
        return $row ?: null;
    }

    /** @return ?array<string,mixed> */
    private function loadItem(int $itemId): ?array
    {
        if ($itemId <= 0 || !table_exists($this->pdo, 'items')) {
            return null;
        }
        $st = $this->pdo->prepare('SELECT id, name FROM items WHERE id=? LIMIT 1');
        $st->execute([$itemId]);
        $row = $st->fetch();
        return $row ?: null;
    }

    private function loadWarehouseName(int $warehouseId): string
    {
        if ($warehouseId <= 0 || !table_exists($this->pdo, 'warehouses')) {
            return '';
        }
        $st = $this->pdo->prepare('SELECT name FROM warehouses WHERE id=? LIMIT 1');
        $st->execute([$warehouseId]);
        return (string) ($st->fetchColumn() ?: '');
    }
}
