<?php
declare(strict_types=1);

namespace App\Modules\Subscriptions\Models;

use App\Core\Model;
use App\Core\App;
use App\Core\Pagination;

class Subscription extends Model
{
    protected static string $table = 'subscriptions';
    protected static string $primaryKey = 'id';
    protected static bool $timestamps = true;
    protected static bool $softDelete = false;
    protected static bool $dispatchEvents = true;

    protected static array $fillable = [
        'member_id', 'financial_year', 'person_type', 'person_id', 'person_name',
        'base_amount', 'development_fee', 'discount_amount', 'total_amount',
        'paid_amount', 'fine_amount', 'status', 'paid_at', 'payment_id',
        'receipt_number', 'paid_by',
        'exempted_by', 'exemption_reason',
    ];

    public static function getForMember(int $memberId, ?string $year = null): array
    {
        $db = App::getInstance()->db();
        $where = 's.member_id = ?';
        $params = [$memberId];
        if ($year) {
            $where .= ' AND s.financial_year = ?';
            $params[] = $year;
        }
        return $db->select(
            "SELECT s.*, e.full_name_ar as paid_by_name
             FROM subscriptions s
             LEFT JOIN employees e ON e.id = s.paid_by
             WHERE {$where} ORDER BY s.financial_year DESC, s.person_type ASC",
            $params
        );
    }

    public static function getMemberYearTotal(int $memberId, string $year): array
    {
        $db = App::getInstance()->db();
        $row = $db->selectOne(
            "SELECT COALESCE(SUM(total_amount),0) as total, COALESCE(SUM(paid_amount),0) as paid, COALESCE(SUM(fine_amount),0) as fines, COUNT(*) as count
             FROM subscriptions WHERE member_id = ? AND financial_year = ?",
            [$memberId, $year]
        );
        return $row ?: ['total' => '0.00', 'paid' => '0.00', 'fines' => '0.00', 'count' => 0];
    }

    public static function getUnpaidYears(int $memberId): array
    {
        $db = App::getInstance()->db();
        return $db->select(
            "SELECT financial_year, SUM(total_amount) as total, SUM(paid_amount) as paid, SUM(fine_amount) as fines
             FROM subscriptions WHERE member_id = ? AND status IN ('pending','overdue')
             GROUP BY financial_year ORDER BY financial_year ASC",
            [$memberId]
        );
    }

    public static function search(array $filters, int $perPage = 25, int $page = 1): array
    {
        $db = App::getInstance()->db();
        $where = '1=1';
        $params = [];

        if (!empty($filters['financial_year'])) { $where .= ' AND s.financial_year = ?'; $params[] = $filters['financial_year']; }
        if (!empty($filters['status'])) { $where .= ' AND s.status = ?'; $params[] = $filters['status']; }
        if (!empty($filters['person_type'])) { $where .= ' AND s.person_type = ?'; $params[] = $filters['person_type']; }
        if (!empty($filters['search'])) {
            $where .= ' AND (m.full_name_ar LIKE ? OR m.membership_number LIKE ? OR s.person_name LIKE ?)';
            $s = '%' . $filters['search'] . '%'; $params[] = $s; $params[] = $s; $params[] = $s;
        }

        $countRow = $db->selectOne("SELECT COUNT(*) as cnt FROM subscriptions s JOIN members m ON m.id = s.member_id WHERE {$where}", $params);
        $total = (int) ($countRow['cnt'] ?? 0);
        $offset = ($page - 1) * $perPage;

        $rows = $db->select(
            "SELECT s.*, m.full_name_ar as member_name, m.membership_number
             FROM subscriptions s JOIN members m ON m.id = s.member_id
             WHERE {$where} ORDER BY s.financial_year DESC, m.full_name_ar ASC
             LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

        return ['data' => $rows, 'pagination' => Pagination::paginate($total, $perPage, $page)];
    }
}