<?php
declare(strict_types=1);

namespace App\Models;

use DateTimeImmutable;
use PDO;

class Order extends BaseModel
{
    protected string $table = 'orders';

    public function createPending(array $data): array
    {
        $statement = $this->db()->prepare(
            'INSERT INTO orders
                (order_no, user_id, creator_id, package_id, subscription_id, type, period_type,
                 gross_amount, discount_amount, provider_fee_rate, provider_fee_fixed, provider_fee_amount,
                 commission_rate, commission_amount, tax_rate, tax_amount, creator_net_amount, platform_net_amount,
                 subtotal, platform_fee, creator_amount, total, currency, payment_provider, provider_reference, status,
                 created_at, updated_at)
             VALUES
                (:order_no, :user_id, :creator_id, :package_id, NULL, "subscription", :period_type,
                 :gross_amount, :discount_amount, :provider_fee_rate, :provider_fee_fixed, :provider_fee_amount,
                 :commission_rate, :commission_amount, :tax_rate, :tax_amount, :creator_net_amount, :platform_net_amount,
                 :subtotal, :platform_fee, :creator_amount, :total, :currency, :payment_provider, NULL, "pending_payment",
                 NOW(), NOW())'
        );
        $statement->execute([
            'order_no' => $data['order_no'],
            'user_id' => (int) $data['user_id'],
            'creator_id' => (int) $data['creator_id'],
            'package_id' => (int) $data['package_id'],
            'period_type' => $data['period_type'],
            'gross_amount' => $data['gross_amount'],
            'discount_amount' => $data['discount_amount'],
            'provider_fee_rate' => $data['provider_fee_rate'],
            'provider_fee_fixed' => $data['provider_fee_fixed'],
            'provider_fee_amount' => $data['provider_fee_amount'],
            'commission_rate' => $data['commission_rate'],
            'commission_amount' => $data['commission_amount'],
            'tax_rate' => $data['tax_rate'],
            'tax_amount' => $data['tax_amount'],
            'creator_net_amount' => $data['creator_net_amount'],
            'platform_net_amount' => $data['platform_net_amount'],
            'subtotal' => $data['gross_amount'],
            'platform_fee' => $data['commission_amount'],
            'creator_amount' => $data['creator_net_amount'],
            'total' => $data['gross_amount'],
            'currency' => $data['currency'],
            'payment_provider' => $data['payment_provider'],
        ]);

        return $this->find((int) $this->db()->lastInsertId()) ?? [];
    }

    public function findByOrderNo(string $orderNo): ?array
    {
        $statement = $this->db()->prepare(
            'SELECT o.*,
                    p.name AS package_name,
                    p.description AS package_description,
                    p.currency AS package_currency,
                    u.username AS creator_username,
                    COALESCE(up.display_name, pf.display_name, u.nickname, u.username, "Creator") AS creator_name,
                    pay.status AS payment_status,
                    pay.provider AS payment_provider_key,
                    pay.provider_reference AS payment_provider_reference
             FROM orders o
             LEFT JOIN packages p ON p.id = o.package_id
             LEFT JOIN users u ON u.id = o.creator_id
             LEFT JOIN user_profiles up ON up.user_id = u.id
             LEFT JOIN profiles pf ON pf.user_id = u.id
             LEFT JOIN payments pay ON pay.order_id = o.id
             WHERE o.order_no = :order_no
             ORDER BY pay.id DESC
             LIMIT 1'
        );
        $statement->execute(['order_no' => $orderNo]);
        $row = $statement->fetch();

        return $row ?: null;
    }

    public function markCompleted(int $orderId, int $subscriptionId, string $providerReference): void
    {
        $statement = $this->db()->prepare(
            'UPDATE orders
             SET status = "completed",
                 subscription_id = :subscription_id,
                 provider_reference = :provider_reference,
                 updated_at = NOW()
             WHERE id = :id'
        );
        $statement->execute([
            'id' => $orderId,
            'subscription_id' => $subscriptionId,
            'provider_reference' => $providerReference,
        ]);
    }

    public function markFailed(int $orderId, string $providerReference = ''): void
    {
        $statement = $this->db()->prepare(
            'UPDATE orders
             SET status = "failed",
                 provider_reference = NULLIF(:provider_reference, ""),
                 updated_at = NOW()
             WHERE id = :id'
        );
        $statement->execute([
            'id' => $orderId,
            'provider_reference' => $providerReference,
        ]);
    }

    public function markPending(int $orderId, string $providerReference = ''): void
    {
        $statement = $this->db()->prepare(
            'UPDATE orders
             SET status = "pending_payment",
                 provider_reference = NULLIF(:provider_reference, ""),
                 updated_at = NOW()
             WHERE id = :id'
        );
        $statement->execute([
            'id' => $orderId,
            'provider_reference' => $providerReference,
        ]);
    }

    public function generateOrderNo(): string
    {
        do {
            $orderNo = 'ZLX-' . (new DateTimeImmutable('now'))->format('Ymd') . '-' . strtoupper(bin2hex(random_bytes(3)));
            $statement = $this->db()->prepare('SELECT id FROM orders WHERE order_no = :order_no LIMIT 1');
            $statement->execute(['order_no' => $orderNo]);
        } while ($statement->fetchColumn());

        return $orderNo;
    }

    public function financials(float $grossAmount, array $settings): array
    {
        $grossAmount = round(max(0.0, $grossAmount), 2);
        $commissionRate = $this->rate($settings['site_commission_rate'] ?? $settings['commission_rate'] ?? 0.10);
        $taxRate = $this->rate($settings['tax_rate'] ?? 0.20);
        $providerFeeRate = $this->rate($settings['provider_fee_rate'] ?? 0);
        $providerFeeFixed = round(max(0.0, (float) ($settings['provider_fee_fixed'] ?? 0)), 2);
        $providerFeeAmount = round(($grossAmount * $providerFeeRate) + $providerFeeFixed, 2);
        $commissionAmount = round($grossAmount * $commissionRate, 2);
        $taxAmount = round($grossAmount * $taxRate, 2);
        $creatorNetAmount = round(max(0.0, $grossAmount - $providerFeeAmount - $commissionAmount - $taxAmount), 2);

        return [
            'gross_amount' => $grossAmount,
            'discount_amount' => 0.00,
            'provider_fee_rate' => $providerFeeRate,
            'provider_fee_fixed' => $providerFeeFixed,
            'provider_fee_amount' => $providerFeeAmount,
            'commission_rate' => $commissionRate,
            'commission_amount' => $commissionAmount,
            'tax_rate' => $taxRate,
            'tax_amount' => $taxAmount,
            'creator_net_amount' => $creatorNetAmount,
            'platform_net_amount' => $commissionAmount,
        ];
    }

    public function recentSalesForCreator(int $creatorId, int $limit = 30): array
    {
        $statement = $this->db()->prepare(
            'SELECT o.*,
                    p.name AS package_name,
                    u.username AS subscriber_username,
                    COALESCE(up.display_name, pf.display_name, u.nickname, u.username) AS subscriber_name,
                    pay.status AS payment_status
             FROM orders o
             LEFT JOIN packages p ON p.id = o.package_id
             LEFT JOIN users u ON u.id = o.user_id
             LEFT JOIN user_profiles up ON up.user_id = u.id
             LEFT JOIN profiles pf ON pf.user_id = u.id
             LEFT JOIN payments pay ON pay.order_id = o.id
             WHERE o.creator_id = :creator_id
               AND o.status IN ("paid", "completed")
             ORDER BY o.id DESC
             LIMIT :limit'
        );
        $statement->bindValue('creator_id', $creatorId, PDO::PARAM_INT);
        $statement->bindValue('limit', max(1, min(100, $limit)), PDO::PARAM_INT);
        $statement->execute();

        return $statement->fetchAll();
    }

    public function recentSalesForAdmin(int $limit = 50): array
    {
        $statement = $this->db()->prepare(
            'SELECT o.*,
                    p.name AS package_name,
                    buyer.username AS subscriber_username,
                    COALESCE(buyer_up.display_name, buyer_pf.display_name, buyer.nickname, buyer.username) AS subscriber_name,
                    creator.username AS creator_username,
                    COALESCE(creator_up.display_name, creator_pf.display_name, creator.nickname, creator.username) AS creator_name,
                    pay.status AS payment_status
             FROM orders o
             LEFT JOIN packages p ON p.id = o.package_id
             LEFT JOIN users buyer ON buyer.id = o.user_id
             LEFT JOIN user_profiles buyer_up ON buyer_up.user_id = buyer.id
             LEFT JOIN profiles buyer_pf ON buyer_pf.user_id = buyer.id
             LEFT JOIN users creator ON creator.id = o.creator_id
             LEFT JOIN user_profiles creator_up ON creator_up.user_id = creator.id
             LEFT JOIN profiles creator_pf ON creator_pf.user_id = creator.id
             LEFT JOIN payments pay ON pay.order_id = o.id
             WHERE o.status IN ("paid", "completed")
             ORDER BY o.id DESC
             LIMIT :limit'
        );
        $statement->bindValue('limit', max(1, min(200, $limit)), PDO::PARAM_INT);
        $statement->execute();

        return $statement->fetchAll();
    }

    public function adminEarningSummary(): array
    {
        $statement = $this->db()->query(
            'SELECT
                COALESCE(SUM(gross_amount), 0) AS gross_total,
                COALESCE(SUM(platform_net_amount), 0) AS platform_net_total,
                COALESCE(SUM(creator_net_amount), 0) AS creator_net_total,
                COALESCE(SUM(commission_amount), 0) AS commission_total,
                COALESCE(SUM(tax_amount), 0) AS tax_total,
                COALESCE(SUM(provider_fee_amount), 0) AS provider_fee_total
             FROM orders
             WHERE status IN ("paid", "completed")'
        );

        return $statement->fetch() ?: [];
    }

    public function creatorSummaries(int $limit = 100): array
    {
        $statement = $this->db()->prepare(
            'SELECT o.creator_id,
                    COALESCE(up.display_name, pf.display_name, u.nickname, u.username) AS creator_name,
                    u.username,
                    COUNT(o.id) AS sale_count,
                    COALESCE(SUM(o.gross_amount), 0) AS gross_total,
                    COALESCE(SUM(o.creator_net_amount), 0) AS creator_net_total,
                    COALESCE(SUM(o.commission_amount), 0) AS commission_total,
                    COALESCE(SUM(o.tax_amount), 0) AS tax_total,
                    COALESCE(SUM(o.provider_fee_amount), 0) AS provider_fee_total,
                    COALESCE(cb.pending_balance, 0) AS pending_balance,
                    COALESCE(cb.available_balance, 0) AS available_balance,
                    COALESCE(cb.withdrawn_balance, 0) AS withdrawn_balance
             FROM orders o
             LEFT JOIN users u ON u.id = o.creator_id
             LEFT JOIN user_profiles up ON up.user_id = u.id
             LEFT JOIN profiles pf ON pf.user_id = u.id
             LEFT JOIN creator_balances cb ON cb.user_id = o.creator_id OR cb.creator_id = o.creator_id
             WHERE o.status IN ("paid", "completed")
             GROUP BY o.creator_id, creator_name, u.username, cb.pending_balance, cb.available_balance, cb.withdrawn_balance
             ORDER BY creator_net_total DESC
             LIMIT :limit'
        );
        $statement->bindValue('limit', max(1, min(300, $limit)), PDO::PARAM_INT);
        $statement->execute();

        return $statement->fetchAll();
    }

    public function priceForPeriod(array $package, string $periodType): float
    {
        $key = match ($periodType) {
            '6_month' => 'price_6_month',
            '12_month' => 'price_12_month',
            default => 'price_monthly',
        };

        return round(max(0.0, (float) ($package[$key] ?? $package['price_monthly'] ?? $package['price'] ?? 0)), 2);
    }

    public function normalizePeriod(string $periodType): string
    {
        return in_array($periodType, ['monthly', '6_month', '12_month'], true) ? $periodType : 'monthly';
    }

    public function periodMonths(string $periodType): int
    {
        return match ($this->normalizePeriod($periodType)) {
            '6_month' => 6,
            '12_month' => 12,
            default => 1,
        };
    }

    private function rate(mixed $value): float
    {
        $rate = (float) str_replace(',', '.', (string) $value);
        if ($rate > 1) {
            $rate = $rate / 100;
        }

        return round(max(0.0, $rate), 4);
    }
}
