CREATE OR REPLACE VIEW car_dealership.EmployeePerformanceView AS
SELECT e.id,
       (e.first_name || ' ' || e.last_name) AS name,
       e.position,
       COUNT(s.id)                          AS "number of sold cars",
       SUM(p.amount)                        AS revenue,
       ROUND(AVG(p.amount), 2)              AS "avg sale value"
FROM car_dealership.employee e
         INNER JOIN car_dealership.sale s
                    ON e.id = s.employee_id
         INNER JOIN car_dealership.payment p
                    ON s.id = p.sale_id
GROUP BY e.id, e.first_name, e.last_name, e.position;


CREATE MATERIALIZED VIEW car_dealership.BrandPopularityView AS
SELECT b.brand,
       COUNT(s.id) AS total_sales
FROM car_dealership.sale s
         JOIN car_dealership.contract c ON s.contract_id = c.id
         JOIN car_dealership.vehicle v ON c.vin = v.vin
         JOIN car_dealership.model m ON v.model_id = m.id
         JOIN car_dealership.brand b ON m.brand_id = b.id
GROUP BY b.brand;

CREATE OR REPLACE VIEW car_dealership.CustomerValueView AS
SELECT c.id,
       (c.first_name || ' ' || c.last_name) AS name,
       COUNT(s.id)                          AS "total purchases",
       COALESCE(SUM(p.amount), 0)           AS "total spent",
       COALESCE(AVG(p.amount), 0)           AS "avg purchase value",
       MAX(s.date)                          AS "last purchase date"
FROM car_dealership.customer c
         INNER JOIN car_dealership.sale s ON c.id = s.customer_id
         INNER JOIN car_dealership.payment p ON s.id = p.sale_id
WHERE s.date >= CURRENT_DATE - INTERVAL '2 years'
GROUP BY c.id, c.first_name, c.last_name;

CREATE OR REPLACE VIEW car_dealership.VehiclesInStockView AS
SELECT v.vin,
       (b.brand || ' - ' || m.model) AS model,
       m.year                        AS year,
       v.color,
       v.price,
       vt.type                       AS "vehicle type",
       f.name                        AS factory
FROM car_dealership.vehicle v
         JOIN car_dealership.model m ON m.id = v.model_id
         JOIN car_dealership.brand b ON b.id = m.brand_id
         JOIN car_dealership.vehicletype vt ON vt.id = v.vehicle_type_id
         JOIN car_dealership.status st ON st.id = v.status_id
         LEFT JOIN car_dealership.production p ON p.vin = v.vin
         LEFT JOIN car_dealership.factory f ON f.id = p.factory_id
WHERE st.status = 'In Stock';
select * from car_dealership.VehiclesInStockView;

CREATE OR REPLACE VIEW car_dealership.MonthlySalesView AS
SELECT EXTRACT(YEAR FROM s.date)::integer  AS year,
       EXTRACT(MONTH FROM s.date)::integer AS month,
       COUNT(s.id)                         AS "total sales",
       SUM(p.amount)                       AS "total revenue",
       AVG(p.amount)                       AS "avg sale value",
       COUNT(DISTINCT s.customer_id)       AS "unique customers"
FROM car_dealership.sale s
         INNER JOIN car_dealership.payment p ON s.id = p.sale_id
WHERE s.date >= DATE_TRUNC('year', CURRENT_DATE) - INTERVAL '2 years'
GROUP BY EXTRACT(YEAR FROM s.date), EXTRACT(MONTH FROM s.date);


-- View created in 46ms
-- Fetched data in 13s
CREATE MATERIALIZED VIEW car_dealership.PopularConfigurationsView AS
SELECT ep.id        AS package_id,
       ep.name      AS "package name",
       ep.price,
       COUNT(cp.id) AS "times selected",
       et.name      AS "equipment type"
FROM car_dealership.equipmentpackage ep
         JOIN car_dealership.configurationpackage cp ON ep.id = cp.package_id
         JOIN car_dealership.equipmenttype et ON ep.equipment_type_id = et.id
GROUP BY ep.id, ep.name, ep.price, et.name;

DROP VIEW IF EXISTS car_dealership.RecentTestDrivesView;
-- View created in 29ms
-- Fetched data in 3s
CREATE OR REPLACE VIEW car_dealership.RecentTestDrivesView AS
SELECT td.id                              AS test_drive_id,
       c.id                               AS customer_id,
       c.first_name || ' ' || c.last_name AS customer_name,
       c.phone,
       b.brand,
       m.model,
       v.vin,
       td.date,
       td.time_start,
       td.time_end,
       td.result
FROM car_dealership.testdrive td
         JOIN car_dealership.customer c ON c.id = td.customer_id
         JOIN car_dealership.vehicle v ON v.vin = td.vin
         JOIN car_dealership.model m ON m.id = v.model_id
         JOIN car_dealership.brand b ON b.id = m.brand_id
WHERE td.date >= CURRENT_DATE - INTERVAL '90 days';


DROP VIEW IF EXISTS car_dealership.PendingOrdersView;
-- View created in 28ms
-- Fetched data in 2s 369ms
CREATE OR REPLACE VIEW car_dealership.PendingOrdersView AS
SELECT o.id                               AS order_id,
       o.customer_id,
       o.date                             AS order_date,
       c.first_name || ' ' || c.last_name AS customer_name,
       c.phone,
       c.email,
       e.first_name || ' ' || e.last_name AS employee_name,
       b.brand,
       m.model,
       v.vin,
       f.name                             AS factory
FROM car_dealership."Order" o
         JOIN car_dealership.customer c ON o.customer_id = c.id
         JOIN car_dealership.employee e ON o.employee_id = e.id
         JOIN car_dealership.configuration conf ON o.configuration_id = conf.id
         JOIN car_dealership.vehicle v ON conf.vin = v.vin
         JOIN car_dealership.model m ON v.model_id = m.id
         JOIN car_dealership.brand b ON m.brand_id = b.id
         LEFT JOIN car_dealership.production p ON p.vin = v.vin
         LEFT JOIN car_dealership.factory f ON f.id = p.factory_id
WHERE o.status = 'Pending';


CREATE MATERIALIZED VIEW car_dealership.AvailableVehiclesByBudgetView AS
SELECT v.vin,
       b.brand,
       m.model,
       m.year  AS model_year,
       vt.type AS vehicle_type,
       v.color,
       v.price,
       CASE
           WHEN v.price < 15000 THEN 'Budget'
           WHEN v.price < 30000 THEN 'Mid-Range'
           WHEN v.price < 55000 THEN 'Premium'
           WHEN v.price < 90000 THEN 'Luxury'
           ELSE 'Ultra-Luxury'
           END AS budget_tier,
       CASE
           WHEN v.price < 15000 THEN 1
           WHEN v.price < 30000 THEN 2
           WHEN v.price < 55000 THEN 3
           WHEN v.price < 90000 THEN 4
           ELSE 5
           END AS tier_sort_order
FROM car_dealership.vehicle v
         JOIN car_dealership.model m ON m.id = v.model_id
         JOIN car_dealership.brand b ON b.id = m.brand_id
         JOIN car_dealership.vehicletype vt ON vt.id = v.vehicle_type_id
         JOIN car_dealership.status st ON st.id = v.status_id
WHERE st.status = 'In Stock';

-- drop materialized view if exists car_dealership.NewArrivalsView;
-- CREATE MATERIALIZED VIEW car_dealership.NewArrivalsView AS
-- SELECT v.vin,
--        b.brand,
--        m.model,
--        m.year         AS model_year,
--        vt.type        AS vehicle_type,
--        v.color,
--        v.price,
--        cfg.created_at AS listed_since,
--        f.name         AS factory
-- FROM car_dealership.vehicle v
--          JOIN car_dealership.model m ON m.id = v.model_id
--          JOIN car_dealership.brand b ON b.id = m.brand_id
--          JOIN car_dealership.vehicletype vt ON vt.id = v.vehicle_type_id
--          JOIN car_dealership.status st ON st.id = v.status_id
--          JOIN car_dealership.configuration cfg ON cfg.vin = v.vin
--          LEFT JOIN car_dealership.production p ON p.vin = v.vin
--          LEFT JOIN car_dealership.factory f ON f.id = p.factory_id
-- WHERE st.status = 'In Stock'
--   AND cfg.created_at >= CURRENT_DATE - INTERVAL '30 days';