DatabaseCreation: views.sql

-- =============================================================================
-- VIEW 1: Contract details (base view)
-- =============================================================================
-- Contract details with both parties resolved.
CREATE OR REPLACE VIEW vw_contract_details AS
SELECT cvc.contract_id,
       cvc.contract_number,
       cvc.contract_title,
       cvc.start_date,
       cvc.end_date,
       cvc.total_value,
       cvc.currency_code,
       cvc.is_active,
       c.client_id,
       c.company_name,
       v.vendor_id,
       v.agency_name
FROM Client_Vendor_Contract cvc
         JOIN Client c ON c.client_id = cvc.client_id
         JOIN Vendor v ON v.vendor_id = cvc.vendor_id;


-- =============================================================================
-- VIEW 2: Project details (base view)
-- =============================================================================
-- Shared project details for the reporting views: the project with its
-- status, its contract and both parties.
CREATE OR REPLACE VIEW vw_project_details AS
SELECT p.project_id,
       p.project_name,
       p.status_id,
       ps.status_name,
       p.start_date,
       p.end_date,
       p.budget,
       cd.contract_id,
       cd.contract_number,
       cd.currency_code,
       cd.client_id,
       cd.company_name,
       cd.vendor_id,
       cd.agency_name
FROM Project p
         JOIN vw_contract_details cd ON cd.contract_id = p.contract_id
         JOIN Project_Status ps ON ps.status_id = p.status_id;


-- =============================================================================
-- VIEW 3: All projects per Vendor
-- =============================================================================
CREATE OR REPLACE VIEW vw_projects_per_vendor AS
SELECT pd.vendor_id,
       pd.agency_name,
       pd.project_id,
       pd.project_name,
       pd.status_name,
       pd.start_date,
       pd.end_date,
       pd.budget,
       pd.currency_code
FROM vw_project_details pd
ORDER BY pd.agency_name, pd.status_name, pd.project_name;


-- =============================================================================
-- VIEW 4: All projects per Client
-- =============================================================================
CREATE OR REPLACE VIEW vw_projects_per_client AS
SELECT pd.client_id,
       pd.company_name,
       pd.project_id,
       pd.project_name,
       pd.status_name,
       pd.start_date,
       pd.end_date,
       pd.budget,
       pd.currency_code
FROM vw_project_details pd
ORDER BY pd.company_name, pd.status_name, pd.project_name;


-- =============================================================================
-- VIEW 5: Budget summary per Vendor
-- =============================================================================
CREATE OR REPLACE VIEW vw_budget_per_vendor AS
SELECT pd.vendor_id,
       pd.agency_name,
       pd.currency_code,
       COUNT(pd.project_id) AS project_count,
       SUM(pd.budget)       AS total_budget
FROM vw_project_details pd
GROUP BY pd.vendor_id, pd.agency_name, pd.currency_code
ORDER BY pd.agency_name, pd.currency_code;


-- =============================================================================
-- VIEW 6: Budget summary per Client
-- =============================================================================
CREATE OR REPLACE VIEW vw_budget_per_client AS
SELECT pd.client_id,
       pd.company_name,
       pd.currency_code,
       COUNT(pd.project_id) AS project_count,
       SUM(pd.budget)       AS total_budget
FROM vw_project_details pd
GROUP BY pd.client_id, pd.company_name, pd.currency_code
ORDER BY pd.company_name, pd.currency_code;


-- =============================================================================
-- VIEW 7: List of Clients per Industry
-- =============================================================================
CREATE OR REPLACE VIEW vw_clients_per_industry AS
SELECT i.industry_id,
       i.industry_name,
       c.client_id,
       c.company_name,
       c.contact_email
FROM Client c
         JOIN Industry i ON i.industry_id = c.industry_id
ORDER BY i.industry_name, c.company_name;


-- =============================================================================
-- VIEW 8: Average rating per Vendor (across all rating dimensions)
-- =============================================================================
-- Aggregates the scores of published reviews. Vendors without a scored
-- published review stay in the result with review_count = 0 and
-- avg_rating = NULL.
-- The scores are summed and counted per review first; the vendor average
-- SUM(score_sum) / SUM(score_cnt) is the same score-weighted mean as
-- AVG(score_value) over all of the vendor's scores, and each scored review
-- is counted once without a COUNT(DISTINCT).
CREATE OR REPLACE VIEW vw_avg_rating_per_vendor AS
SELECT v.vendor_id,
       v.agency_name,
       COUNT(vr.review_id)                                        AS review_count,
       ROUND(SUM(vr.score_sum) / NULLIF(SUM(vr.score_cnt), 0), 2) AS avg_rating
FROM Vendor v
         LEFT JOIN (SELECT pd.vendor_id,
                           r.review_id,
                           SUM(rs.score_value)::numeric AS score_sum,
                           COUNT(*)                     AS score_cnt
                    FROM Review r
                             JOIN Review_Score rs ON rs.review_id = r.review_id
                             JOIN vw_project_details pd ON pd.project_id = r.project_id
                    WHERE r.is_published = true
                    GROUP BY pd.vendor_id, r.review_id) vr ON vr.vendor_id = v.vendor_id
GROUP BY v.vendor_id, v.agency_name
ORDER BY avg_rating DESC NULLS LAST;


-- =============================================================================
-- VIEW 9: All unresolved Dispute Tickets
-- =============================================================================
-- The vendor columns identify the agency of the filing user.
CREATE OR REPLACE VIEW vw_unresolved_dispute_tickets AS
SELECT dt.ticket_id,
       dt.filed_at,
       dt.reason,
       r.review_id,
       p.project_id,
       p.project_name,
       dt.vendor_user_id                        AS filed_by_vendor_user_id,
       vusr.first_name || ' ' || vusr.last_name AS filed_by_vendor_user,
       dt.assigned_management_user_id,
       musr.first_name || ' ' || musr.last_name AS assigned_to_management_user,
       dt.created_at,
       dt.updated_at,
       ven.vendor_id,
       ven.agency_name
FROM Dispute_Ticket dt
         JOIN Review r ON r.review_id = dt.review_id
         JOIN Project p ON p.project_id = r.project_id
         JOIN Vendor_User vu ON vu.user_id = dt.vendor_user_id
         JOIN Vendor ven ON ven.vendor_id = vu.vendor_id
         JOIN "User" vusr ON vusr.user_id = vu.user_id
         LEFT JOIN "User" musr ON musr.user_id = dt.assigned_management_user_id
WHERE dt.is_resolved = false
ORDER BY dt.filed_at;


-- =============================================================================
-- VIEW 10: Project count per Status per Vendor
-- =============================================================================
CREATE OR REPLACE VIEW vw_project_count_by_status_per_vendor AS
SELECT pd.vendor_id,
       pd.agency_name,
       pd.status_id,
       pd.status_name,
       COUNT(pd.project_id) AS project_count
FROM vw_project_details pd
GROUP BY pd.vendor_id, pd.agency_name, pd.status_id, pd.status_name
ORDER BY pd.agency_name, pd.status_name;


-- =============================================================================
-- VIEW 11: Project count per Status per Client
-- =============================================================================
CREATE OR REPLACE VIEW vw_project_count_by_status_per_client AS
SELECT pd.client_id,
       pd.company_name,
       pd.status_id,
       pd.status_name,
       COUNT(pd.project_id) AS project_count
FROM vw_project_details pd
GROUP BY pd.client_id, pd.company_name, pd.status_id, pd.status_name
ORDER BY pd.company_name, pd.status_name;


-- =============================================================================
-- VIEW 12: Contracts per Vendor
-- =============================================================================
CREATE OR REPLACE VIEW vw_contracts_per_vendor AS
SELECT cd.vendor_id,
       cd.agency_name,
       cd.contract_id,
       cd.contract_number,
       cd.contract_title,
       cd.client_id,
       cd.company_name AS client_name,
       cd.start_date,
       cd.end_date,
       cd.total_value,
       cd.currency_code,
       cd.is_active
FROM vw_contract_details cd
ORDER BY cd.agency_name, cd.is_active DESC, cd.start_date DESC;


-- =============================================================================
-- VIEW 13: Contracts per Client
-- =============================================================================
CREATE OR REPLACE VIEW vw_contracts_per_client AS
SELECT cd.client_id,
       cd.company_name,
       cd.contract_id,
       cd.contract_number,
       cd.contract_title,
       cd.vendor_id,
       cd.agency_name AS vendor_name,
       cd.start_date,
       cd.end_date,
       cd.total_value,
       cd.currency_code,
       cd.is_active
FROM vw_contract_details cd
ORDER BY cd.company_name, cd.is_active DESC, cd.start_date DESC;


-- =============================================================================
-- VIEW 14: Vendor subscription status
-- =============================================================================
CREATE OR REPLACE VIEW vw_vendor_subscriptions AS
SELECT v.vendor_id,
       v.agency_name,
       st.tier_id,
       st.tier_name,
       COALESCE(vs.negotiated_price, st.list_price, 0) AS effective_price,
       vs.start_date,
       vs.end_date,
       vs.is_active
FROM Vendor_Subscription vs
         JOIN Vendor v ON v.vendor_id = vs.vendor_id
         JOIN Subscription_Tier st ON st.tier_id = vs.tier_id
ORDER BY v.agency_name, vs.is_active DESC, vs.start_date DESC;


-- =============================================================================
-- VIEW 15: Rating per Vendor broken down by rating dimension
-- =============================================================================
-- Per-dimension scores from published reviews.
CREATE OR REPLACE VIEW vw_vendor_rating_by_dimension AS
SELECT pd.vendor_id,
       pd.agency_name,
       rd.dimension_id,
       rd.dimension_name,
       COUNT(*)                      AS score_count,
       ROUND(AVG(rs.score_value), 2) AS avg_score,
       MIN(rs.score_value)           AS min_score,
       MAX(rs.score_value)           AS max_score
FROM Review_Score rs
         JOIN Rating_Dimension rd ON rd.dimension_id = rs.dimension_id
         JOIN Review r ON r.review_id = rs.review_id
         JOIN vw_project_details pd ON pd.project_id = r.project_id
WHERE r.is_published = true
GROUP BY pd.vendor_id, pd.agency_name, rd.dimension_id, rd.dimension_name;


-- =============================================================================
-- VIEW 16: Budget changes per Project (the budget audit trail)
-- =============================================================================
-- One row per recorded budget change; delta and pct_change are derived here.
CREATE OR REPLACE VIEW vw_project_budget_changes AS
SELECT pba.audit_id,
       pd.project_id,
       pd.project_name,
       pd.vendor_id,
       pd.client_id,
       pd.currency_code,
       pba.old_budget,
       pba.new_budget,
       pba.new_budget - pba.old_budget AS delta,
       ROUND(100.0 * (pba.new_budget - pba.old_budget) / NULLIF(pba.old_budget, 0), 1) AS pct_change,
       pba.created_at                  AS changed_at
FROM Project_Budget_Audit pba
         JOIN vw_project_details pd ON pd.project_id = pba.project_id;