Stored Procedure های کامل برای مدیریت جداول سامانه درج آگهی

DELIMITER $$

CREATE PROCEDURE sp_GetUserInfo(
    IN p_UserID INT
)
BEGIN
    SELECT 
        u.UserID,
        u.Username,
        u.Email,
        u.FirstName,
        u.LastName,
        u.Phone,
        u.Avatar,
        u.UserType,
        u.IsVerified,
        u.IsActive,
        u.CreatedAt,
        u.LastLogin,
        up.CompanyName,
        up.Website,
        up.Address,
        up.City,
        up.Province,
        up.PostalCode,
        up.Bio,
        up.SocialMedia,
        up.NotificationPreferences,
        p.PackageName,
        p.PackageType,
        s.StartDate as SubscriptionStart,
        s.EndDate as SubscriptionEnd,
        p.MaxAds,
        p.MaxImagesPerAd,
        p.AutoApprove,
        (SELECT COUNT(*) FROM Advertisements WHERE UserID = p_UserID) as TotalAds,
        (SELECT COUNT(*) FROM Advertisements WHERE UserID = p_UserID AND Status = 'Approved') as ActiveAds
    FROM Users u
    LEFT JOIN UserProfiles up ON u.UserID = up.UserID
    LEFT JOIN UserSubscriptions s ON u.UserID = s.UserID AND s.IsActive = TRUE
    LEFT JOIN UserPackages p ON s.PackageID = p.PackageID
    WHERE u.UserID = p_UserID;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_UpdateUserProfile(
    IN p_UserID INT,
    IN p_FirstName NVARCHAR(50),
    IN p_LastName NVARCHAR(50),
    IN p_Phone VARCHAR(15),
    IN p_Avatar VARCHAR(255),
    IN p_CompanyName NVARCHAR(200),
    IN p_Website VARCHAR(200),
    IN p_Address TEXT,
    IN p_City NVARCHAR(50),
    IN p_Province NVARCHAR(50),
    IN p_PostalCode VARCHAR(10),
    IN p_Bio TEXT,
    IN p_SocialMedia JSON,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در به‌روزرسانی پروفایل';
    END;
    
    START TRANSACTION;
    
    -- به‌روزرسانی اطلاعات کاربر
    UPDATE Users 
    SET 
        FirstName = p_FirstName,
        LastName = p_LastName,
        Phone = p_Phone,
        Avatar = p_Avatar,
        UpdatedAt = CURRENT_TIMESTAMP
    WHERE UserID = p_UserID;
    
    -- به‌روزرسانی پروفایل
    UPDATE UserProfiles 
    SET 
        CompanyName = p_CompanyName,
        Website = p_Website,
        Address = p_Address,
        City = p_City,
        Province = p_Province,
        PostalCode = p_PostalCode,
        Bio = p_Bio,
        SocialMedia = p_SocialMedia,
        UpdatedAt = CURRENT_TIMESTAMP
    WHERE UserID = p_UserID;
    
    SET p_ResultMessage = 'پروفایل با موفقیت به‌روزرسانی شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetAds(
    IN p_UserID INT,
    IN p_CategoryID INT,
    IN p_Status ENUM('Draft', 'Pending', 'Approved', 'Rejected', 'Expired', 'Sold'),
    IN p_IsPremium BOOLEAN,
    IN p_IsUrgent BOOLEAN,
    IN p_SearchTerm NVARCHAR(200),
    IN p_MinPrice DECIMAL(15,2),
    IN p_MaxPrice DECIMAL(15,2),
    IN p_City NVARCHAR(50),
    IN p_Province NVARCHAR(50),
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT,
    OUT p_TotalPages INT
)
BEGIN
    DECLARE where_conditions TEXT DEFAULT '1=1';
    DECLARE query_params TEXT DEFAULT '';
    
    -- ساخت شرط‌های WHERE
    IF p_UserID IS NOT NULL THEN
        SET where_conditions = CONCAT(where_conditions, ' AND a.UserID = ?');
        SET query_params = CONCAT(query_params, 'i');
    END IF;
    
    IF p_CategoryID IS NOT NULL THEN
        SET where_conditions = CONCAT(where_conditions, ' AND a.CategoryID = ?');
        SET query_params = CONCAT(query_params, 'i');
    END IF;
    
    IF p_Status IS NOT NULL THEN
        SET where_conditions = CONCAT(where_conditions, ' AND a.Status = ?');
        SET query_params = CONCAT(query_params, 's');
    END IF;
    
    IF p_IsPremium IS NOT NULL THEN
        SET where_conditions = CONCAT(where_conditions, ' AND a.IsPremium = ?');
        SET query_params = CONCAT(query_params, 'i');
    END IF;
    
    IF p_IsUrgent IS NOT NULL THEN
        SET where_conditions = CONCAT(where_conditions, ' AND a.IsUrgent = ?');
        SET query_params = CONCAT(query_params, 'i');
    END IF;
    
    IF p_MinPrice IS NOT NULL THEN
        SET where_conditions = CONCAT(where_conditions, ' AND a.Price >= ?');
        SET query_params = CONCAT(query_params, 'd');
    END IF;
    
    IF p_MaxPrice IS NOT NULL THEN
        SET where_conditions = CONCAT(where_conditions, ' AND a.Price <= ?');
        SET query_params = CONCAT(query_params, 'd');
    END IF;
    
    IF p_City IS NOT NULL THEN
        SET where_conditions = CONCAT(where_conditions, ' AND JSON_EXTRACT(a.LocationInfo, ''$.city'') = ?');
        SET query_params = CONCAT(query_params, 's');
    END IF;
    
    IF p_Province IS NOT NULL THEN
        SET where_conditions = CONCAT(where_conditions, ' AND JSON_EXTRACT(a.LocationInfo, ''$.province'') = ?');
        SET query_params = CONCAT(query_params, 's');
    END IF;
    
    -- اگر جستجو وجود دارد
    IF p_SearchTerm IS NOT NULL THEN
        SET where_conditions = CONCAT(where_conditions, 
            ' AND (MATCH(a.Title, a.Description, a.ShortDescription) AGAINST(? IN NATURAL LANGUAGE MODE)',
            ' OR a.Title LIKE ? OR a.Description LIKE ?)');
        SET query_params = CONCAT(query_params, 'sss');
    END IF;
    
    -- محاسبه تعداد کل رکوردها
    SET @count_query = CONCAT(
        'SELECT COUNT(*) INTO @total_records ',
        'FROM Advertisements a ',
        'WHERE ', where_conditions
    );
    
    PREPARE count_stmt FROM @count_query;
    
    -- bind parameters برای count query
    SET @param_index = 1;
    IF p_UserID IS NOT NULL THEN SET @user_id = p_UserID; END IF;
    IF p_CategoryID IS NOT NULL THEN SET @category_id = p_CategoryID; END IF;
    IF p_Status IS NOT NULL THEN SET @status = p_Status; END IF;
    IF p_IsPremium IS NOT NULL THEN SET @is_premium = p_IsPremium; END IF;
    IF p_IsUrgent IS NOT NULL THEN SET @is_urgent = p_IsUrgent; END IF;
    IF p_MinPrice IS NOT NULL THEN SET @min_price = p_MinPrice; END IF;
    IF p_MaxPrice IS NOT NULL THEN SET @max_price = p_MaxPrice; END IF;
    IF p_City IS NOT NULL THEN SET @city = p_City; END IF;
    IF p_Province IS NOT NULL THEN SET @province = p_Province; END IF;
    IF p_SearchTerm IS NOT NULL THEN 
        SET @search_term = p_SearchTerm;
        SET @search_like = CONCAT('%', p_SearchTerm, '%');
    END IF;
    
    EXECUTE count_stmt;
    DEALLOCATE PREPARE count_stmt;
    
    SET p_TotalRecords = @total_records;
    SET p_TotalPages = CEIL(p_TotalRecords / p_PageSize);
    
    -- گرفتن داده‌ها
    SET @data_query = CONCAT(
        'SELECT ',
        'a.AdID, a.Title, a.Slug, a.ShortDescription, a.Price, a.Currency, ',
        'a.IsNegotiable, a.Status, a.IsPremium, a.IsUrgent, a.ViewCount, ',
        'a.LikeCount, a.ExpiryDate, a.CreatedAt, a.FeaturedUntil, ',
        'c.CategoryID, c.CategoryName, c.CategorySlug, ',
        'u.UserID, u.Username, u.FirstName, u.LastName, up.CompanyName, ',
        '(SELECT ImagePath FROM AdImages WHERE AdID = a.AdID AND IsPrimary = TRUE LIMIT 1) as PrimaryImage, ',
        '(SELECT COUNT(*) FROM Favorites WHERE AdID = a.AdID) as FavoriteCount, ',
        'CASE ',
        '    WHEN a.FeaturedUntil > NOW() THEN TRUE ',
        '    ELSE FALSE ',
        'END as IsFeatured, ',
        'CASE ',
        '    WHEN a.ExpiryDate < NOW() THEN TRUE ',
        '    ELSE FALSE ',
        'END as IsExpired ',
        'FROM Advertisements a ',
        'INNER JOIN Categories c ON a.CategoryID = c.CategoryID ',
        'INNER JOIN Users u ON a.UserID = u.UserID ',
        'LEFT JOIN UserProfiles up ON u.UserID = up.UserID ',
        'WHERE ', where_conditions, ' ',
        'ORDER BY ',
        '    IsFeatured DESC, ',
        '    a.IsPremium DESC, ',
        '    a.IsUrgent DESC, ',
        '    a.CreatedAt DESC ',
        'LIMIT ? OFFSET ?'
    );
    
    SET query_params = CONCAT(query_params, 'ii');
    
    PREPARE data_stmt FROM @data_query;
    
    -- bind parameters برای data query
    SET @limit = p_PageSize;
    SET @offset = (p_Page - 1) * p_PageSize;
    
    EXECUTE data_stmt;
    DEALLOCATE PREPARE data_stmt;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetAdDetails(
    IN p_AdID INT,
    IN p_IncrementViews BOOLEAN
)
BEGIN
    -- افزایش تعداد بازدیدها اگر لازم باشد
    IF p_IncrementViews THEN
        UPDATE Advertisements 
        SET ViewCount = ViewCount + 1 
        WHERE AdID = p_AdID;
        
        -- ثبت در جدول آمار بازدیدها
        INSERT INTO AdViews (AdID, ViewDate) 
        VALUES (p_AdID, NOW());
    END IF;
    
    -- اطلاعات اصلی آگهی
    SELECT 
        a.*,
        c.CategoryName,
        c.CategorySlug,
        u.UserID,
        u.Username,
        u.FirstName,
        u.LastName,
        u.Phone as UserPhone,
        up.CompanyName,
        up.Website as CompanyWebsite,
        DATEDIFF(a.ExpiryDate, NOW()) as DaysUntilExpiry,
        CASE 
            WHEN a.ExpiryDate < NOW() THEN TRUE 
            ELSE FALSE 
        END as IsExpired
    FROM Advertisements a
    INNER JOIN Categories c ON a.CategoryID = c.CategoryID
    INNER JOIN Users u ON a.UserID = u.UserID
    LEFT JOIN UserProfiles up ON u.UserID = up.UserID
    WHERE a.AdID = p_AdID;
    
    -- تصاویر آگهی
    SELECT 
        ImageID,
        ImagePath,
        ImageThumbnail,
        ImageAlt,
        IsPrimary,
        SortOrder
    FROM AdImages
    WHERE AdID = p_AdID
    ORDER BY IsPrimary DESC, SortOrder ASC;
    
    -- ویژگی‌های آگهی
    SELECT 
        aa.AttributeID,
        aa.AttributeName,
        aa.AttributeType,
        CASE 
            WHEN aa.AttributeType = 'Text' THEN aav.StringValue
            WHEN aa.AttributeType = 'Number' THEN aav.NumberValue
            WHEN aa.AttributeType = 'Boolean' THEN aav.BooleanValue
            WHEN aa.AttributeType = 'Date' THEN aav.DateValue
            ELSE NULL
        END as AttributeValue
    FROM AdAttributeValues aav
    INNER JOIN AdAttributes aa ON aav.AttributeID = aa.AttributeID
    WHERE aav.AdID = p_AdID
    ORDER BY aa.SortOrder;
    
    -- نظرات و امتیازات
    SELECT 
        r.ReviewID,
        r.Rating,
        r.Comment,
        r.CreatedAt,
        u.UserID,
        u.Username,
        u.FirstName,
        u.LastName
    FROM Reviews r
    INNER JOIN Users u ON r.UserID = u.UserID
    WHERE r.AdID = p_AdID AND r.IsApproved = TRUE
    ORDER BY r.CreatedAt DESC;
    
    -- آمار بازدیدهای اخیر
    SELECT 
        COUNT(*) as TotalViews,
        COUNT(DISTINCT IPAddress) as UniqueVisitors,
        DATE(ViewDate) as ViewDate
    FROM AdViews
    WHERE AdID = p_AdID AND ViewDate >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    GROUP BY DATE(ViewDate)
    ORDER BY ViewDate DESC;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateAd(
    IN p_UserID INT,
    IN p_CategoryID INT,
    IN p_Title NVARCHAR(200),
    IN p_Description TEXT,
    IN p_ShortDescription NVARCHAR(500),
    IN p_Price DECIMAL(15,2),
    IN p_Currency VARCHAR(3),
    IN p_IsNegotiable BOOLEAN,
    IN p_ContactInfo JSON,
    IN p_LocationInfo JSON,
    IN p_IsPremium BOOLEAN,
    IN p_IsUrgent BOOLEAN,
    IN p_AttributeValues JSON,
    OUT p_AdID INT,
    OUT p_Slug VARCHAR(250),
    OUT p_Status ENUM('Draft', 'Pending', 'Approved', 'Rejected', 'Expired', 'Sold'),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_UserPackageType VARCHAR(20);
    DECLARE v_AutoApprove BOOLEAN;
    DECLARE v_ActiveAdsCount INT;
    DECLARE v_MaxAds INT;
    DECLARE v_ExpiryDays INT;
    DECLARE v_ExpiryDate DATETIME;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ایجاد آگهی';
        SET p_AdID = -1;
    END;
    
    START TRANSACTION;
    
    -- بررسی وضعیت کاربر و بسته‌اش
    SELECT 
        p.PackageType, 
        p.AutoApprove,
        p.MaxAds,
        (SELECT COUNT(*) FROM Advertisements WHERE UserID = p_UserID AND Status IN ('Pending', 'Approved')) as ActiveAds
    INTO v_UserPackageType, v_AutoApprove, v_MaxAds, v_ActiveAdsCount
    FROM UserSubscriptions s
    INNER JOIN UserPackages p ON s.PackageID = p.PackageID
    WHERE s.UserID = p_UserID AND s.IsActive = TRUE AND s.EndDate > NOW();
    
    IF v_UserPackageType IS NULL THEN
        SET p_ResultMessage = 'کاربر بسته فعال ندارد';
        SET p_AdID = -1;
        ROLLBACK;
    ELSEIF v_ActiveAdsCount >= v_MaxAds THEN
        SET p_ResultMessage = 'تعداد آگهی‌های فعال شما به حداکثر رسیده است';
        SET p_AdID = -1;
        ROLLBACK;
    ELSE
        -- تعیین وضعیت آگهی
        IF v_AutoApprove THEN
            SET p_Status = 'Approved';
        ELSE
            SET p_Status = 'Pending';
        END IF;
        
        -- تولید slug
        SET p_Slug = fn_GenerateAdSlug(p_Title);
        
        -- محاسبه تاریخ انقضا
        SET v_ExpiryDays = fn_GetSetting('default_ad_expiry_days');
        SET v_ExpiryDate = DATE_ADD(NOW(), INTERVAL v_ExpiryDays DAY);
        
        -- ایجاد آگهی
        INSERT INTO Advertisements (
            UserID, CategoryID, Title, Slug, Description, ShortDescription,
            Price, Currency, IsNegotiable, ContactInfo, LocationInfo,
            Status, IsPremium, IsUrgent, ExpiryDate
        ) VALUES (
            p_UserID, p_CategoryID, p_Title, p_Slug, p_Description, p_ShortDescription,
            p_Price, p_Currency, p_IsNegotiable, p_ContactInfo, p_LocationInfo,
            p_Status, p_IsPremium, p_IsUrgent, v_ExpiryDate
        );
        
        SET p_AdID = LAST_INSERT_ID();
        
        -- ذخیره ویژگی‌ها
        IF p_AttributeValues IS NOT NULL THEN
            CALL sp_SaveAdAttributes(p_AdID, p_AttributeValues);
        END IF;
        
        SET p_ResultMessage = 'آگهی با موفقیت ایجاد شد';
        COMMIT;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE FUNCTION fn_GenerateAdSlug(
    p_Title NVARCHAR(200)
) RETURNS VARCHAR(250)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE v_Slug VARCHAR(250);
    DECLARE v_BaseSlug VARCHAR(250);
    DECLARE v_Counter INT DEFAULT 1;
    DECLARE v_FinalSlug VARCHAR(250);
    
    -- تبدیل عنوان به slug
    SET v_Slug = LOWER(p_Title);
    SET v_Slug = REPLACE(v_Slug, ' ', '-');
    SET v_Slug = REGEXP_REPLACE(v_Slug, '[^a-z0-9-]', '');
    SET v_Slug = REGEXP_REPLACE(v_Slug, '-+', '-');
    SET v_Slug = TRIM(BOTH '-' FROM v_Slug);
    
    SET v_BaseSlug = v_Slug;
    SET v_FinalSlug = v_Slug;
    
    -- بررسی تکراری نبودن
    WHILE EXISTS (SELECT 1 FROM Advertisements WHERE Slug = v_FinalSlug) DO
        SET v_FinalSlug = CONCAT(v_BaseSlug, '-', v_Counter);
        SET v_Counter = v_Counter + 1;
    END WHILE;
    
    RETURN v_FinalSlug;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_SaveAdAttributes(
    IN p_AdID INT,
    IN p_AttributeValues JSON
)
BEGIN
    DECLARE v_AttributeID INT;
    DECLARE v_AttributeValue JSON;
    DECLARE v_AttributeType ENUM('Text', 'Number', 'Select', 'Boolean', 'Date');
    DECLARE v_Done INT DEFAULT FALSE;
    
    DECLARE cur_attributes CURSOR FOR 
        SELECT j.AttributeID, j.AttributeValue
        FROM JSON_TABLE(
            p_AttributeValues,
            '$[*]' COLUMNS(
                AttributeID INT PATH '$.attribute_id',
                AttributeValue JSON PATH '$.value'
            )
        ) j;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_Done = TRUE;
    
    OPEN cur_attributes;
    
    read_loop: LOOP
        FETCH cur_attributes INTO v_AttributeID, v_AttributeValue;
        IF v_Done THEN
            LEAVE read_loop;
        END IF;
        
        -- گرفتن نوع attribute
        SELECT AttributeType INTO v_AttributeType
        FROM AdAttributes
        WHERE AttributeID = v_AttributeID;
        
        -- حذف مقدار قبلی اگر وجود دارد
        DELETE FROM AdAttributeValues 
        WHERE AdID = p_AdID AND AttributeID = v_AttributeID;
        
        -- درج مقدار جدید
        INSERT INTO AdAttributeValues (AdID, AttributeID, StringValue, NumberValue, BooleanValue, DateValue)
        VALUES (
            p_AdID,
            v_AttributeID,
            CASE WHEN v_AttributeType IN ('Text', 'Select') THEN JSON_UNQUOTE(v_AttributeValue) ELSE NULL END,
            CASE WHEN v_AttributeType = 'Number' THEN CAST(JSON_UNQUOTE(v_AttributeValue) AS DECIMAL(15,2)) ELSE NULL END,
            CASE WHEN v_AttributeType = 'Boolean' THEN CAST(JSON_UNQUOTE(v_AttributeValue) AS BOOLEAN) ELSE NULL END,
            CASE WHEN v_AttributeType = 'Date' THEN CAST(JSON_UNQUOTE(v_AttributeValue) AS DATE) ELSE NULL END
        );
    END LOOP;
    
    CLOSE cur_attributes;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_AddAdImage(
    IN p_AdID INT,
    IN p_ImagePath VARCHAR(500),
    IN p_ImageThumbnail VARCHAR(500),
    IN p_ImageAlt NVARCHAR(200),
    IN p_IsPrimary BOOLEAN,
    OUT p_ImageID INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_ImageCount INT;
    DECLARE v_MaxImages INT;
    DECLARE v_UserID INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در اضافه کردن تصویر';
        SET p_ImageID = -1;
    END;
    
    START TRANSACTION;
    
    -- گرفتن کاربر و بررسی محدودیت تصاویر
    SELECT a.UserID INTO v_UserID
    FROM Advertisements a
    WHERE a.AdID = p_AdID;
    
    SELECT 
        p.MaxImagesPerAd,
        (SELECT COUNT(*) FROM AdImages WHERE AdID = p_AdID) as CurrentImages
    INTO v_MaxImages, v_ImageCount
    FROM UserSubscriptions s
    INNER JOIN UserPackages p ON s.PackageID = p.PackageID
    WHERE s.UserID = v_UserID AND s.IsActive = TRUE;
    
    IF v_ImageCount >= v_MaxImages THEN
        SET p_ResultMessage = 'تعداد تصاویر به حداکثر رسیده است';
        SET p_ImageID = -1;
        ROLLBACK;
    ELSE
        -- اگر این تصویر primary است، بقیه را non-primary کن
        IF p_IsPrimary THEN
            UPDATE AdImages 
            SET IsPrimary = FALSE 
            WHERE AdID = p_AdID;
        END IF;
        
        -- اگر اولین تصویر است، آن را primary کن
        IF v_ImageCount = 0 THEN
            SET p_IsPrimary = TRUE;
        END IF;
        
        -- اضافه کردن تصویر
        INSERT INTO AdImages (AdID, ImagePath, ImageThumbnail, ImageAlt, IsPrimary, SortOrder)
        VALUES (p_AdID, p_ImagePath, p_ImageThumbnail, p_ImageAlt, p_IsPrimary, v_ImageCount + 1);
        
        SET p_ImageID = LAST_INSERT_ID();
        SET p_ResultMessage = 'تصویر با موفقیت اضافه شد';
        COMMIT;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_ApproveRejectAd(
    IN p_AdID INT,
    IN p_AdminID INT,
    IN p_Status ENUM('Approved', 'Rejected'),
    IN p_RejectionReason TEXT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_CurrentStatus ENUM('Draft', 'Pending', 'Approved', 'Rejected', 'Expired', 'Sold');
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در به‌روزرسانی وضعیت آگهی';
    END;
    
    START TRANSACTION;
    
    -- گرفتن وضعیت فعلی
    SELECT Status INTO v_CurrentStatus
    FROM Advertisements
    WHERE AdID = p_AdID;
    
    IF v_CurrentStatus != 'Pending' THEN
        SET p_ResultMessage = 'آگهی در وضعیت درستی برای تایید/رد نیست';
        ROLLBACK;
    ELSE
        IF p_Status = 'Approved' THEN
            UPDATE Advertisements 
            SET 
                Status = 'Approved',
                ApprovedBy = p_AdminID,
                ApprovedAt = NOW(),
                RejectionReason = NULL
            WHERE AdID = p_AdID;
            
            SET p_ResultMessage = 'آگهی با موفقیت تایید شد';
        ELSE
            UPDATE Advertisements 
            SET 
                Status = 'Rejected',
                ApprovedBy = NULL,
                ApprovedAt = NULL,
                RejectionReason = p_RejectionReason
            WHERE AdID = p_AdID;
            
            SET p_ResultMessage = 'آگهی با موفقیت رد شد';
        END IF;
        
        COMMIT;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetPendingAds(
    IN p_AdminID INT,
    IN p_CategoryID INT,
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT
)
BEGIN
    -- محاسبه تعداد کل
    SELECT COUNT(*) INTO p_TotalRecords
    FROM Advertisements a
    WHERE a.Status = 'Pending'
    AND (p_CategoryID IS NULL OR a.CategoryID = p_CategoryID);
    
    -- گرفتن آگهی‌های در انتظار
    SELECT 
        a.AdID,
        a.Title,
        a.Slug,
        a.ShortDescription,
        a.Price,
        a.Currency,
        a.CreatedAt,
        c.CategoryName,
        u.UserID,
        u.Username,
        u.FirstName,
        u.LastName,
        up.CompanyName,
        (SELECT ImagePath FROM AdImages WHERE AdID = a.AdID AND IsPrimary = TRUE LIMIT 1) as PrimaryImage,
        JSON_EXTRACT(a.ContactInfo, '$.phone') as ContactPhone,
        JSON_EXTRACT(a.ContactInfo, '$.email') as ContactEmail
    FROM Advertisements a
    INNER JOIN Categories c ON a.CategoryID = c.CategoryID
    INNER JOIN Users u ON a.UserID = u.UserID
    LEFT JOIN UserProfiles up ON u.UserID = up.UserID
    WHERE a.Status = 'Pending'
    AND (p_CategoryID IS NULL OR a.CategoryID = p_CategoryID)
    ORDER BY a.CreatedAt ASC
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetSystemStats(
    IN p_AdminID INT
)
BEGIN
    -- آمار کلی
    SELECT 
        (SELECT COUNT(*) FROM Users) as TotalUsers,
        (SELECT COUNT(*) FROM Users WHERE IsVerified = TRUE) as VerifiedUsers,
        (SELECT COUNT(*) FROM Users WHERE CreatedAt >= DATE_SUB(NOW(), INTERVAL 30 DAY)) as NewUsersLast30Days,
        (SELECT COUNT(*) FROM Advertisements) as TotalAds,
        (SELECT COUNT(*) FROM Advertisements WHERE Status = 'Approved') as ActiveAds,
        (SELECT COUNT(*) FROM Advertisements WHERE Status = 'Pending') as PendingAds,
        (SELECT COUNT(*) FROM Advertisements WHERE CreatedAt >= DATE_SUB(NOW(), INTERVAL 30 DAY)) as NewAdsLast30Days,
        (SELECT COUNT(*) FROM Advertisements WHERE ExpiryDate < NOW()) as ExpiredAds,
        (SELECT SUM(Amount) FROM Transactions WHERE PaymentStatus = 'Completed') as TotalRevenue;
    
    -- آمار کاربران بر اساس بسته
    SELECT 
        p.PackageName,
        p.PackageType,
        COUNT(s.SubscriptionID) as UserCount
    FROM UserSubscriptions s
    INNER JOIN UserPackages p ON s.PackageID = p.PackageID
    WHERE s.IsActive = TRUE
    GROUP BY p.PackageID
    ORDER BY UserCount DESC;
    
    -- آمار آگهی‌ها بر اساس دسته‌بندی
    SELECT 
        c.CategoryName,
        COUNT(a.AdID) as AdCount,
        ROUND((COUNT(a.AdID) * 100.0 / (SELECT COUNT(*) FROM Advertisements)), 2) as Percentage
    FROM Categories c
    LEFT JOIN Advertisements a ON c.CategoryID = a.CategoryID
    GROUP BY c.CategoryID
    ORDER BY AdCount DESC;
END$$

DELIMITER ;

<?php
// فراخوانی stored procedure برای گرفتن آگهی‌ها
$categoryId = 1;
$page = 1;
$pageSize = 20;

$query = "CALL sp_GetAds(NULL, ?, 'Approved', NULL, NULL, NULL, NULL, NULL, NULL, NULL, ?, ?, @totalRecords, @totalPages)";
$stmt = $conn->prepare($query);
$stmt->bind_param("iii", $categoryId, $page, $pageSize);
$stmt->execute();
$result = $stmt->get_result();

$ads = [];
while ($row = $result->fetch_assoc()) {
    $ads[] = $row;
}

// گرفتن خروجی‌ها
$output = $conn->query("SELECT @totalRecords as total_records, @totalPages as total_pages");
$pagination = $output->fetch_assoc();

// فراخوانی برای ایجاد آگهی جدید
$userId = 123;
$title = "آگهی تستی";
$description = "توضیحات آگهی تستی";
// ... سایر پارامترها

$query = "CALL sp_CreateAd(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, @adId, @slug, @status, @resultMessage)";
$stmt = $conn->prepare($query);
$stmt->bind_param(
    "iisssdssssii",
    $userId, $categoryId, $title, $description, $shortDescription,
    $price, $currency, $isNegotiable, $contactInfo, $locationInfo,
    $isPremium, $isUrgent
);
$stmt->execute();

// گرفتن نتیجه
$result = $conn->query("SELECT @adId as ad_id, @slug as slug, @status as status, @resultMessage as message");
$output = $result->fetch_assoc();

if ($output['ad_id'] > 0) {
    echo "آگهی با شناسه " . $output['ad_id'] . " ایجاد شد";
} else {
    echo "خطا: " . $output['message'];
}

// فراخوانی برای گرفتن اطلاعات کامل آگهی
$adId = 456;
$incrementViews = true;

$query = "CALL sp_GetAdDetails(?, ?)";
$stmt = $conn->prepare($query);
$stmt->bind_param("ii", $adId, $incrementViews);
$stmt->execute();

// گرفتن multiple result sets
$adDetails = $stmt->get_result()->fetch_assoc();

$stmt->next_result();
$images = [];
while ($row = $stmt->get_result()->fetch_assoc()) {
    $images[] = $row;
}

$stmt->next_result();
$attributes = [];
while ($row = $stmt->get_result()->fetch_assoc()) {
    $attributes[] = $row;
}
?>