source: src/main/resources/static/SQLscript.txt

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,
    transaction_id int not null,
    amount decimal(15,2) not null,
    foreign key(account_id) references account(id),
    foreign key(transaction_id) references transaction(id)
);

create table withdraw(
    id serial primary key,
    account_id int not null,
    transaction_id int not null,
    amount decimal(15,2) not null,
    foreign key(account_id) references account(id),
    foreign key(transaction_id) references transaction(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, transaction_id, amount)
        VALUES (NEW.account_id, NEW.id, NEW.amount);

    ELSIF NEW.type = 'Withdrawal' THEN
        INSERT INTO Withdraw(account_id, transaction_id, amount)
        VALUES (NEW.account_id, NEW.id, NEW.amount);

    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
        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
    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-04-01 22:22: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)';


WITH recent_activity AS (
    SELECT account_id, COUNT(*) AS activity_count
    FROM "transaction"
    WHERE timestamp >= NOW() - INTERVAL '30 days'
    GROUP BY account_id
)

SELECT a.id AS account_id,
       a.username,
       a.email,
       COALESCE(ra.activity_count, 0) AS recent_transactions
FROM account a
LEFT JOIN recent_activity ra ON a.id = ra.account_id
ORDER BY recent_transactions DESC
LIMIT 1;


WITH deposit_totals AS (
    SELECT account_id, SUM(amount) AS total_deposit
    FROM deposit
    GROUP BY account_id
)

SELECT a.id AS account_id,
       a.username,
       a.email,
       COALESCE(dt.total_deposit, 0) AS total_deposit,
       RANK() OVER (ORDER BY COALESCE(dt.total_deposit, 0) DESC) AS deposit_rank
FROM account a
LEFT JOIN deposit_totals dt ON a.id = dt.account_id
ORDER BY deposit_rank
LIMIT 3;

CREATE INDEX idx_deposit_account_id_amount ON deposit (account_id, amount);


WITH transfer_amount AS (
    SELECT t.s_id AS account_id, SUM(tr.amount) AS total_transfer
    FROM transfer t
    JOIN "transaction" tr ON t.id = tr.id
    GROUP BY t.s_id

    UNION ALL

    SELECT t.r_id AS account_id, SUM(tr.amount) AS total_transfer
    FROM transfer t
    JOIN "transaction" tr ON t.id = tr.id
    GROUP BY t.r_id
),

aggregated_transfers AS (
    SELECT account_id, SUM(total_transfer) AS total_transfer_amount
    FROM transfer_amount
    GROUP BY account_id
)

SELECT a.id AS account_id,
       a.username,
       a.email,
       COALESCE(at.total_transfer_amount, 0) AS total_transfer_amount,
       RANK() OVER (ORDER BY COALESCE(at.total_transfer_amount, 0) DESC) AS transfer_rank
FROM account a
LEFT JOIN aggregated_transfers at ON a.id = at.account_id
ORDER BY transfer_rank
LIMIT 2;


WITH transfer_frequency AS (
    SELECT t.s_id AS account_id, COUNT(*) AS transfer_count
    FROM transfer t
    GROUP BY t.s_id

    UNION ALL

    SELECT t.r_id AS account_id, COUNT(*) AS transfer_count
    FROM transfer t
    GROUP BY t.r_id
)

SELECT *
FROM (
    SELECT a.id AS account_id,
           a.username,
           a.email,
           tf.transfer_count,
           RANK() OVER (ORDER BY tf.transfer_count DESC) AS transfer_rank
    FROM account a
    JOIN transfer_frequency tf ON a.id = tf.account_id
) ranked_accounts
WHERE transfer_rank <= 2
ORDER BY transfer_rank;




CREATE INDEX idx_transfer_s_id ON transfer(s_id);


CREATE INDEX idx_transfer_r_id ON transfer(r_id);

CREATE INDEX idx_account_id ON account(id);




-- create index client on account(username, email, balance);


-- SELECT indexname, tablename
-- FROM pg_indexes
-- WHERE tablename = 'account';

-- select * from account;
-- select * from "transaction";

create index form on "transaction"(currency,"type",timestamp);

SET enable_seqscan = off;