<?php
declare(strict_types=1);

namespace App\Models;

use PDO;

class Profile extends BaseModel
{
    protected string $table = 'user_profiles';

    public function editableForUser(int $userId): array
    {
        $statement = $this->db()->prepare(
            'SELECT u.id AS user_id,
                    u.username,
                    u.email,
                    u.nickname AS user_nickname,
                    COALESCE(up.nickname, p.nickname, u.username) AS nickname,
                    COALESCE(up.display_name, p.display_name, u.nickname, u.username) AS display_name,
                    COALESCE(up.about, p.bio, "") AS about,
                    COALESCE(up.avatar, p.avatar_path, "") AS avatar,
                    COALESCE(up.cover_image, p.cover_path, "") AS cover_image,
                    COALESCE(up.instagram, "") AS instagram,
                    COALESCE(up.youtube, "") AS youtube,
                    COALESCE(up.website, p.website_url, "") AS website,
                    COALESCE(up.privacy, "public") AS privacy,
                    COALESCE(up.message_privacy, "everyone") AS message_privacy
             FROM users u
             LEFT JOIN user_profiles up ON up.user_id = u.id
             LEFT JOIN profiles p ON p.user_id = u.id
             WHERE u.id = :user_id
             LIMIT 1'
        );
        $statement->execute(['user_id' => $userId]);
        $profile = $statement->fetch() ?: [
            'user_id' => $userId,
            'username' => '',
            'email' => '',
            'nickname' => '',
            'display_name' => '',
            'about' => '',
            'avatar' => '',
            'cover_image' => '',
            'instagram' => '',
            'youtube' => '',
            'website' => '',
            'privacy' => 'public',
            'message_privacy' => 'everyone',
        ];

        $profile['privacy'] = $this->normalizePrivacy((string) ($profile['privacy'] ?? 'public'));
        $profile['message_privacy'] = $this->normalizeMessagePrivacy((string) ($profile['message_privacy'] ?? 'everyone'));

        return $profile;
    }

    public function findByUserId(int $userId): ?array
    {
        $statement = $this->db()->prepare("SELECT * FROM {$this->table} WHERE user_id = :user_id LIMIT 1");
        $statement->execute(['user_id' => $userId]);
        $row = $statement->fetch();

        return $row ?: null;
    }

    public function createForUser(int $userId, array $data): void
    {
        $statement = $this->db()->prepare(
            "INSERT INTO {$this->table}
            (user_id, nickname, display_name, about, avatar, cover_image, instagram, youtube, website, privacy, message_privacy, billing_first_name, billing_last_name, billing_email, billing_phone)
            VALUES
            (:user_id, :nickname, :display_name, :about, :avatar, :cover_image, :instagram, :youtube, :website, :privacy, :message_privacy, :billing_first_name, :billing_last_name, :billing_email, :billing_phone)
            ON DUPLICATE KEY UPDATE
                display_name = VALUES(display_name),
                billing_email = VALUES(billing_email),
                updated_at = NOW()"
        );
        $statement->execute([
            'user_id' => $userId,
            'nickname' => $data['nickname'] ?? null,
            'display_name' => $data['display_name'] ?? '',
            'about' => $data['about'] ?? '',
            'avatar' => $data['avatar'] ?? null,
            'cover_image' => $data['cover_image'] ?? null,
            'instagram' => $data['instagram'] ?? null,
            'youtube' => $data['youtube'] ?? null,
            'website' => $data['website'] ?? null,
            'privacy' => $data['privacy'] ?? 'public',
            'message_privacy' => $data['message_privacy'] ?? 'everyone',
            'billing_first_name' => $data['billing_first_name'] ?? null,
            'billing_last_name' => $data['billing_last_name'] ?? null,
            'billing_email' => $data['billing_email'] ?? null,
            'billing_phone' => $data['billing_phone'] ?? null,
        ]);
    }

    public function updateEditable(int $userId, array $data): array
    {
        $nickname = $this->slugify((string) ($data['nickname'] ?? ''));
        $displayName = trim((string) ($data['display_name'] ?? ''));
        $about = trim((string) ($data['about'] ?? ''));
        $instagram = $this->cleanExternalUrl((string) ($data['instagram'] ?? ''));
        $youtube = $this->cleanExternalUrl((string) ($data['youtube'] ?? ''));
        $website = $this->cleanExternalUrl((string) ($data['website'] ?? ''));
        $privacy = $this->normalizePrivacy((string) ($data['privacy'] ?? 'public'));
        $messagePrivacy = $this->normalizeMessagePrivacy((string) ($data['message_privacy'] ?? 'everyone'));

        $statement = $this->db()->prepare(
            "INSERT INTO {$this->table}
                (user_id, nickname, display_name, about, instagram, youtube, website, privacy, message_privacy, created_at, updated_at)
             VALUES
                (:user_id, :nickname, :display_name, :about, :instagram, :youtube, :website, :privacy, :message_privacy, NOW(), NOW())
             ON DUPLICATE KEY UPDATE
                nickname = VALUES(nickname),
                display_name = VALUES(display_name),
                about = VALUES(about),
                instagram = VALUES(instagram),
                youtube = VALUES(youtube),
                website = VALUES(website),
                privacy = VALUES(privacy),
                message_privacy = VALUES(message_privacy),
                updated_at = NOW()"
        );
        $statement->execute([
            'user_id' => $userId,
            'nickname' => $nickname,
            'display_name' => $displayName,
            'about' => $about,
            'instagram' => $instagram ?: null,
            'youtube' => $youtube ?: null,
            'website' => $website ?: null,
            'privacy' => $privacy,
            'message_privacy' => $messagePrivacy,
        ]);

        $this->db()->prepare('UPDATE users SET nickname = :display_name, updated_at = NOW() WHERE id = :user_id')
            ->execute([
                'display_name' => $displayName,
                'user_id' => $userId,
            ]);

        try {
            $this->db()->prepare(
                'INSERT INTO profiles (user_id, display_name, nickname, bio, website_url, created_at, updated_at)
                 VALUES (:user_id, :display_name, :nickname, :bio, :website_url, NOW(), NOW())
                 ON DUPLICATE KEY UPDATE
                    display_name = VALUES(display_name),
                    nickname = VALUES(nickname),
                    bio = VALUES(bio),
                    website_url = VALUES(website_url),
                    updated_at = NOW()'
            )->execute([
                'user_id' => $userId,
                'display_name' => $displayName,
                'nickname' => $nickname,
                'bio' => $about,
                'website_url' => $website ?: null,
            ]);
        } catch (\Throwable) {
            // Legacy profiles table has stricter uniqueness in old installs; user_profiles is the source of truth.
        }

        return $this->editableForUser($userId);
    }

    public function updateAvatar(int $userId, string $path): array
    {
        $current = $this->editableForUser($userId);
        $statement = $this->db()->prepare(
            "INSERT INTO {$this->table} (user_id, nickname, display_name, avatar, created_at, updated_at)
             VALUES (:user_id, :nickname, :display_name, :avatar, NOW(), NOW())
             ON DUPLICATE KEY UPDATE avatar = VALUES(avatar), updated_at = NOW()"
        );
        $statement->execute([
            'user_id' => $userId,
            'nickname' => $this->slugify((string) ($current['nickname'] ?? '')) ?: (string) ($current['username'] ?? 'user'),
            'display_name' => (string) (($current['display_name'] ?? '') ?: ($current['username'] ?? 'User')),
            'avatar' => $path !== '' ? $path : null,
        ]);

        try {
            $this->db()->prepare('UPDATE profiles SET avatar_path = :avatar, updated_at = NOW() WHERE user_id = :user_id')
                ->execute(['avatar' => $path !== '' ? $path : null, 'user_id' => $userId]);
        } catch (\Throwable) {
            // Legacy profile sync is best-effort.
        }

        return $this->editableForUser($userId);
    }

    public function updateCover(int $userId, string $path): array
    {
        $current = $this->editableForUser($userId);
        $statement = $this->db()->prepare(
            "INSERT INTO {$this->table} (user_id, nickname, display_name, cover_image, created_at, updated_at)
             VALUES (:user_id, :nickname, :display_name, :cover_image, NOW(), NOW())
             ON DUPLICATE KEY UPDATE cover_image = VALUES(cover_image), updated_at = NOW()"
        );
        $statement->execute([
            'user_id' => $userId,
            'nickname' => $this->slugify((string) ($current['nickname'] ?? '')) ?: (string) ($current['username'] ?? 'user'),
            'display_name' => (string) (($current['display_name'] ?? '') ?: ($current['username'] ?? 'User')),
            'cover_image' => $path !== '' ? $path : null,
        ]);

        try {
            $this->db()->prepare('UPDATE profiles SET cover_path = :cover_image, updated_at = NOW() WHERE user_id = :user_id')
                ->execute(['cover_image' => $path !== '' ? $path : null, 'user_id' => $userId]);
        } catch (\Throwable) {
            // Legacy profile sync is best-effort.
        }

        return $this->editableForUser($userId);
    }

    public function nicknameAvailable(string $nickname, int $userId): bool
    {
        $nickname = $this->slugify($nickname);
        if ($nickname === '') {
            return false;
        }

        $statement = $this->db()->prepare(
            'SELECT u.id
             FROM users u
             LEFT JOIN user_profiles up ON up.user_id = u.id
             LEFT JOIN profiles p ON p.user_id = u.id
             WHERE u.id <> :user_id
               AND (
                    LOWER(u.username) = :nickname_username
                    OR LOWER(up.nickname) = :nickname_profile
                    OR LOWER(p.nickname) = :nickname_legacy
               )
             LIMIT 1'
        );
        $statement->execute([
            'user_id' => $userId,
            'nickname_username' => $nickname,
            'nickname_profile' => $nickname,
            'nickname_legacy' => $nickname,
        ]);

        return !$statement->fetchColumn();
    }

    public function findPublicByNickname(string $nickname): ?array
    {
        $needle = strtolower(trim($nickname));
        $slug = $this->slugify($needle);

        if ($needle === '' || $slug === '') {
            return null;
        }

        $statement = $this->db()->prepare(
            'SELECT u.id,
                    u.username,
                    u.email,
                    u.nickname AS user_nickname,
                    u.role,
                    u.status,
                    u.created_at,
                    COALESCE(NULLIF(up.nickname, ""), NULLIF(p.nickname, ""), u.username) AS nickname,
                    COALESCE(NULLIF(up.nickname, ""), NULLIF(p.nickname, ""), u.username) AS profile_slug,
                    COALESCE(NULLIF(up.display_name, ""), NULLIF(p.display_name, ""), NULLIF(u.nickname, ""), u.username) AS display_name,
                    COALESCE(NULLIF(up.about, ""), NULLIF(p.bio, ""), "") AS about,
                    COALESCE(NULLIF(up.avatar, ""), NULLIF(p.avatar_path, "")) AS avatar,
                    COALESCE(NULLIF(up.cover_image, ""), NULLIF(p.cover_path, "")) AS cover_image,
                    up.instagram,
                    up.youtube,
                    COALESCE(NULLIF(up.website, ""), NULLIF(p.website_url, "")) AS website,
                    COALESCE(NULLIF(up.privacy, ""), "public") AS privacy,
                    COALESCE(NULLIF(up.message_privacy, ""), "everyone") AS message_privacy
             FROM users u
             LEFT JOIN user_profiles up ON up.user_id = u.id
             LEFT JOIN profiles p ON p.user_id = u.id
             WHERE u.status = "active"
               AND (
                    LOWER(u.username) = :needle_username
                    OR LOWER(u.nickname) = :needle_user_nickname
                    OR LOWER(up.nickname) = :needle_profile_nickname
                    OR LOWER(p.nickname) = :needle_legacy_nickname
                    OR LOWER(REPLACE(u.nickname, " ", "-")) = :slug_user_nickname
                    OR LOWER(REPLACE(up.nickname, " ", "-")) = :slug_profile_nickname
                    OR LOWER(REPLACE(p.nickname, " ", "-")) = :slug_legacy_nickname
               )
             LIMIT 1'
        );
        $statement->execute([
            'needle_username' => $needle,
            'needle_user_nickname' => $needle,
            'needle_profile_nickname' => $needle,
            'needle_legacy_nickname' => $needle,
            'slug_user_nickname' => $slug,
            'slug_profile_nickname' => $slug,
            'slug_legacy_nickname' => $slug,
        ]);
        $row = $statement->fetch();

        return $row ? $this->normalizeProfile($row) : null;
    }

    public function statsForUser(int $userId): array
    {
        $statement = $this->db()->prepare(
            'SELECT
                COALESCE((SELECT COUNT(*) FROM mods m WHERE COALESCE(m.user_id, m.creator_id) = :mods_user_id AND m.status IN ("published", "active")), 0) AS mod_count,
                COALESCE((SELECT COUNT(*) FROM music_tracks mt WHERE mt.user_id = :music_user_id AND mt.status IN ("published", "active")), 0) AS music_count,
                COALESCE((SELECT SUM(m.download_count) FROM mods m WHERE COALESCE(m.user_id, m.creator_id) = :downloads_user_id AND m.status IN ("published", "active")), 0) AS total_downloads,
                COALESCE((SELECT SUM(mt.play_count) FROM music_tracks mt WHERE mt.user_id = :plays_user_id AND mt.status IN ("published", "active")), 0) AS total_plays,
                COALESCE((SELECT SUM(m.view_count) FROM mods m WHERE COALESCE(m.user_id, m.creator_id) = :mod_views_user_id AND m.status IN ("published", "active")), 0)
                    + COALESCE((SELECT SUM(mt.view_count) FROM music_tracks mt WHERE mt.user_id = :music_views_user_id AND mt.status IN ("published", "active")), 0) AS total_views,
                COALESCE((SELECT COUNT(*) FROM subscriptions s WHERE s.creator_id = :sub_creator_id AND s.status = "active" AND (s.starts_at IS NULL OR s.starts_at <= NOW()) AND (s.ends_at IS NULL OR s.ends_at > NOW())), 0) AS subscriber_count,
                COALESCE((SELECT COUNT(*) FROM user_follows uf WHERE uf.followed_id = :followed_id), 0) AS follower_count,
                COALESCE((SELECT COUNT(*) FROM user_follows uf WHERE uf.follower_id = :following_id), 0) AS following_count'
        );
        $statement->execute([
            'mods_user_id' => $userId,
            'music_user_id' => $userId,
            'downloads_user_id' => $userId,
            'plays_user_id' => $userId,
            'mod_views_user_id' => $userId,
            'music_views_user_id' => $userId,
            'sub_creator_id' => $userId,
            'followed_id' => $userId,
            'following_id' => $userId,
        ]);
        $stats = $statement->fetch() ?: [];

        return [
            'mod_count' => (int) ($stats['mod_count'] ?? 0),
            'music_count' => (int) ($stats['music_count'] ?? 0),
            'total_downloads' => (int) ($stats['total_downloads'] ?? 0),
            'total_plays' => (int) ($stats['total_plays'] ?? 0),
            'total_views' => (int) ($stats['total_views'] ?? 0),
            'subscriber_count' => (int) ($stats['subscriber_count'] ?? 0),
            'follower_count' => (int) ($stats['follower_count'] ?? 0),
            'following_count' => (int) ($stats['following_count'] ?? 0),
        ];
    }

    public function follow(int $followerId, int $followedId): bool
    {
        if ($followerId <= 0 || $followedId <= 0 || $followerId === $followedId) {
            return false;
        }

        $statement = $this->db()->prepare(
            'INSERT INTO user_follows (follower_id, followed_id, created_at)
             VALUES (:follower_id, :followed_id, NOW())
             ON DUPLICATE KEY UPDATE created_at = created_at'
        );
        $statement->execute([
            'follower_id' => $followerId,
            'followed_id' => $followedId,
        ]);

        return true;
    }

    public function unfollow(int $followerId, int $followedId): bool
    {
        if ($followerId <= 0 || $followedId <= 0 || $followerId === $followedId) {
            return false;
        }

        $statement = $this->db()->prepare(
            'DELETE FROM user_follows
             WHERE follower_id = :follower_id AND followed_id = :followed_id'
        );
        $statement->execute([
            'follower_id' => $followerId,
            'followed_id' => $followedId,
        ]);

        return true;
    }

    public function isFollowing(int $followerId, int $followedId): bool
    {
        if ($followerId <= 0 || $followedId <= 0 || $followerId === $followedId) {
            return false;
        }

        $statement = $this->db()->prepare(
            'SELECT 1 FROM user_follows
             WHERE follower_id = :follower_id AND followed_id = :followed_id
             LIMIT 1'
        );
        $statement->execute([
            'follower_id' => $followerId,
            'followed_id' => $followedId,
        ]);

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

    public function followerCount(int $userId): int
    {
        $statement = $this->db()->prepare('SELECT COUNT(*) FROM user_follows WHERE followed_id = :user_id');
        $statement->execute(['user_id' => $userId]);

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

    public function publicSocialLinks(array $profile): array
    {
        $items = [
            'instagram' => ['label' => 'Instagram', 'value' => $profile['instagram'] ?? ''],
            'youtube' => ['label' => 'YouTube', 'value' => $profile['youtube'] ?? ''],
            'website' => ['label' => 'Website', 'value' => $profile['website'] ?? ''],
        ];
        $links = [];

        foreach ($items as $key => $item) {
            $url = $this->cleanExternalUrl((string) $item['value']);
            if ($url === '') {
                continue;
            }

            $links[] = [
                'key' => $key,
                'label' => $item['label'],
                'url' => $url,
            ];
        }

        return $links;
    }

    private function normalizeProfile(array $profile): array
    {
        $profile['id'] = (int) ($profile['id'] ?? 0);
        $profile['privacy'] = $this->normalizePrivacy((string) (($profile['privacy'] ?? '') ?: 'public'));
        $profile['message_privacy'] = $this->normalizeMessagePrivacy((string) (($profile['message_privacy'] ?? '') ?: 'everyone'));
        $profile['nickname'] = (string) (($profile['nickname'] ?? '') ?: ($profile['username'] ?? 'user'));
        $profile['profile_slug'] = $this->slugify((string) (($profile['profile_slug'] ?? '') ?: $profile['nickname']));
        $profile['display_name'] = (string) (($profile['display_name'] ?? '') ?: $profile['nickname']);
        $profile['about'] = (string) (($profile['about'] ?? '') ?: '');

        return $profile;
    }

    private function cleanExternalUrl(string $url): string
    {
        $url = trim($url);
        if ($url === '') {
            return '';
        }

        if (!preg_match('~^https?://~i', $url)) {
            $url = 'https://' . ltrim($url, '/');
        }

        return filter_var($url, FILTER_VALIDATE_URL) ? $url : '';
    }

    private function normalizePrivacy(string $privacy): string
    {
        if ($privacy === 'members') {
            $privacy = 'logged_in';
        }

        return in_array($privacy, ['public', 'logged_in', 'private'], true) ? $privacy : 'public';
    }

    private function normalizeMessagePrivacy(string $messagePrivacy): string
    {
        if ($messagePrivacy === 'members') {
            $messagePrivacy = 'everyone';
        }

        return in_array($messagePrivacy, ['everyone', 'friends', 'none'], true) ? $messagePrivacy : 'everyone';
    }

    private function slugify(string $value): string
    {
        $value = strtolower(trim($value));
        $value = preg_replace('~[^a-z0-9._-]+~', '-', $value) ?: '';
        $value = trim($value, '-');

        return substr($value, 0, 120);
    }
}
