DDL, податоци, погледи : views.sql

--Најава на корисник (Login view)
CREATE VIEW v_user_login AS
SELECT
    cu.client_user_id,
    cu.username,
    cu.password_hash,
    cu.status,
    c.client_id,
    c.first_name,
    c.last_name,
    c.email
FROM Client_user cu
JOIN Client c ON cu.client_id = c.client_id
WHERE cu.status = 'ACTIVE';

SELECT * FROM v_user_login;


--Регистрација на корисник (Registration view)
CREATE VIEW v_user_registration AS
SELECT
    c.client_id,
    c.first_name,
    c.last_name,
    c.embg,
    c.email,
    c.phone,
    cu.username,
    cu.status AS user_status,
    c.date_registered
FROM Client c
LEFT JOIN Client_user cu ON c.client_id = cu.client_id
WHERE (cu.client_id IS NULL OR cu.status <> 'ACTIVE')
  AND c.status <> 'DELETED';

SELECT * FROM v_user_registration;



--Извештаи (Receipt view)
CREATE VIEW vw_client_receipts AS
SELECT
    r.receipt_id,
    r.receipt_number,
    r.print_time,
    t.transaction_id,
    t.amount,
    t.transaction_date
FROM Receipt r
JOIN Transaction t ON t.transaction_id = r.transaction_id
WHERE t.status = 'COMPLETED'
  AND t.amount > 0
  AND r.print_time IS NOT NULL;

SELECT *
FROM vw_client_receipts;


--Рати (Loan installments view)
CREATE VIEW v_loan_installments AS
SELECT
    li.installment_id,
    li.installment_number,
    li.due_date,
    li.amount,
    li.status,
    li.paid_date,
    l.loan_id,
    l.client_id
FROM Loan_installment li
JOIN Loan l ON li.loan_id = l.loan_id
WHERE l.status IN ('APPROVED', 'CLOSED')
  AND li.amount > 0
  AND li.due_date IS NOT NULL;

SELECT *
FROM v_loan_installments;


--Преглед на кредити
CREATE VIEW v_loans AS
SELECT
    l.loan_id,
    l.loan_type,
    l.amount,
    l.interest_rate,
    l.start_date,
    l.end_date,
    l.status,
    l.approved_date,
    c.client_id,
    c.first_name,
    c.last_name
FROM Loan l
JOIN Client c ON l.client_id = c.client_id
WHERE l.status IN ('APPROVED', 'CLOSED')
  AND l.amount > 0
  AND l.interest_rate >= 0
  AND (l.status <> 'APPROVED' OR l.approved_date IS NOT NULL);


SELECT *
FROM v_loans;

--Трансакции
CREATE VIEW v_transactions AS
SELECT
    t.transaction_id,
    t.amount,
    t.transaction_date,
    t.status,
    t.description,
    t.reference_number,
    t.account_id,
    at.account_number,
    tt.type_name AS transaction_type,
    pm.method_name AS payment_method
FROM Transaction t
JOIN Account at ON t.account_id = at.account_id
LEFT JOIN Transaction_type tt ON t.transaction_type_id = tt.transaction_type_id
LEFT JOIN Payment_method pm ON t.payment_method_id = pm.method_id
WHERE t.status = 'COMPLETED'
  AND t.amount > 0
  AND t.transaction_date IS NOT NULL
  AND at.status = 'ACTIVE';

SELECT *
FROM v_transactions;

--Сомнителни трансакции
CREATE VIEW v_suspicious_transactions AS
SELECT *
FROM Transaction
WHERE amount > 10000
   OR status IN ('FAILED', 'PENDING');

SELECT *
FROM v_suspicious_transactions;


--Вкупна состојба на клиент
CREATE VIEW v_client_total_balance_10 AS
SELECT
    c.client_id,
    c.first_name,
    c.last_name,
    SUM(a.balance) AS total_balance
FROM Client c
JOIN Account a ON c.client_id = a.client_id
WHERE c.client_id = 10
GROUP BY c.client_id, c.first_name, c.last_name;

SELECT *
FROM v_client_total_balance_10;

--Следење на доцнења (Loan Default Monitoring)
CREATE VIEW v_loan_default_monitoring AS
SELECT
    l.loan_id,
    l.client_id,
    li.installment_id,
    li.due_date,
    li.status,
    li.paid_date
FROM Loan_installment li
JOIN Loan l ON li.loan_id = l.loan_id
WHERE li.status = 'LATE'
   OR (li.paid_date IS NULL AND li.due_date < CURRENT_DATE);


SELECT *
FROM v_loan_default_monitoring;


--Accounts (сметки)
CREATE VIEW v_accounts AS
SELECT
    a.account_id,
    a.account_number,
    a.account_type,
    a.balance,
    a.status,
    c.client_id,
    c.first_name,
    c.last_name,
    b.branch_name
FROM Account a
JOIN Client c ON a.client_id = c.client_id
JOIN Branch b ON a.branch_id = b.branch_id
WHERE a.status = 'ACTIVE'
  AND c.status = 'ACTIVE'
  AND a.balance >= 0;


SELECT *
FROM v_accounts;



--Камати по штедни сметки
CREATE VIEW v_savings_interest_payments AS
SELECT
    ip.interest_payment_id,
    ip.amount,
    ip.payment_date,
    ip.period_start,
    ip.period_end,
    a.account_id,
    a.account_number,
    sa.interest_rate
FROM InterestPayment ip
JOIN Account a ON ip.account_id = a.account_id
JOIN SavingsAccount sa ON sa.account_id = a.account_id
WHERE a.account_type = 'SAVINGS'
  AND a.status = 'ACTIVE'
  AND ip.amount > 0
  AND a.balance >= sa.minimum_balance
  AND sa.interest_rate > 0;
SELECT *
FROM v_savings_interest_payments;


--Дневен извештај на филијала
CREATE VIEW v_daily_branch_report AS
SELECT
    dr.report_id,
    dr.report_date,
    dr.total_transactions,
    dr.total_amount,
    b.branch_id,
    b.branch_name,
    b.city
FROM Daily_report dr
JOIN Branch b ON dr.branch_id = b.branch_id
WHERE dr.report_date IS NOT NULL
  AND dr.report_date <= CURRENT_DATE
  AND dr.total_transactions >= 0
  AND dr.total_amount >= 0;

SELECT *
FROM v_daily_branch_report;