CREATE OR REPLACE PROCEDURE public.run_rating_calculation_procedures()
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT
            rcp.procedure_name,
            rcp.id
        FROM
            rating_calculation_procedures rcp
        WHERE
            rcp.is_enabled
    LOOP
        BEGIN
            EXECUTE format('CALL %I($1)', r.procedure_name) USING r.id;
        EXCEPTION WHEN OTHERS THEN
            RAISE WARNING 'Error executing procedure %: %', r.procedure_name, SQLERRM;
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.get_predicates_with_newer_ratings(creator_id int4)
RETURNS TABLE (
    predicate_id int4
) AS $$
BEGIN
    RETURN QUERY
    SELECT DISTINCT p.id AS predicate_id
    FROM
        predicates p
        JOIN ratings r ON r.predicate_id = p.id
    WHERE r.creation_time > (
        SELECT COALESCE(MAX(car.creation_time), 'epoch'::timestamp)
        FROM cached_aggregate_ratings car
        WHERE car.predicate_id = p.id AND car.creator_id = get_predicates_with_newer_ratings.creator_id
    );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION public.get_predicates_with_newer_ratings_or_delegations(creator_id int4)
RETURNS TABLE (
    predicate_id int4
) AS $$
BEGIN
    RETURN QUERY
    SELECT DISTINCT p.id AS predicate_id
    FROM predicates p
    WHERE 
        -- Predicates with newer ratings
        EXISTS (
            SELECT 1 
            FROM ratings r 
            WHERE r.predicate_id = p.id 
            AND r.creation_time > (
                SELECT COALESCE(MAX(car.creation_time), 'epoch'::timestamp)
                FROM cached_aggregate_ratings car
                WHERE car.predicate_id = p.id AND car.creator_id = get_predicates_with_newer_ratings_or_delegations.creator_id
            )
        )
        OR
        -- Predicates with tags that have newer delegations
        EXISTS (
            SELECT 1
            FROM predicate_tag_links ptl
            JOIN delegations d ON d.tag_id = ptl.tag_id
            WHERE ptl.predicate_id = p.id
            AND d.creation_time > (
                SELECT COALESCE(MAX(car.creation_time), 'epoch'::timestamp)
                FROM cached_aggregate_ratings car
                WHERE car.predicate_id = p.id AND car.creator_id = get_predicates_with_newer_ratings_or_delegations.creator_id
            )
        );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE public.rating_calculation_procedure_mean(id int4)
AS $$
DECLARE
    cid int4;
BEGIN
    SELECT creator_id
    INTO STRICT cid
    FROM rating_calculation_procedures rcp
    WHERE rcp.id = rating_calculation_procedure_mean.id
    LIMIT 1;

    IF cid IS NULL THEN
        RAISE EXCEPTION 'Creator ID not found for procedure ID %', rating_calculation_procedure_mean.id;
    END IF;

    -- Get predicates whose ratings need to be recalculated,
    -- using the helper function to get newer ratings.
    -- Calculate aggregate ratings and numbers for those predicates using an aggregate query.
    -- Insert the result to cached_aggregate_ratings table (do not update).
    -- All of this in one query!

    INSERT INTO cached_aggregate_ratings (
        predicate_id,
        aggregate_rating,
        number_of_ratings,
        weight_of_ratings,
        creator_id
    )
    SELECT
        pnr.predicate_id,
        cr.aggregate_rating,
        cr.number_of_ratings,
        cr.weight_of_ratings,
        cid AS creator_id
    FROM
        get_predicates_with_newer_ratings(cid) AS pnr
        JOIN (
            SELECT
                r.predicate_id,
                AVG(r.rating)::numeric AS aggregate_rating,
                COUNT(*)::int4 AS number_of_ratings,
                1::numeric AS weight_of_ratings
            FROM
                (
                    SELECT DISTINCT ON (rat.creator_id, rat.predicate_id)
                        rat.creator_id,
                        rat.predicate_id,
                        rat.rating
                    FROM ratings rat
                    ORDER BY rat.creator_id, rat.predicate_id, rat.creation_time DESC
                ) r
            GROUP BY
                predicate_id
            HAVING
                COUNT(*) > 0
        ) AS cr ON cr.predicate_id = pnr.predicate_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE public.rating_calculation_procedure_weighted_delegation(id int4)
AS $$
DECLARE
    cid int4;
    predicates_to_update int4;
    processed_count int4 := 0;
    rec RECORD;
BEGIN
    SELECT creator_id
    INTO STRICT cid
    FROM rating_calculation_procedures rcp
    WHERE rcp.id = rating_calculation_procedure_weighted_delegation.id
    LIMIT 1;

    IF cid IS NULL THEN
        RAISE EXCEPTION 'Creator ID not found for procedure ID %', rating_calculation_procedure_weighted_delegation.id;
    END IF;

    -- Debug: Check how many predicates need updating
    SELECT COUNT(*)
    INTO predicates_to_update
    FROM get_predicates_with_newer_ratings_or_delegations(cid);
    
    RAISE NOTICE 'Weighted delegation procedure starting for creator_id %. Found % predicates to update.', cid, predicates_to_update;

    -- Calculate weighted ratings for predicates based on tag delegations
    INSERT INTO cached_aggregate_ratings (
        predicate_id,
        aggregate_rating,
        number_of_ratings,
        weight_of_ratings,
        creator_id
    )
    SELECT
        pnr.predicate_id,
        COALESCE(final_calcs.weighted_average_rating, 0)::numeric AS aggregate_rating,
        COALESCE(final_calcs.number_of_voters, 0)::int4 AS number_of_ratings,
        COALESCE(final_calcs.total_weight, 0)::numeric AS weight_of_ratings,
        cid AS creator_id
    FROM
        get_predicates_with_newer_ratings_or_delegations(cid) AS pnr
        LEFT JOIN (
            -- Calculate final weighted rating per predicate
            SELECT 
                predicate_id,
                CASE 
                    WHEN SUM(final_user_weight) > 0 
                    THEN SUM(rating * final_user_weight) / SUM(final_user_weight)
                    ELSE 0 
                END AS weighted_average_rating,
                SUM(final_user_weight) AS total_weight,
                COUNT(*) AS number_of_voters            FROM (
                -- Get each user's final weight and rating for each predicate
                SELECT DISTINCT
                    r.predicate_id,
                    r.creator_id AS user_id,
                    r.rating,
                    -- Calculate user's weight - if predicate has no tags, default to 1
                    CASE 
                        WHEN NOT EXISTS (SELECT 1 FROM predicate_tag_links ptl_check WHERE ptl_check.predicate_id = r.predicate_id) 
                        THEN 1  -- No tags = default weight of 1 for everyone
                        ELSE GREATEST(0, 
                            CASE 
                                WHEN COUNT(delegation_summary.tag_id) = 0 THEN 1  -- No delegations = default weight of 1
                                ELSE AVG(
                                    1 + 
                                    COALESCE(delegation_summary.net_received, 0) - 
                                    COALESCE(delegation_summary.total_delegated, 0)
                                )
                            END
                        )
                    END AS final_user_weight
                FROM (
                    -- Get latest rating for each user-predicate combination
                    SELECT DISTINCT ON (r_inner.creator_id, r_inner.predicate_id)
                        r_inner.creator_id,
                        r_inner.predicate_id,
                        r_inner.rating
                    FROM ratings r_inner
                    ORDER BY r_inner.creator_id, r_inner.predicate_id, r_inner.creation_time DESC
                ) r
                LEFT JOIN predicate_tag_links ptl ON ptl.predicate_id = r.predicate_id
                LEFT JOIN (
                    -- Calculate net delegation effect per user per tag
                    SELECT 
                        COALESCE(ld_received.user_id, ld_given.user_id) AS user_id,
                        COALESCE(ld_received.tag_id, ld_given.tag_id) AS tag_id,
                        COALESCE(ld_received.received, 0) AS net_received,
                        COALESCE(ld_given.delegated, 0) AS total_delegated
                    FROM (
                        -- Weight received from others
                        SELECT 
                            ld.receiver_id AS user_id,
                            ld.tag_id,
                            SUM(ld.weight) AS received
                        FROM latest_delegations ld
                        WHERE ld.weight > 0
                        GROUP BY ld.receiver_id, ld.tag_id
                    ) ld_received
                    FULL OUTER JOIN (
                        -- Weight delegated to others
                        SELECT 
                            ld.creator_id AS user_id,
                            ld.tag_id,
                            SUM(ABS(ld.weight)) AS delegated
                        FROM latest_delegations ld
                        GROUP BY ld.creator_id, ld.tag_id
                    ) ld_given ON ld_received.user_id = ld_given.user_id AND ld_received.tag_id = ld_given.tag_id                ) delegation_summary ON delegation_summary.user_id = r.creator_id AND delegation_summary.tag_id = ptl.tag_id
                WHERE r.creator_id <> 0  -- Exclude system user
                GROUP BY r.predicate_id, r.creator_id, r.rating
                HAVING CASE 
                    WHEN NOT EXISTS (SELECT 1 FROM predicate_tag_links ptl_check WHERE ptl_check.predicate_id = r.predicate_id) 
                    THEN 1  -- No tags = default weight of 1 for everyone
                    WHEN COUNT(delegation_summary.tag_id) = 0 THEN 1  -- No delegations = default weight of 1
                    ELSE AVG(
                        1 + 
                        COALESCE(delegation_summary.net_received, 0) - 
                        COALESCE(delegation_summary.total_delegated, 0)
                    )
                END > 0  -- Only include users with positive final weight
            ) user_weights
            GROUP BY predicate_id
        ) final_calcs ON final_calcs.predicate_id = pnr.predicate_id;-- Debug: Check how many records were actually inserted
    GET DIAGNOSTICS processed_count = ROW_COUNT;
    RAISE NOTICE 'Weighted delegation procedure completed. Inserted % new cached aggregate ratings.', processed_count;
    
    -- Debug: Show individual voter weights and ratings for first predicate (if any)
    FOR rec IN 
        SELECT DISTINCT
            u.id AS user_id,
            u.username,
            t.id AS tag_id,
            t.name AS tag_name,
            pnr.predicate_id,
            GREATEST(0, 1 + 
                COALESCE(received.total_received, 0) - 
                COALESCE(delegated.total_delegated, 0)
            ) AS final_weight,
            latest_rating.rating
        FROM 
            get_predicates_with_newer_ratings_or_delegations(cid) pnr
            JOIN predicate_tag_links ptl ON ptl.predicate_id = pnr.predicate_id
            JOIN tags t ON t.id = ptl.tag_id
            CROSS JOIN users u
            LEFT JOIN (
                SELECT DISTINCT ON (r.creator_id, r.predicate_id)
                    r.creator_id,
                    r.predicate_id,
                    r.rating
                FROM ratings r
                ORDER BY r.creator_id, r.predicate_id, r.creation_time DESC
            ) latest_rating ON latest_rating.creator_id = u.id AND latest_rating.predicate_id = pnr.predicate_id
            LEFT JOIN (
                SELECT 
                    ld.receiver_id,
                    ld.tag_id,
                    SUM(ld.weight) AS total_received
                FROM latest_delegations ld
                WHERE ld.weight > 0
                GROUP BY ld.receiver_id, ld.tag_id
            ) received ON received.receiver_id = u.id AND received.tag_id = t.id
            LEFT JOIN (
                SELECT 
                    ld.creator_id,
                    ld.tag_id,
                    SUM(ABS(ld.weight)) AS total_delegated
                FROM latest_delegations ld
                GROUP BY ld.creator_id, ld.tag_id
            ) delegated ON delegated.creator_id = u.id AND delegated.tag_id = t.id
        WHERE 
            u.id <> 0
            AND latest_rating.rating IS NOT NULL
        ORDER BY pnr.predicate_id, t.id, u.id
        LIMIT 10  -- Show first 10 voter records
    LOOP
        RAISE NOTICE 'Voter Debug - Predicate: %, Tag: % (%), User: % (%), Weight: %, Rating: %', 
                     rec.predicate_id, rec.tag_name, rec.tag_id, rec.username, rec.user_id, 
                     rec.final_weight, rec.rating;
    END LOOP;
    
    -- Debug: Show sample of what was calculated (first 3 records)
    FOR rec IN 
        SELECT car.predicate_id, car.aggregate_rating, car.number_of_ratings, car.weight_of_ratings
        FROM cached_aggregate_ratings car
        WHERE car.creator_id = cid
        ORDER BY car.creation_time DESC
        LIMIT 3
    LOOP
        RAISE NOTICE 'Sample result - Predicate ID: %, Rating: %, Voters: %, Weight: %', 
                     rec.predicate_id, rec.aggregate_rating, rec.number_of_ratings, rec.weight_of_ratings;
    END LOOP;
END;
$$ LANGUAGE plpgsql;