<?php

declare(strict_types=1);

/**
 * توابع مدیریت Relation های دیتابیس
 * این فایل شامل توابعی برای JOIN queries و گرفتن داده‌های مرتبط است
 * توجه: PDO به صورت global استفاده می‌شود و نیازی به use نیست
 */

/**
 * دریافت مدیا با اطلاعات folder و admin
 * 
 * @return array{media: array, folder: ?array, admin: ?array}
 */
function getMediaWithRelations(PDO $pdo, int $mediaId): ?array
{
    $stmt = $pdo->prepare('
        SELECT 
            m.*,
            f.id as folder_id,
            f.name as folder_name,
            f.owner_admin_id as folder_owner_admin_id,
            a.id as admin_id,
            a.telegram_id as admin_telegram_id,
            a.is_owner as admin_is_owner
        FROM media m
        LEFT JOIN folders f ON m.folder_id = f.id
        LEFT JOIN admins a ON m.owner_admin_id = a.id
        WHERE m.id = :media_id
    ');
    
    $stmt->execute(['media_id' => $mediaId]);
    $row = $stmt->fetch();
    
    if ($row === false) {
        return null;
    }
    
    return [
        'media' => [
            'id' => (int) $row['id'],
            'folder_id' => (int) $row['folder_id'],
            'owner_admin_id' => (int) $row['owner_admin_id'],
            'telegram_file_id' => $row['telegram_file_id'],
            'type' => $row['type'],
            'uuid' => $row['uuid'],
            'thumbnail_file_id' => $row['thumbnail_file_id'],
            'thumbnail_enabled' => (bool) $row['thumbnail_enabled'],
            'caption' => $row['caption'],
            'auto_delete_seconds' => $row['auto_delete_seconds'] !== null ? (int) $row['auto_delete_seconds'] : null,
            'link_in_caption_enabled' => (bool) $row['link_in_caption_enabled'],
        ],
        'folder' => $row['folder_id'] !== null ? [
            'id' => (int) $row['folder_id'],
            'name' => $row['folder_name'],
            'owner_admin_id' => (int) $row['folder_owner_admin_id'],
        ] : null,
        'admin' => $row['admin_id'] !== null ? [
            'id' => (int) $row['admin_id'],
            'telegram_id' => (int) $row['admin_telegram_id'],
            'is_owner' => (bool) $row['admin_is_owner'],
        ] : null,
    ];
}

/**
 * دریافت تمام مدیاهای یک folder با اطلاعات admin
 * 
 * @return array<array{media: array, admin: ?array}>
 */
function getMediaByFolderWithRelations(PDO $pdo, int $folderId): array
{
    $stmt = $pdo->prepare('
        SELECT 
            m.*,
            a.id as admin_id,
            a.telegram_id as admin_telegram_id,
            a.is_owner as admin_is_owner
        FROM media m
        LEFT JOIN admins a ON m.owner_admin_id = a.id
        WHERE m.folder_id = :folder_id
        ORDER BY m.id DESC
    ');
    
    $stmt->execute(['folder_id' => $folderId]);
    $rows = $stmt->fetchAll();
    
    $result = [];
    foreach ($rows as $row) {
        $result[] = [
            'media' => [
                'id' => (int) $row['id'],
                'folder_id' => (int) $row['folder_id'],
                'owner_admin_id' => (int) $row['owner_admin_id'],
                'telegram_file_id' => $row['telegram_file_id'],
                'type' => $row['type'],
                'uuid' => $row['uuid'],
                'thumbnail_file_id' => $row['thumbnail_file_id'],
                'thumbnail_enabled' => (bool) $row['thumbnail_enabled'],
                'caption' => $row['caption'],
                'auto_delete_seconds' => $row['auto_delete_seconds'] !== null ? (int) $row['auto_delete_seconds'] : null,
                'link_in_caption_enabled' => (bool) $row['link_in_caption_enabled'],
            ],
            'admin' => $row['admin_id'] !== null ? [
                'id' => (int) $row['admin_id'],
                'telegram_id' => (int) $row['admin_telegram_id'],
                'is_owner' => (bool) $row['admin_is_owner'],
            ] : null,
        ];
    }
    
    return $result;
}

/**
 * دریافت payment با اطلاعات user
 * 
 * @return array{payment: array, user: ?array}
 */
function getPaymentWithRelations(PDO $pdo, int $paymentId): ?array
{
    $stmt = $pdo->prepare('
        SELECT 
            p.*,
            u.id as user_id,
            u.telegram_id as user_telegram_id,
            u.mode as user_mode
        FROM payments p
        LEFT JOIN users u ON p.user_id = u.id
        WHERE p.id = :payment_id
    ');
    
    $stmt->execute(['payment_id' => $paymentId]);
    $row = $stmt->fetch();
    
    if ($row === false) {
        return null;
    }
    
    return [
        'payment' => [
            'id' => (int) $row['id'],
            'user_id' => (int) $row['user_id'],
            'gateway' => $row['gateway'],
            'amount' => (int) $row['amount'],
            'status' => $row['status'],
            'external_id' => $row['external_id'],
            'payload' => $row['payload'],
            'created_at' => $row['created_at'],
        ],
        'user' => $row['user_id'] !== null ? [
            'id' => (int) $row['user_id'],
            'telegram_id' => (int) $row['user_telegram_id'],
            'mode' => $row['user_mode'],
        ] : null,
    ];
}

/**
 * دریافت analytics event با اطلاعات مرتبط
 * 
 * @return array{event: array, user: ?array, media: ?array, payment: ?array}
 */
function getAnalyticsEventWithRelations(PDO $pdo, int $eventId): ?array
{
    $stmt = $pdo->prepare('
        SELECT 
            ae.*,
            u.id as user_id,
            u.telegram_id as user_telegram_id,
            u.mode as user_mode,
            m.id as media_id,
            m.type as media_type,
            m.telegram_file_id as media_telegram_file_id,
            p.id as payment_id,
            p.gateway as payment_gateway,
            p.amount as payment_amount
        FROM analytics_events ae
        LEFT JOIN users u ON ae.user_id = u.id
        LEFT JOIN media m ON ae.media_id = m.id
        LEFT JOIN payments p ON ae.payment_id = p.id
        WHERE ae.id = :event_id
    ');
    
    $stmt->execute(['event_id' => $eventId]);
    $row = $stmt->fetch();
    
    if ($row === false) {
        return null;
    }
    
    return [
        'event' => [
            'id' => (int) $row['id'],
            'type' => $row['type'],
            'user_id' => $row['user_id'] !== null ? (int) $row['user_id'] : null,
            'media_id' => $row['media_id'] !== null ? (int) $row['media_id'] : null,
            'payment_id' => $row['payment_id'] !== null ? (int) $row['payment_id'] : null,
            'occurred_at' => $row['occurred_at'],
        ],
        'user' => $row['user_id'] !== null ? [
            'id' => (int) $row['user_id'],
            'telegram_id' => (int) $row['user_telegram_id'],
            'mode' => $row['user_mode'],
        ] : null,
        'media' => $row['media_id'] !== null ? [
            'id' => (int) $row['media_id'],
            'type' => $row['media_type'],
            'telegram_file_id' => $row['media_telegram_file_id'],
        ] : null,
        'payment' => $row['payment_id'] !== null ? [
            'id' => (int) $row['payment_id'],
            'gateway' => $row['payment_gateway'],
            'amount' => (int) $row['payment_amount'],
        ] : null,
    ];
}

/**
 * دریافت log با اطلاعات مرتبط
 * 
 * @return array{log: array, user: ?array, admin: ?array, media: ?array}
 */
function getLogWithRelations(PDO $pdo, int $logId): ?array
{
    $stmt = $pdo->prepare('
        SELECT 
            l.*,
            u.id as user_id,
            u.telegram_id as user_telegram_id,
            u.mode as user_mode,
            a.id as admin_id,
            a.telegram_id as admin_telegram_id,
            a.is_owner as admin_is_owner,
            m.id as media_id,
            m.type as media_type
        FROM logs l
        LEFT JOIN users u ON l.user_id = u.id
        LEFT JOIN admins a ON l.admin_id = a.id
        LEFT JOIN media m ON l.media_id = m.id
        WHERE l.id = :log_id
    ');
    
    $stmt->execute(['log_id' => $logId]);
    $row = $stmt->fetch();
    
    if ($row === false) {
        return null;
    }
    
    return [
        'log' => [
            'id' => (int) $row['id'],
            'level' => $row['level'],
            'message' => $row['message'],
            'context' => $row['context'] !== null ? json_decode($row['context'], true) : null,
            'file' => $row['file'],
            'line' => $row['line'] !== null ? (int) $row['line'] : null,
            'telegram_id' => $row['telegram_id'] !== null ? (int) $row['telegram_id'] : null,
            'user_id' => $row['user_id'] !== null ? (int) $row['user_id'] : null,
            'admin_id' => $row['admin_id'] !== null ? (int) $row['admin_id'] : null,
            'media_id' => $row['media_id'] !== null ? (int) $row['media_id'] : null,
            'exception_type' => $row['exception_type'],
            'exception_message' => $row['exception_message'],
            'exception_trace' => $row['exception_trace'],
            'created_at' => $row['created_at'],
        ],
        'user' => $row['user_id'] !== null ? [
            'id' => (int) $row['user_id'],
            'telegram_id' => (int) $row['user_telegram_id'],
            'mode' => $row['user_mode'],
        ] : null,
        'admin' => $row['admin_id'] !== null ? [
            'id' => (int) $row['admin_id'],
            'telegram_id' => (int) $row['admin_telegram_id'],
            'is_owner' => (bool) $row['admin_is_owner'],
        ] : null,
        'media' => $row['media_id'] !== null ? [
            'id' => (int) $row['media_id'],
            'type' => $row['media_type'],
        ] : null,
    ];
}

/**
 * دریافت folder با تمام مدیاها و اطلاعات admin
 * 
 * @return array{folder: array, admin: ?array, media: array<array>}
 */
function getFolderWithRelations(PDO $pdo, int $folderId): ?array
{
    $stmt = $pdo->prepare('
        SELECT 
            f.*,
            a.id as admin_id,
            a.telegram_id as admin_telegram_id,
            a.is_owner as admin_is_owner
        FROM folders f
        LEFT JOIN admins a ON f.owner_admin_id = a.id
        WHERE f.id = :folder_id
    ');
    
    $stmt->execute(['folder_id' => $folderId]);
    $row = $stmt->fetch();
    
    if ($row === false) {
        return null;
    }
    
    // دریافت مدیاهای این folder
    $mediaStmt = $pdo->prepare('SELECT * FROM media WHERE folder_id = :folder_id ORDER BY id DESC');
    $mediaStmt->execute(['folder_id' => $folderId]);
    $mediaRows = $mediaStmt->fetchAll();
    
    $media = [];
    foreach ($mediaRows as $mediaRow) {
        $media[] = [
            'id' => (int) $mediaRow['id'],
            'folder_id' => (int) $mediaRow['folder_id'],
            'owner_admin_id' => (int) $mediaRow['owner_admin_id'],
            'telegram_file_id' => $mediaRow['telegram_file_id'],
            'type' => $mediaRow['type'],
            'uuid' => $mediaRow['uuid'],
            'thumbnail_file_id' => $mediaRow['thumbnail_file_id'],
            'thumbnail_enabled' => (bool) $mediaRow['thumbnail_enabled'],
            'caption' => $mediaRow['caption'],
            'auto_delete_seconds' => $mediaRow['auto_delete_seconds'] !== null ? (int) $mediaRow['auto_delete_seconds'] : null,
            'link_in_caption_enabled' => (bool) $mediaRow['link_in_caption_enabled'],
        ];
    }
    
    return [
        'folder' => [
            'id' => (int) $row['id'],
            'owner_admin_id' => (int) $row['owner_admin_id'],
            'name' => $row['name'],
        ],
        'admin' => $row['admin_id'] !== null ? [
            'id' => (int) $row['admin_id'],
            'telegram_id' => (int) $row['admin_telegram_id'],
            'is_owner' => (bool) $row['admin_is_owner'],
        ] : null,
        'media' => $media,
    ];
}

/**
 * دریافت تمام folders یک admin با تعداد مدیاها
 * 
 * @return array<array{folder: array, admin: ?array, media_count: int}>
 */
function getFoldersByAdminWithRelations(PDO $pdo, int $adminId): array
{
    $stmt = $pdo->prepare('
        SELECT 
            f.*,
            a.id as admin_id,
            a.telegram_id as admin_telegram_id,
            a.is_owner as admin_is_owner,
            COUNT(m.id) as media_count
        FROM folders f
        LEFT JOIN admins a ON f.owner_admin_id = a.id
        LEFT JOIN media m ON f.id = m.folder_id
        WHERE f.owner_admin_id = :admin_id
        GROUP BY f.id
        ORDER BY f.id DESC
    ');
    
    $stmt->execute(['admin_id' => $adminId]);
    $rows = $stmt->fetchAll();
    
    $result = [];
    foreach ($rows as $row) {
        $result[] = [
            'folder' => [
                'id' => (int) $row['id'],
                'owner_admin_id' => (int) $row['owner_admin_id'],
                'name' => $row['name'],
            ],
            'admin' => $row['admin_id'] !== null ? [
                'id' => (int) $row['admin_id'],
                'telegram_id' => (int) $row['admin_telegram_id'],
                'is_owner' => (bool) $row['admin_is_owner'],
            ] : null,
            'media_count' => (int) $row['media_count'],
        ];
    }
    
    return $result;
}

/**
 * بررسی وجود relation بین دو جدول
 * 
 * @param string $table1 نام جدول اول
 * @param string $column1 نام ستون در جدول اول
 * @param int $value1 مقدار در جدول اول
 * @param string $table2 نام جدول دوم
 * @param string $column2 نام ستون در جدول دوم
 * @return bool
 */
function checkRelationExists(PDO $pdo, string $table1, string $column1, int $value1, string $table2, string $column2): bool
{
    try {
        // Whitelist برای نام جداول و ستون‌ها برای جلوگیری از SQL Injection
        $allowedTables = ['users', 'admins', 'folders', 'media', 'payments', 'ads', 'analytics_events', 'logs'];
        $allowedColumns = ['id', 'user_id', 'admin_id', 'folder_id', 'media_id', 'payment_id', 'owner_admin_id', 'telegram_id'];
        
        if (!in_array($table2, $allowedTables, true) || !in_array($column2, $allowedColumns, true)) {
            return false;
        }
        
        $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM `{$table2}` WHERE `{$column2}` = :value");
        $stmt->execute(['value' => $value1]);
        $result = $stmt->fetch();
        return ($result['count'] ?? 0) > 0;
    } catch (PDOException $e) {
        return false;
    }
}

/**
 * حذف cascade (در صورت نیاز)
 * حذف تمام رکوردهای مرتبط با یک رکورد
 * 
 * @param string $table نام جدول
 * @param string $column نام ستون
 * @param int $value مقدار
 * @return int تعداد رکوردهای حذف شده
 */
function deleteCascade(PDO $pdo, string $table, string $column, int $value): int
{
    try {
        // Whitelist برای نام جداول و ستون‌ها برای جلوگیری از SQL Injection
        $allowedTables = ['users', 'admins', 'folders', 'media', 'payments', 'ads', 'analytics_events', 'logs'];
        $allowedColumns = ['id', 'user_id', 'admin_id', 'folder_id', 'media_id', 'payment_id', 'owner_admin_id', 'telegram_id'];
        
        if (!in_array($table, $allowedTables, true) || !in_array($column, $allowedColumns, true)) {
            return 0;
        }
        
        $stmt = $pdo->prepare("DELETE FROM `{$table}` WHERE `{$column}` = :value");
        $stmt->execute(['value' => $value]);
        return $stmt->rowCount();
    } catch (PDOException $e) {
        return 0;
    }
}

/**
 * حذف media و تمام analytics events مرتبط
 */
function deleteMediaCascade(PDO $pdo, int $mediaId): int
{
    $deleted = 0;
    
    // حذف analytics events
    $stmt = $pdo->prepare('DELETE FROM analytics_events WHERE media_id = :media_id');
    $stmt->execute(['media_id' => $mediaId]);
    $deleted += $stmt->rowCount();
    
    // حذف logs
    $stmt = $pdo->prepare('DELETE FROM logs WHERE media_id = :media_id');
    $stmt->execute(['media_id' => $mediaId]);
    $deleted += $stmt->rowCount();
    
    // حذف media
    $stmt = $pdo->prepare('DELETE FROM media WHERE id = :media_id');
    $stmt->execute(['media_id' => $mediaId]);
    $deleted += $stmt->rowCount();
    
    return $deleted;
}

/**
 * حذف user و تمام داده‌های مرتبط
 */
function deleteUserCascade(PDO $pdo, int $userId): int
{
    $deleted = 0;
    
    // حذف payments
    $stmt = $pdo->prepare('DELETE FROM payments WHERE user_id = :user_id');
    $stmt->execute(['user_id' => $userId]);
    $deleted += $stmt->rowCount();
    
    // حذف analytics events
    $stmt = $pdo->prepare('DELETE FROM analytics_events WHERE user_id = :user_id');
    $stmt->execute(['user_id' => $userId]);
    $deleted += $stmt->rowCount();
    
    // حذف logs
    $stmt = $pdo->prepare('DELETE FROM logs WHERE user_id = :user_id');
    $stmt->execute(['user_id' => $userId]);
    $deleted += $stmt->rowCount();
    
    // حذف user
    $stmt = $pdo->prepare('DELETE FROM users WHERE id = :user_id');
    $stmt->execute(['user_id' => $userId]);
    $deleted += $stmt->rowCount();
    
    return $deleted;
}

/**
 * حذف admin و تمام داده‌های مرتبط
 */
function deleteAdminCascade(PDO $pdo, int $adminId): int
{
    $deleted = 0;
    
    // حذف folders (که media ها را هم حذف می‌کند)
    $foldersStmt = $pdo->prepare('SELECT id FROM folders WHERE owner_admin_id = :admin_id');
    $foldersStmt->execute(['admin_id' => $adminId]);
    $folders = $foldersStmt->fetchAll();
    
    foreach ($folders as $folder) {
        $folderId = (int) $folder['id'];
        
        // حذف media های این folder
        $mediaStmt = $pdo->prepare('SELECT id FROM media WHERE folder_id = :folder_id');
        $mediaStmt->execute(['folder_id' => $folderId]);
        $medias = $mediaStmt->fetchAll();
        
        foreach ($medias as $media) {
            $deleted += deleteMediaCascade($pdo, (int) $media['id']);
        }
        
        // حذف folder
        $stmt = $pdo->prepare('DELETE FROM folders WHERE id = :folder_id');
        $stmt->execute(['folder_id' => $folderId]);
        $deleted += $stmt->rowCount();
    }
    
    // حذف ads
    $stmt = $pdo->prepare('DELETE FROM ads WHERE owner_admin_id = :admin_id');
    $stmt->execute(['admin_id' => $adminId]);
    $deleted += $stmt->rowCount();
    
    // حذف logs
    $stmt = $pdo->prepare('DELETE FROM logs WHERE admin_id = :admin_id');
    $stmt->execute(['admin_id' => $adminId]);
    $deleted += $stmt->rowCount();
    
    // حذف admin
    $stmt = $pdo->prepare('DELETE FROM admins WHERE id = :admin_id');
    $stmt->execute(['admin_id' => $adminId]);
    $deleted += $stmt->rowCount();
    
    return $deleted;
}

