<?php
/**
 * Settlement Model
 * Maps to `settlements` table
 * Handles seller payout cycles, commission deductions, and transfer records
 */

require_once __DIR__ . '/../Core/Model.php';

class Settlement extends Model {
    protected string $table = 'settlements';

    public function getAdminSettlements(array $filters = [], int $limit = 20, int $offset = 0): array {
        [$whereSql, $params] = $this->buildFilters($filters);

        $sql = "SELECT se.*, s.store_name, s.store_slug, s.phone as store_phone,
                       CONCAT(u.first_name, ' ', u.last_name) as owner_name, u.username as owner_username, u.email as owner_email
                FROM `settlements` se
                JOIN `stores` s ON se.store_id = s.id
                JOIN `users` u ON s.user_id = u.id
                WHERE {$whereSql}
                ORDER BY se.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit',  $limit,  PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function countAdminSettlements(array $filters = []): int {
        [$whereSql, $params] = $this->buildFilters($filters);
        $sql = "SELECT COUNT(*)
                FROM `settlements` se
                JOIN `stores` s ON se.store_id = s.id
                JOIN `users` u ON s.user_id = u.id
                WHERE {$whereSql}";
        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->execute();
        return (int)$stmt->fetchColumn();
    }

    private function buildFilters(array $filters): array {
        $conditions = ['1=1'];
        $params = [];

        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $conditions[] = "se.payment_status = :status";
            $params[':status'] = $filters['status'];
        }
        if (!empty($filters['store_id'])) {
            $conditions[] = "se.store_id = :store_id";
            $params[':store_id'] = (int)$filters['store_id'];
        }
        if (!empty($filters['q'])) {
            $conditions[] = "(s.store_name LIKE :q OR s.store_slug LIKE :q OR u.username LIKE :q OR se.payment_reference LIKE :q)";
            $params[':q'] = '%' . trim($filters['q']) . '%';
        }
        if (!empty($filters['date_from'])) {
            $conditions[] = "se.created_at >= :date_from";
            $params[':date_from'] = $filters['date_from'] . ' 00:00:00';
        }
        if (!empty($filters['date_to'])) {
            $conditions[] = "se.created_at <= :date_to";
            $params[':date_to'] = $filters['date_to'] . ' 23:59:59';
        }

        return [implode(' AND ', $conditions), $params];
    }

    public function findWithStore(int $id): ?array {
        $sql = "SELECT se.*, s.store_name, s.store_slug, s.phone as store_phone, s.address as store_address,
                       CONCAT(u.first_name, ' ', u.last_name) as owner_name, u.username as owner_username, u.email as owner_email
                FROM `settlements` se
                JOIN `stores` s ON se.store_id = s.id
                JOIN `users` u ON s.user_id = u.id
                WHERE se.id = :id
                LIMIT 1";
        return $this->fetchOne($sql, [':id' => $id]);
    }

    public function getSettlementStats(): array {
        $sql = "SELECT
                  COUNT(*) as total,
                  SUM(CASE WHEN payment_status = 'pending' THEN 1 ELSE 0 END) as pending_count,
                  SUM(CASE WHEN payment_status = 'pending' THEN seller_receives ELSE 0 END) as pending_amount,
                  SUM(CASE WHEN payment_status = 'processing' THEN 1 ELSE 0 END) as processing_count,
                  SUM(CASE WHEN payment_status = 'completed' THEN 1 ELSE 0 END) as completed_count,
                  SUM(CASE WHEN payment_status = 'completed' THEN seller_receives ELSE 0 END) as completed_amount,
                  SUM(total_sales) as total_sales,
                  SUM(commission_deduction) as total_commission,
                  SUM(seller_receives) as total_seller_receives
                FROM `settlements`";
        return $this->fetchOne($sql) ?: [];
    }

    public function process(int $id, string $status, ?string $paymentReference, ?string $note): bool {
        $data = [
            'payment_status' => $status,
            'updated_at'     => date('Y-m-d H:i:s')
        ];

        if ($paymentReference !== null) {
            $data['payment_reference'] = $paymentReference;
        }
        if ($note !== null) {
            $data['note'] = $note;
        }
        if ($status === 'completed') {
            $data['transfer_date'] = date('Y-m-d H:i:s');
        }

        return $this->update($id, $data);
    }

    /**
     * Calculate completed unsettled order totals per store
     */
    public function getUnsettledStoreBalances(): array {
        $sql = "SELECT s.id as store_id, s.store_name, s.store_slug,
                       CONCAT(u.first_name, ' ', u.last_name) as owner_name, u.username as owner_username,
                       COUNT(o.id) as completed_orders,
                       SUM(o.total_amount) as gross_sales,
                       SUM(o.commission_amount) as total_commission,
                       SUM(o.net_revenue) as net_receivable
                FROM `stores` s
                JOIN `users` u ON s.user_id = u.id
                JOIN `orders` o ON o.store_id = s.id
                WHERE o.order_status = 'completed'
                  AND o.deleted_at IS NULL
                GROUP BY s.id
                ORDER BY net_receivable DESC";
        return $this->fetchAll($sql);
    }
}