---1=====================================================ANONYMOUS BLOCKS ========
DO $$ 
<<first_block>>
DECLARE
  counter integer := 0;
BEGIN 
   counter := counter + 1;
   RAISE NOTICE 'The current value of counter is %', counter;
END first_block $$;
---2=====================================================================

DO $$ 
<<outer_block>>
DECLARE
  counter integer := 0;
BEGIN 
   counter := counter + 1;
   RAISE NOTICE 'The current value of counter is %', counter;
 
   DECLARE 
       counter integer := 0;
   BEGIN 
       counter := counter + 10;
       RAISE NOTICE 'The current value of counter in the subblock is %', counter;
       RAISE NOTICE 'The current value of counter in the outer block is %', outer_block.counter;
   END;
 
   RAISE NOTICE 'The current value of counter in the outer block is %', counter;
   
END outer_block $$;

---3==================================================================VARIABLES =========
DO $$ 
DECLARE
   counter integer := 1;
   first_name varchar(50) := 'John';
   last_name varchar(50) := 'Doe';
   payment numeric(11,2) := 20.5;
BEGIN 
   RAISE NOTICE '% % % has been paid % USD', counter, first_name, last_name, payment;
END $$;

---4=================================================================== CONSTANTS ==========

DO $$ 
DECLARE
   VAT CONSTANT numeric := 0.1;
   net_price numeric := 20.5;
BEGIN 
   RAISE NOTICE 'The selling price is %', net_price * ( 1 + VAT );
END $$;

---5===================================================================== MESSAGES ==========

DO $$ 
BEGIN 
  RAISE INFO 'information message %', now() ;
  RAISE LOG 'log message %', now();
  RAISE DEBUG 'debug message %', now();
  RAISE WARNING 'warning message %', now();
  RAISE NOTICE 'notice message %', now();
END $$;

---6======================================================================EXCEPTION =========
DO $$ 
DECLARE
 email varchar(255) := 'info@test.com';
BEGIN 
  -- check email for duplicate
  -- ...
  -- report duplicate email
  RAISE EXCEPTION 'Duplicate email: %', email 
 USING HINT = 'Check the email again';
END $$;
---7=========================================================================================
DO $$
DECLARE
a numeric := 1;
b numeric := 2;
BEGIN
 RAISE NOTICE '%',a + b;
END; $$

FUNCTIONS

CREATE OR REPLACE FUNCTION get_sum(a NUMERIC, b NUMERIC) 
RETURNS NUMERIC AS $$
BEGIN
 RETURN a + b;
END; $$
 
LANGUAGE plpgsql;

---9=========================================================================================
CREATE OR REPLACE FUNCTION square( INOUT a NUMERIC)
AS $$
BEGIN
 a := a * a;
END; $$
LANGUAGE plpgsql;

---10=========================================================================================

CREATE FUNCTION member_connections(p_email text)
RETURNS TABLE(
    email text,
    firstName text,
    secondName text
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT m.email, m.firstName, m.secondName
    FROM connects c
    JOIN member m ON c."connectedWithEmail" = m.email
    WHERE c.email1 = p_email;
END;
$$;

---11=========================================================================================

CREATE FUNCTION search_members(p_column text, p_value text)
RETURNS SETOF member
LANGUAGE plpgsql
AS $$
DECLARE
    sql_query text;
BEGIN
    sql_query :=
        'SELECT * FROM member WHERE ' ||
        quote_ident(p_column) ||
        ' = $1';

    RETURN QUERY EXECUTE sql_query
    USING p_value;
END;
$$;

SELECT *
FROM search_members('country','Greece');

---12=======================================================================================

CREATE OR REPLACE FUNCTION check_invitations(p_days integer)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
    r record;
    v_count integer := 0;
BEGIN

    FOR r IN
        SELECT "invitationID", "dateSent", "theStatus"
        FROM invitation
    LOOP

        -- αγνόησε όσα έχουν ήδη γίνει accepted
        IF r."theStatus" = 'ACCEPTED' THEN
            CONTINUE;

        ELSIF r."theStatus" = 'PENDING'
              AND r."dateSent" < CURRENT_DATE - p_days * INTERVAL '1 days' THEN

            UPDATE invitation
            SET "theStatus" = 'REJECTED'
            WHERE "invitationID" = r."invitationID";

            v_count := v_count + 1;

            RAISE NOTICE 'Invitation % rejected (too old)', r."invitationID";

        ELSE
            RAISE NOTICE 'Invitation % unchanged', r."invitationID";
        END IF;

    END LOOP;

    RETURN v_count;

END;
$$;

---14===========================================================================

CREATE OR REPLACE FUNCTION accept_invitation(p_id integer)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
    v_status text;
BEGIN

    SELECT "theStatus" INTO v_status
    FROM invitation
    WHERE "invitationID" = p_id;

    IF v_status IS NULL THEN
        RAISE EXCEPTION 'Invitation % does not exist', p_id;
    END IF;

    IF v_status = 'ACCEPTED' THEN
        RETURN 'Invitation already accepted';
    END IF;

    UPDATE invitation
    SET "theStatus" = 'ACCEPTED'
    WHERE "invitationID" = p_id;

    RETURN 'Invitation accepted';

EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Error occurred: %', SQLERRM;
        RETURN 'Operation failed';
END;
$$;


---15================================================================

CREATE OR REPLACE FUNCTION create_invitation(p_sender varchar, p_receiver varchar)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
    v_id integer;
BEGIN

    SELECT COALESCE(MAX("invitationID"),0) + 1
    INTO v_id
    FROM invitation;

    INSERT INTO invitation VALUES(v_id, CURRENT_DATE,'PENDING', p_sender, p_receiver);

    RETURN 'Invitation created';

EXCEPTION
    WHEN foreign_key_violation THEN
        RAISE INFO 'Sender or receiver does not exist in member';
		RETURN 'Invitation NOT created';
END;
$$;