<?php
declare(strict_types=1);

namespace App\Models;

class Package extends BaseModel
{
    protected string $table = 'packages';

    public function listForCreator(int $creatorId, bool $includeInactive = true): array
    {
        if ($creatorId <= 0) {
            return [];
        }

        $where = '(p.user_id = :user_id OR p.creator_id = :creator_id)';
        if (!$includeInactive) {
            $where .= ' AND (p.status = "active" OR (p.status IS NULL AND p.is_active = 1))';
        }

        $statement = $this->db()->prepare(
            'SELECT p.*,
                    COALESCE(p.user_id, p.creator_id) AS owner_id,
                    COALESCE(stats.active_subscribers, 0) AS active_subscribers,
                    COALESCE(stats.active_subscribers, 0) * COALESCE(NULLIF(p.price_monthly, 0), p.price, 0) AS monthly_revenue_placeholder
             FROM packages p
             LEFT JOIN (
                SELECT package_id, COUNT(*) AS active_subscribers
                FROM subscriptions
                WHERE status = "active"
                  AND (starts_at IS NULL OR starts_at <= NOW())
                  AND (ends_at IS NULL OR ends_at > NOW())
                GROUP BY package_id
             ) stats ON stats.package_id = p.id
             WHERE ' . $where . '
             ORDER BY (p.status = "active") DESC, p.price_monthly ASC, p.id DESC'
        );
        $statement->execute([
            'user_id' => $creatorId,
            'creator_id' => $creatorId,
        ]);

        return array_map(fn (array $package): array => $this->normalize($package), $statement->fetchAll());
    }

    public function activeForCreator(int $creatorId): array
    {
        return $this->listForCreator($creatorId, false);
    }

    public function findPublic(int $id): ?array
    {
        $statement = $this->db()->prepare(
            'SELECT p.*,
                    COALESCE(p.user_id, p.creator_id) AS owner_id,
                    u.username AS creator_username,
                    COALESCE(up.display_name, pf.display_name, u.nickname, u.username, "Creator") AS creator_name,
                    COALESCE(up.avatar, pf.avatar_path) AS creator_avatar
             FROM packages p
             LEFT JOIN users u ON u.id = COALESCE(p.user_id, p.creator_id)
             LEFT JOIN user_profiles up ON up.user_id = u.id
             LEFT JOIN profiles pf ON pf.user_id = u.id
             WHERE p.id = :id
             LIMIT 1'
        );
        $statement->execute(['id' => $id]);
        $package = $statement->fetch();

        return $package ? $this->normalize($package) : null;
    }

    public function createForCreator(int $creatorId, array $data): array
    {
        $slug = $this->uniqueSlug((string) $data['name']);
        $status = $this->normalizeStatus((string) ($data['status'] ?? 'active'));
        $monthly = $this->money($data['price_monthly'] ?? 0);
        $sixMonth = $this->money($data['price_6_month'] ?? 0);
        $twelveMonth = $this->money($data['price_12_month'] ?? 0);

        $statement = $this->db()->prepare(
            'INSERT INTO packages
                (user_id, creator_id, name, slug, description, price_monthly, price_6_month, price_12_month, price, currency, billing_period, benefits, status, is_active)
             VALUES
                (:user_id, :creator_id, :name, :slug, :description, :price_monthly, :price_6_month, :price_12_month, :price, "TRY", "monthly", :benefits, :status, :is_active)'
        );
        $statement->execute([
            'user_id' => $creatorId,
            'creator_id' => $creatorId,
            'name' => trim((string) $data['name']),
            'slug' => $slug,
            'description' => trim((string) ($data['description'] ?? '')),
            'price_monthly' => $monthly,
            'price_6_month' => $sixMonth,
            'price_12_month' => $twelveMonth,
            'price' => $monthly,
            'benefits' => json_encode(['Premium mod erisimi', 'Premium muzik erisimi', 'Creator destek rozeti'], JSON_UNESCAPED_UNICODE),
            'status' => $status,
            'is_active' => $status === 'active' ? 1 : 0,
        ]);

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

    public function updateOwned(int $packageId, int $creatorId, bool $isAdmin, array $data): ?array
    {
        $current = $this->findPublic($packageId);
        if (!$current) {
            return null;
        }

        if (!$isAdmin && (int) ($current['user_id'] ?? 0) !== $creatorId) {
            return null;
        }

        $slug = $this->uniqueSlug((string) $data['name'], $packageId);
        $status = $this->normalizeStatus((string) ($data['status'] ?? ($current['status'] ?? 'active')));
        $monthly = $this->money($data['price_monthly'] ?? $current['price_monthly'] ?? 0);
        $sixMonth = $this->money($data['price_6_month'] ?? $current['price_6_month'] ?? 0);
        $twelveMonth = $this->money($data['price_12_month'] ?? $current['price_12_month'] ?? 0);

        $statement = $this->db()->prepare(
            'UPDATE packages
             SET name = :name,
                 slug = :slug,
                 description = :description,
                 price_monthly = :price_monthly,
                 price_6_month = :price_6_month,
                 price_12_month = :price_12_month,
                 price = :price,
                 status = :status,
                 is_active = :is_active,
                 updated_at = NOW()
             WHERE id = :id'
        );
        $statement->execute([
            'id' => $packageId,
            'name' => trim((string) $data['name']),
            'slug' => $slug,
            'description' => trim((string) ($data['description'] ?? '')),
            'price_monthly' => $monthly,
            'price_6_month' => $sixMonth,
            'price_12_month' => $twelveMonth,
            'price' => $monthly,
            'status' => $status,
            'is_active' => $status === 'active' ? 1 : 0,
        ]);

        return $this->findPublic($packageId);
    }

    public function setStatusOwned(int $packageId, int $creatorId, bool $isAdmin, string $status): ?array
    {
        $current = $this->findPublic($packageId);
        if (!$current) {
            return null;
        }

        if (!$isAdmin && (int) ($current['user_id'] ?? 0) !== $creatorId) {
            return null;
        }

        $status = $this->normalizeStatus($status);
        $statement = $this->db()->prepare(
            'UPDATE packages
             SET status = :status, is_active = :is_active, updated_at = NOW()
             WHERE id = :id'
        );
        $statement->execute([
            'id' => $packageId,
            'status' => $status,
            'is_active' => $status === 'active' ? 1 : 0,
        ]);

        return $this->findPublic($packageId);
    }

    public function softDeleteOwned(int $packageId, int $creatorId, bool $isAdmin): ?array
    {
        return $this->setStatusOwned($packageId, $creatorId, $isAdmin, 'inactive');
    }

    public function validatePayload(array $data): array
    {
        $errors = [];
        $name = trim((string) ($data['name'] ?? ''));

        if ($name === '' || strlen($name) > 120) {
            $errors[] = 'Paket adi 1-120 karakter arasinda olmalidir.';
        }

        foreach (['price_monthly' => 'Aylik fiyat', 'price_6_month' => '6 aylik fiyat', 'price_12_month' => '12 aylik fiyat'] as $key => $label) {
            $value = str_replace(',', '.', trim((string) ($data[$key] ?? '')));
            if ($value === '' || !is_numeric($value) || (float) $value <= 0) {
                $errors[] = $label . ' 0dan buyuk numeric bir deger olmalidir.';
            }
        }

        $status = (string) ($data['status'] ?? 'active');
        if (!in_array($status, ['active', 'inactive'], true)) {
            $errors[] = 'Paket durumu gecersiz.';
        }

        return $errors;
    }

    public function normalize(array $package): array
    {
        $ownerId = (int) (($package['user_id'] ?? null) ?: ($package['creator_id'] ?? 0));
        $monthly = $this->money($package['price_monthly'] ?? ($package['price'] ?? 0));
        $sixMonth = $this->money($package['price_6_month'] ?? ($monthly * 6));
        $twelveMonth = $this->money($package['price_12_month'] ?? ($monthly * 12));
        $status = $this->normalizeStatus((string) (($package['status'] ?? '') ?: ((int) ($package['is_active'] ?? 1) === 1 ? 'active' : 'inactive')));

        $package['id'] = (int) ($package['id'] ?? 0);
        $package['user_id'] = $ownerId;
        $package['creator_id'] = $ownerId;
        $package['owner_id'] = $ownerId;
        $package['name'] = (string) ($package['name'] ?? '');
        $package['slug'] = (string) ($package['slug'] ?? '');
        $package['description'] = (string) ($package['description'] ?? '');
        $package['price_monthly'] = $monthly;
        $package['price_6_month'] = $sixMonth;
        $package['price_12_month'] = $twelveMonth;
        $package['currency'] = (string) (($package['currency'] ?? '') ?: 'TRY');
        $package['status'] = $status;
        $package['is_active'] = $status === 'active';
        $package['active_subscribers'] = (int) ($package['active_subscribers'] ?? 0);
        $package['monthly_revenue_placeholder'] = $this->money($package['monthly_revenue_placeholder'] ?? 0);
        $package['creator_username'] = $package['creator_username'] ?? null;
        $package['creator_name'] = $package['creator_name'] ?? null;
        $package['creator_avatar'] = $package['creator_avatar'] ?? null;

        return $package;
    }

    private function uniqueSlug(string $name, int $excludeId = 0): string
    {
        $base = $this->slugify($name);
        $slug = $base;
        $suffix = 2;

        while ($this->slugExists($slug, $excludeId)) {
            $slug = $base . '-' . $suffix;
            $suffix++;
        }

        return $slug;
    }

    private function slugExists(string $slug, int $excludeId = 0): bool
    {
        $sql = 'SELECT id FROM packages WHERE slug = :slug';
        $params = ['slug' => $slug];

        if ($excludeId > 0) {
            $sql .= ' AND id <> :exclude_id';
            $params['exclude_id'] = $excludeId;
        }

        $sql .= ' LIMIT 1';
        $statement = $this->db()->prepare($sql);
        $statement->execute($params);

        return (bool) $statement->fetchColumn();
    }

    private function slugify(string $value): string
    {
        $value = strtr($value, [
            'Ç' => 'C', 'Ğ' => 'G', 'İ' => 'I', 'I' => 'I', 'Ö' => 'O', 'Ş' => 'S', 'Ü' => 'U',
            'ç' => 'c', 'ğ' => 'g', 'ı' => 'i', 'i' => 'i', 'ö' => 'o', 'ş' => 's', 'ü' => 'u',
        ]);
        $value = strtolower($value);
        $value = preg_replace('~[^a-z0-9]+~', '-', $value) ?: '';
        $value = trim($value, '-');

        return $value !== '' ? substr($value, 0, 150) : 'paket';
    }

    private function normalizeStatus(string $status): string
    {
        return in_array($status, ['active', 'inactive'], true) ? $status : 'active';
    }

    private function money(mixed $value): float
    {
        return round(max(0.0, (float) str_replace(',', '.', (string) $value)), 2);
    }
}
