-- 1. accept_or_reject_friendship
CREATE OR REPLACE PROCEDURE public.accept_or_reject_friendship(IN p_responder_id bigint, IN p_requester_id bigint, IN p_action text)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_low        BIGINT;
    v_high       BIGINT;
    v_status_id  BIGINT;
    v_status_name VARCHAR(50);
    v_req_by     BIGINT;
    v_action_status_id BIGINT;
    v_type_id    BIGINT;
BEGIN
    v_low  := LEAST(p_responder_id, p_requester_id);
    v_high := GREATEST(p_responder_id, p_requester_id);

    SELECT f.status_id, fs.name, f.requested_by_user_id
    INTO   v_status_id, v_status_name, v_req_by
    FROM   friendships f
    JOIN   friendship_statuses fs ON fs.id = f.status_id
    WHERE  f.user_id_low = v_low
    AND    f.user_id_high = v_high;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'There is no friend request between these users.';
    END IF;

    IF v_req_by = p_responder_id THEN
        RAISE EXCEPTION 'Can not respond to your own request.';
    END IF;

    IF p_action IN ('accept', 'reject') AND v_status_name <> 'pending' THEN
        RAISE EXCEPTION 'Only pending friend requests can be accepted or rejected.';
    END IF;

    IF p_action = 'accept' THEN
        SELECT id INTO v_action_status_id FROM friendship_statuses WHERE name = 'accepted';
        SELECT id INTO v_type_id FROM notification_types WHERE name = 'friend_request';

        UPDATE friendships
        SET status_id = v_action_status_id,
            updated_at = CURRENT_TIMESTAMP
        WHERE user_id_low = v_low
        AND user_id_high = v_high;

        INSERT INTO notifications (id, user_id, type_id, title, message)
        VALUES (
            coalesce((select max(id) from notifications), 0) + 1,
            p_requester_id,
            v_type_id,
            'Friend request accepted!',
            'User ' || p_responder_id || ' accepted your friend request.'
        );
        RAISE NOTICE 'Friend request accepted.';

    ELSIF p_action = 'reject' THEN
        DELETE FROM friendships
        WHERE user_id_low = v_low
        AND user_id_high = v_high;
        RAISE NOTICE 'Friend request rejected.';

    ELSIF p_action = 'block' THEN
        SELECT id INTO v_action_status_id FROM friendship_statuses WHERE name = 'blocked';

        UPDATE friendships
        SET status_id = v_action_status_id,
            updated_at = CURRENT_TIMESTAMP
        WHERE user_id_low = v_low
        AND user_id_high = v_high;
        RAISE NOTICE 'The user is blocked.';

    ELSE
        RAISE EXCEPTION 'Invalid action: %', p_action;
    END IF;
END;
$procedure$
;

-- 2. add_match_participants
CREATE OR REPLACE PROCEDURE public.add_match_participants(IN p_match_id bigint, IN p_user_ids bigint[], IN p_team_ids bigint[])
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_ongoing_id BIGINT;
    v_started_at TIMESTAMP;
BEGIN
    SELECT id INTO v_ongoing_id FROM match_statuses WHERE name = 'in_progress';

    SELECT started_at INTO v_started_at
    FROM matches
    WHERE id = p_match_id AND status_id = v_ongoing_id;

    IF v_started_at IS NULL THEN
        RAISE EXCEPTION 'Match % is not active, does not exist, or status mismatch.', p_match_id;
    END IF;

    IF cardinality(p_user_ids) != cardinality(p_team_ids) THEN
        RAISE EXCEPTION 'The number of user IDs must match the number of team IDs.';
    END IF;

    INSERT INTO match_teams (match_id, match_started_at, team_id, side_label)
    SELECT DISTINCT p_match_id, v_started_at, t_id, 'Team_' || t_id
    FROM unnest(p_team_ids) AS t_id
    ON CONFLICT DO NOTHING;

    INSERT INTO match_participants (id, match_id, match_started_at, user_id, team_id)
    SELECT
        coalesce((select max(id) from match_participants), 0) + row_number() OVER (),
        p_match_id,
        v_started_at,
        arr.u_id,
        arr.t_id
    FROM unnest(p_user_ids, p_team_ids) AS arr(u_id, t_id);

    RAISE NOTICE 'Participants successfully added to match %.', p_match_id;
END;
$procedure$
;

-- 3. auto_create_profile
CREATE OR REPLACE FUNCTION public.auto_create_profile_fn()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    INSERT INTO profiles (user_id, avatar_url, bio, rank_points)
    VALUES (NEW.id, NULL, NULL, 1000);
    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE TRIGGER auto_create_profile
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION auto_create_profile_fn();

-- 4. create_match
CREATE OR REPLACE PROCEDURE public.create_match(
    IN p_match_id bigint,
    IN p_game_id bigint,
    IN p_game_mode_id bigint,
    IN p_tournament_id bigint,
    IN p_server_id bigint,
    IN p_participants jsonb,
    OUT p_started_at timestamp
)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_status_id BIGINT;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM servers WHERE id = p_server_id AND is_active = TRUE) THEN
        RAISE EXCEPTION 'Server % is not active or does not exist.', p_server_id;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM tournaments WHERE id = p_tournament_id AND game_id = p_game_id) THEN
        RAISE EXCEPTION 'Tournament % is not connected to game %.', p_tournament_id, p_game_id;
    END IF;

    SELECT id INTO v_status_id FROM match_statuses WHERE name = 'in_progress';
    p_started_at := CURRENT_TIMESTAMP;

    INSERT INTO matches (id, game_id, game_mode_id, tournament_id, server_id, status_id, started_at)
    VALUES (p_match_id, p_game_id, p_game_mode_id, p_tournament_id, p_server_id, v_status_id, p_started_at);

    RAISE NOTICE 'The match % is created in partition window %.', p_match_id, p_started_at;
END;
$procedure$
;

-- 5. notify_on_achievement
CREATE OR REPLACE FUNCTION public.notify_on_achievement_fn()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    a_title       VARCHAR(100);
    a_game_title  VARCHAR(100);
    v_type_id     BIGINT;
BEGIN
    SELECT a.title, g.title
    INTO   a_title, a_game_title
    FROM   achievements a
    JOIN   games g ON g.id = a.game_id
    WHERE  a.id = NEW.achievement_id;

    SELECT id INTO v_type_id FROM notification_types WHERE name = 'achievement';

    INSERT INTO notifications (id, user_id, type_id, title, message, is_read)
    VALUES (
        coalesce((select max(id) from notifications), 0) + 1,
        NEW.user_id,
        v_type_id,
        'New Achievement!',
        'You got the achievement "' || a_title || '" in the game ' || a_game_title || '.',
        FALSE
    );
    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE TRIGGER notify_on_achievement
AFTER INSERT ON user_achievements
FOR EACH ROW
EXECUTE FUNCTION notify_on_achievement_fn();

-- 6. register_match_result
CREATE OR REPLACE FUNCTION public.register_match_result(r_match_id bigint, r_participant_ids bigint[], r_results text[], r_scores integer[], r_elo_changes integer[])
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
    r_status_name text;
    v_started_at  TIMESTAMP;
    v_comp_id     BIGINT;
BEGIN
    IF cardinality(r_participant_ids) != cardinality(r_results) OR
       cardinality(r_participant_ids) != cardinality(r_scores) OR
       cardinality(r_participant_ids) != cardinality(r_elo_changes) THEN
        RETURN 'Error: Input arrays must have matching dimensions.';
    END IF;

    SELECT ms.name, m.started_at INTO r_status_name, v_started_at
    FROM matches m
    JOIN match_statuses ms ON ms.id = m.status_id
    WHERE m.id = r_match_id;

    IF v_started_at IS NULL THEN
        RETURN 'Error: Match does not exist.';
    END IF;

    IF r_status_name NOT IN ('in_progress', 'scheduled') THEN
        RETURN 'Error: The match is over or canceled.';
    END IF;

    UPDATE match_participants AS mp
    SET result_id  = (SELECT id FROM participant_results WHERE name = arr.res),
        score      = arr.scr,
        elo_change = arr.elo
    FROM unnest(r_participant_ids, r_results, r_scores, r_elo_changes) AS arr(part_id, res, scr, elo)
    WHERE mp.id = arr.part_id
      AND mp.match_id = r_match_id;

    SELECT id INTO v_comp_id FROM match_statuses WHERE name = 'completed';

    UPDATE matches
    SET status_id   = v_comp_id,
        ended_at = CURRENT_TIMESTAMP
    WHERE id = r_match_id
      AND started_at = v_started_at;

    RETURN 'OK: Match is over.';
END;
$function$
;

-- 7. register_user
CREATE OR REPLACE PROCEDURE public.register_user(IN p_id bigint, IN p_username character varying, IN p_email character varying, IN p_password_hash text, IN p_location_id bigint DEFAULT 0)
 LANGUAGE plpgsql
AS $procedure$
BEGIN
    IF char_length(p_username) < 3 OR char_length(p_username) > 30 THEN
        RAISE EXCEPTION 'Username must have between 3 and 30 characters.';
    END IF;

    IF EXISTS (SELECT 1 FROM users WHERE username = p_username) THEN
        RAISE EXCEPTION 'This username is taken.';
    END IF;

    INSERT INTO users (id, username, email, password_hash, location_id)
    VALUES (p_id, p_username, p_email, p_password_hash, p_location_id);
    RAISE NOTICE 'User % is successfully registered.', p_username;
END;
$procedure$
;

-- 8. send_friend_request
CREATE OR REPLACE FUNCTION public.send_friend_request(requester_id bigint, target_id bigint)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
    low         BIGINT;
    high        BIGINT;
    v_status_id BIGINT;
BEGIN
    IF requester_id = target_id THEN
        RETURN 'Error: Can not send request to self.';
    END IF;

    low  := LEAST(requester_id, target_id);
    high := GREATEST(requester_id, target_id);

    IF EXISTS (
        SELECT 1 FROM friendships
        WHERE user_id_low = low AND user_id_high = high
    ) THEN
        IF EXISTS (
            SELECT 1 FROM friendships f
            JOIN friendship_statuses fs ON fs.id = f.status_id
            WHERE f.user_id_low = low AND f.user_id_high = high AND fs.name = 'blocked'
        ) THEN
            RETURN 'Error: The user is blocked.';
        END IF;

        RETURN 'Error: The request exists or the users are already friends.';
    END IF;

    SELECT id INTO v_status_id FROM friendship_statuses WHERE name = 'pending';

    INSERT INTO friendships (id, user_id_low, user_id_high, requested_by_user_id, status_id)
    VALUES (coalesce((select max(id) from friendships), 0) + 1, low, high, requester_id, v_status_id);

    RETURN 'OK: The friend request is sent.';
END;
$function$
;

-- 9. subscribe_user
CREATE OR REPLACE PROCEDURE public.subscribe_user(IN p_subscription_id bigint, IN p_user_id bigint, IN p_plan_name text, IN p_payment_id bigint, IN p_amount numeric, IN p_currency character DEFAULT 'EUR'::bpchar)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    subs_end_at TIMESTAMP;
    v_plan_id   BIGINT;
BEGIN
    subs_end_at := CASE p_plan_name
                    WHEN 'weekly'  THEN CURRENT_TIMESTAMP + INTERVAL '7 days'
                    WHEN 'monthly' THEN CURRENT_TIMESTAMP + INTERVAL '30 days'
                    ELSE CURRENT_TIMESTAMP + INTERVAL '30 days'
                END;

    SELECT id INTO v_plan_id FROM subscription_plans WHERE name = p_plan_name;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Subscription plan % does not exist.', p_plan_name;
    END IF;

    UPDATE subscriptions
    SET is_active = FALSE,
        end_at    = CURRENT_TIMESTAMP
    WHERE user_id = p_user_id
      AND is_active = TRUE;

    INSERT INTO subscriptions (id, user_id, plan_id, start_at, end_at, is_active)
    VALUES (p_subscription_id, p_user_id, v_plan_id, CURRENT_TIMESTAMP, subs_end_at, TRUE);

    INSERT INTO payments (id, user_id, subscription_id, amount, currency_code, paid_at)
    VALUES (p_payment_id, p_user_id, p_subscription_id, p_amount, p_currency, CURRENT_TIMESTAMP);

    RAISE NOTICE 'Subscription % for user % is activated till %', p_plan_name, p_user_id, subs_end_at;
END;
$procedure$
;

-- 10. unsubscribe_user
CREATE OR REPLACE PROCEDURE public.unsubscribe_user(IN p_user_id bigint)
 LANGUAGE plpgsql
AS $procedure$
BEGIN
    UPDATE subscriptions
    SET is_active = FALSE,
        end_at    = CURRENT_TIMESTAMP
    WHERE user_id = p_user_id
      AND is_active = TRUE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User % does not have an active subscription.', p_user_id;
    ELSE
        RAISE NOTICE 'Subscription for user % has been successfully canceled.', p_user_id;
    END IF;
END;
$procedure$
;

-- 11. update_rank
CREATE OR REPLACE FUNCTION public.update_rank()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF OLD.result_id IS NULL AND NEW.result_id IS NOT NULL THEN
        UPDATE profiles
        SET rank_points = GREATEST(0, rank_points + NEW.elo_change)
        WHERE user_id = NEW.user_id;
    END IF;
    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE TRIGGER update_rank_after_match
AFTER UPDATE OF result_id ON match_participants
FOR EACH ROW
EXECUTE FUNCTION public.update_rank();