Views.txt

CREATE OR REPLACE VIEW customer_owned_accounts AS
SELECT 
    c.customer_id,
    c.name AS customer_name,
    sa.saving_account_id,
    sa.balance,
    sa.status AS account_status,
    sp.plan_name,
    sa.open_date
FROM Customer c
JOIN AccountHolder ah ON c.customer_id = ah.customer_id
JOIN SavingsAccount sa ON ah.saving_account_id = sa.saving_account_id
JOIN SavingsAccount_Plans sp ON sa.s_plan_id = sp.s_plan_id;

--  Account transactions
CREATE OR REPLACE VIEW vw_account_transactions AS
SELECT 
    sa.saving_account_id,
    t.transaction_id,
    t.type,
    t.amount,
    t.timestamp,
    t.ref_number,
    t.description
FROM Transactions t
JOIN AccountHolder ah ON t.holder_id = ah.holder_id
JOIN SavingsAccount sa ON ah.saving_account_id = sa.saving_account_id;

--  Active Fixed Deposits
CREATE OR REPLACE VIEW vw_active_fds AS
SELECT 
    fd.fixed_deposit_id,
    fd.saving_account_id,
    fd.f_plan_id,
    fd.start_date,
    fd.end_date,
    fd.principal_amount,
    fd.interest_payment_type,
    fd.last_payout_date,
    fd.status
FROM FixedDeposit fd
WHERE fd.status = TRUE;

--  Joint Accounts & Holders
CREATE MATERIALIZED VIEW joint_accounts_holders_mv AS
SELECT 
    sa.saving_account_id,
    sa.balance,
    sa.status AS account_status,
    string_agg(c.name, ', ') AS joint_customers
FROM SavingsAccount sa
JOIN AccountHolder ah ON sa.saving_account_id = ah.saving_account_id
JOIN Customer c ON ah.customer_id = c.customer_id
GROUP BY sa.saving_account_id, sa.balance, sa.status
HAVING COUNT(ah.customer_id) > 1
WITH DATA;



-- Agent Transaction Summary
CREATE MATERIALIZED VIEW vw_agent_transactions_mv AS
SELECT 
    e.employee_id,
    e.name AS agent_name,
    COUNT(t.transaction_id) AS total_transactions,
    SUM(t.amount) AS total_value
FROM Employee e
JOIN Customer c ON e.employee_id = c.employee_id
JOIN AccountHolder ah ON c.customer_id = ah.customer_id
JOIN Transactions t ON ah.holder_id = t.holder_id
GROUP BY e.employee_id, e.name
WITH DATA;



-- Monthly Interest Summary
CREATE MATERIALIZED VIEW vw_monthly_interest_summary_mv AS
SELECT 
    sp.plan_name,
    sa.saving_account_id,
    DATE_TRUNC('month', t.timestamp) AS month,
    SUM(t.amount) AS monthly_interest
FROM Transactions t
JOIN AccountHolder ah ON t.holder_id = ah.holder_id
JOIN SavingsAccount sa ON ah.saving_account_id = sa.saving_account_id
JOIN SavingsAccount_Plans sp ON sa.s_plan_id = sp.s_plan_id
WHERE t.type = 'Interest'
GROUP BY sp.plan_name, sa.saving_account_id, DATE_TRUNC('month', t.timestamp)
WITH DATA;




-- Customer Activity Summary
CREATE MATERIALIZED VIEW vw_customer_activity_mv AS
SELECT 
    c.customer_id,
    c.name AS customer_name,
    SUM(CASE WHEN t.type = 'Deposit' THEN t.amount ELSE 0 END) AS total_deposits,
    SUM(CASE WHEN t.type = 'Withdrawal' THEN t.amount ELSE 0 END) AS total_withdrawals,
    SUM(CASE WHEN t.type = 'Deposit' THEN t.amount
             WHEN t.type = 'Withdrawal' THEN -t.amount
             ELSE 0 END) AS net_change
FROM Customer c
JOIN AccountHolder ah ON c.customer_id = ah.customer_id
JOIN Transactions t ON ah.holder_id = t.holder_id
GROUP BY c.customer_id, c.name;

--search saving accounts get details
CREATE OR REPLACE VIEW savings_account_with_customer AS
SELECT
    sa.saving_account_id,
    sa.open_date,
    sa.balance,
    sa.employee_id,
    sa.s_plan_id,
    sa.status,
    sa.branch_id,
    c.customer_id,
    c.name AS customer_name,
    c.nic AS customer_nic
FROM SavingsAccount sa
JOIN AccountHolder ah ON sa.saving_account_id = ah.saving_account_id
JOIN Customer c ON ah.customer_id = c.customer_id;

CREATE INDEX idx_customer_activity_mv_id ON vw_customer_activity_mv(customer_id);

CREATE OR REPLACE VIEW holder_balance_min AS
SELECT
    ah.holder_id,
    sa.saving_account_id,
    sa.balance,
    sp.min_balance
FROM AccountHolder ah
JOIN SavingsAccount sa ON ah.saving_account_id = sa.saving_account_id
JOIN SavingsAccount_Plans sp ON sa.s_plan_id = sp.s_plan_id;