Оптимизиран SQL-код, кој работи поедноставно и побрзо

create table account(
    id serial primary key,
    username varchar(255) not null,
    email varchar(255) not null,
    password varchar(255) not null,
    balance decimal(15, 2) not null 
);

create table "transaction"(
    id serial primary key,
    amount decimal(15, 2) not null,
    currency varchar(255) not null,
    type varchar(50) not null,
    timestamp timestamp default current_timestamp,
    account_id int not null,
    foreign key (account_id) references account(id)
);

create table transfer(
    id serial primary key,
    s_id int not null,
    r_id int not null, 
    foreign key(s_id) references account(id),
    foreign key(r_id) references
    account(id)

);

create table deposit(
    id serial primary key,
    account_id int not null,
    amount decimal(15,2) not null,
    timestamp timestamp default current_timestamp,
    foreign key(account_id) references account(id)
);

create table withdraw(
    id serial primary key,
    account_id int not null,
    amount decimal(15,2) not null,
    timestamp timestamp default current_timestamp,
    foreign key(account_id) references account(id)
);

create table transferred_money(
    id serial primary key, 
    s_account_id int not null, 
    r_account_id int not null, 
    t_id int not null, 
    currency varchar(50) not null, 
    foreign key (s_account_id) references account(id),
    foreign key (r_account_id) references account(id),
    foreign key (t_id) references transaction(id)
);

create or replace function after_transaction_trigger()
returns trigger as $$
begin

	if new.type = 'Deposit' then
		insert into Deposit(account_id,amount, timestamp)
		values (new.account_id, new.amount, new.timestamp);
	
	elsif new.type = 'Withdrawal' then
		insert into Withdraw(account_id,amount, timestamp)
		values (new.account_id, new.amount, new.timestamp);
	
	elsif new.type like '(MKD) Transfer Out to%' then
		insert into transferred_money(s_account_id, r_account_id, t_id, currency)
		select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
			new.id, new.currency; 

	elsif new.type like '(USD) Transfer Out to%' then
		insert into transferred_money(s_account_id, r_account_id, t_id, currency)
		select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
			new.id, new.currency;
	
	elsif new.type like '(EUR) Transfer Out to%' then
		insert into transferred_money(s_account_id, r_account_id, t_id, currency)
		select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
			new.id, new.currency;
			
	elsif new.type like '(GBP) Transfer Out to%' then
		insert into transferred_money(s_account_id, r_account_id, t_id, currency)
		select new.account_id, (select id from account where username = trim(substring(new.type from position('Transfer Out to ' in new.type)+15)) limit 1),
			new.id, new.currency;

	end if;
	return new;
end;
$$ language plpgsql;

create trigger after_transaction
after insert on transaction
for each row
execute function after_transaction_trigger();


CREATE OR REPLACE FUNCTION after_transaction_trigger2()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.type = 'Deposit' THEN
        -- Log deposit as a self-transfer
        INSERT INTO transfer(s_id, r_id)
        VALUES (NEW.account_id, NEW.account_id);

    ELSIF NEW.type = 'Withdrawal' THEN
        -- Log withdrawal as a self-transfer
        INSERT INTO transfer(s_id, r_id)
        VALUES (NEW.account_id, NEW.account_id);

    ELSIF NEW.type LIKE '(MKD) Transfer Out to%' OR 
          NEW.type LIKE '(USD) Transfer Out to%' OR 
          NEW.type LIKE '(EUR) Transfer Out to%' OR 
          NEW.type LIKE '(GBP) Transfer Out to%' THEN
          
        -- Extract recipient username and insert into transfer table
        INSERT INTO transfer(s_id, r_id)
        SELECT NEW.account_id, 
               (SELECT id FROM account 
                WHERE username = TRIM(SUBSTRING(NEW.type FROM POSITION('Transfer Out to ' IN NEW.type) + 15)) 
                LIMIT 1);

    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_transaction2
AFTER INSERT ON transaction
FOR EACH ROW
EXECUTE FUNCTION after_transaction_trigger2();



select * from account;
select * from "transaction";
select * from transfer;
select * from transferred_money;
select * from deposit;
select * from withdraw;

SELECT 
    tm.s_account_id AS sender_id,
	a_s.username AS sender_username,
	tm.r_account_id AS receiver_id,
	a_r.username AS receiver_username,
    tm.t_id,
	t.timestamp,
	t.amount
    
FROM 
    transferred_money tm
JOIN 
    account a_s ON tm.s_account_id = a_s.id
JOIN 
    account a_r ON tm.r_account_id = a_r.id
JOIN
    "transaction" t ON tm.t_id = t.id;


SELECT 
    tm.s_account_id AS sender_id,
	a_s.username AS sender_username,
	tm.r_account_id AS receiver_id,
	a_r.username AS receiver_username,
    tm.t_id,
	t.timestamp,
	t.amount
    
FROM 
    transferred_money tm
JOIN 
    account a_s ON tm.s_account_id = a_s.id
JOIN 
    account a_r ON tm.r_account_id = a_r.id
JOIN
    "transaction" t ON tm.t_id = t.id
WHERE
      timestamp<'2025-03-23 20:30:00.000000';

SELECT 
    tm.s_account_id AS sender_id,
	a_s.username AS sender_username,
	tm.r_account_id AS receiver_id,
	a_r.username AS receiver_username,
    tm.t_id,
	t.timestamp,
	t.amount,
	tm.currency
    
FROM 
    transferred_money tm
JOIN 
    account a_s ON tm.s_account_id = a_s.id
JOIN 
    account a_r ON tm.r_account_id = a_r.id
JOIN
    "transaction" t ON tm.t_id = t.id
WHERE tm.currency='(EUR)'
ORDER BY timestamp;

SELECT 
	max(t.amount) as maximum,
	avg(t.amount) as "average",
	min(t.amount) as minimum
    
FROM 
    transferred_money tm
JOIN 
    account a_s ON tm.s_account_id = a_s.id
JOIN 
    account a_r ON tm.r_account_id = a_r.id
JOIN
    "transaction" t ON tm.t_id = t.id
WHERE tm.currency='(USD)';