FunkciiTrigeri: Funkcii,proceduri i indeksi.sql

-- =========================================================
-- SETUP FIXES / INDEXES
-- =========================================================

SELECT setval(
    'car_dealership.configurationpackage_id_seq',
    COALESCE((SELECT MAX(id) FROM car_dealership.ConfigurationPackage), 0) + 1,
    false
);

CREATE INDEX IF NOT EXISTS idx_testdrive_vin_date_times
ON car_dealership.TestDrive (vin, date, time_start, time_end);


-- =========================================================
-- CONSTRAINTS
-- =========================================================

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'chk_payment_amount_positive'
    ) THEN
        ALTER TABLE car_dealership.Payment
        ADD CONSTRAINT chk_payment_amount_positive
        CHECK (amount > 0);
    END IF;
END;
$$;

-- Prevent duplicate contracts per order and per vehicle.
-- These back the existence checks in complete_sale_from_order and
-- also guard against concurrent calls slipping through the IF EXISTS checks.
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'uq_contract_order_id'
    ) THEN
        ALTER TABLE car_dealership.Contract
        ADD CONSTRAINT uq_contract_order_id UNIQUE (order_id);
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'uq_contract_vin'
    ) THEN
        ALTER TABLE car_dealership.Contract
        ADD CONSTRAINT uq_contract_vin UNIQUE (vin);
    END IF;
END;
$$;


-- =========================================================
-- FUNCTION 1: Calculate configuration total
-- =========================================================

CREATE OR REPLACE FUNCTION car_dealership.calculate_configuration_total(
    p_configuration_id INT
)
RETURNS NUMERIC(10, 2)
LANGUAGE sql
AS $$
    SELECT v.price + COALESCE(SUM(ep.price), 0)
    FROM car_dealership.configuration c
             JOIN car_dealership.vehicle v
                  ON v.vin = c.vin
             LEFT JOIN car_dealership.configurationpackage cp
                       ON cp.configuration_id = c.id
             LEFT JOIN car_dealership.equipmentpackage ep
                       ON ep.id = cp.package_id
    WHERE c.id = p_configuration_id
    GROUP BY v.price;
$$;


-- =========================================================
-- FUNCTION 2: Check vehicle availability
-- =========================================================

CREATE OR REPLACE FUNCTION car_dealership.is_vehicle_available(
    p_vin VARCHAR
)
RETURNS BOOLEAN
LANGUAGE sql
AS $$
    SELECT EXISTS (
        SELECT 1
        FROM car_dealership.vehicle v
                 JOIN car_dealership.status s
                      ON s.id = v.status_id
        WHERE v.vin = p_vin
          AND s.status = 'In Stock'
    );
$$;


-- =========================================================
-- FUNCTION 3: Get customer total spent
-- =========================================================

CREATE OR REPLACE FUNCTION car_dealership.get_customer_total_spent(
    p_customer_id INT
)
RETURNS NUMERIC(10, 2)
LANGUAGE sql
AS $$
    SELECT COALESCE(SUM(p.amount), 0)
    FROM car_dealership.sale s
             JOIN car_dealership.payment p
                  ON p.sale_id = s.id
    WHERE s.customer_id = p_customer_id;
$$;


-- =========================================================
-- PROCEDURE 1: Create customer configuration
-- =========================================================

CREATE OR REPLACE PROCEDURE car_dealership.create_customer_configuration(
    p_customer_id INT,
    p_vin         VARCHAR,
    p_description VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_vehicle_price    NUMERIC(10, 2);
    v_configuration_id INT;
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM car_dealership.Customer WHERE id = p_customer_id
    ) THEN
        RAISE EXCEPTION 'Customer with id % does not exist.', p_customer_id;
    END IF;

    -- is_vehicle_available returns false for non-existent VINs as well,
    -- so a single check covers both the existence and availability cases.
    IF NOT car_dealership.is_vehicle_available(p_vin) THEN
        RAISE EXCEPTION 'Vehicle % does not exist or is not available for configuration.', p_vin;
    END IF;

    SELECT price INTO v_vehicle_price
    FROM car_dealership.Vehicle
    WHERE vin = p_vin;

    INSERT INTO car_dealership.Configuration(vin, description, total_price, created_at, customer_id)
    VALUES (p_vin, p_description, v_vehicle_price, CURRENT_DATE, p_customer_id)
    RETURNING id INTO v_configuration_id;

    RAISE NOTICE 'Configuration % created for customer %.', v_configuration_id, p_customer_id;
END;
$$;


-- =========================================================
-- PROCEDURE 2: Add package to configuration
-- =========================================================

CREATE OR REPLACE PROCEDURE car_dealership.add_package_to_configuration(
    p_configuration_id INT,
    p_package_id       INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM car_dealership.Configuration WHERE id = p_configuration_id
    ) THEN
        RAISE EXCEPTION 'Configuration with id % does not exist.', p_configuration_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM car_dealership.EquipmentPackage WHERE id = p_package_id
    ) THEN
        RAISE EXCEPTION 'Equipment package with id % does not exist.', p_package_id;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM car_dealership.ConfigurationPackage
        WHERE configuration_id = p_configuration_id
          AND package_id = p_package_id
    ) THEN
        RAISE EXCEPTION 'Package % already exists in configuration %.', p_package_id, p_configuration_id;
    END IF;

    INSERT INTO car_dealership.ConfigurationPackage(configuration_id, package_id)
    VALUES (p_configuration_id, p_package_id);

    RAISE NOTICE 'Package % added to configuration %.', p_package_id, p_configuration_id;
END;
$$;


-- =========================================================
-- PROCEDURE 3: Create order from configuration
--
-- NOTE: The configuration ownership check (customer_id = p_customer_id
-- AND vin = p_vin) is intentional. A configuration is created by a
-- specific customer for a specific vehicle; the order must match both.
-- If the business requirement changes so that any customer can reuse an
-- existing configuration against a different vehicle, remove the vin and
-- customer_id predicates from the check below.
-- =========================================================

CREATE OR REPLACE PROCEDURE car_dealership.create_order_from_configuration(
    p_customer_id      INT,
    p_configuration_id INT,
    p_employee_id      INT,
    p_vin              VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_reserved_status_id INT;
    v_order_id           INT;
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM car_dealership.Customer WHERE id = p_customer_id
    ) THEN
        RAISE EXCEPTION 'Customer with id % does not exist.', p_customer_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM car_dealership.Employee WHERE id = p_employee_id
    ) THEN
        RAISE EXCEPTION 'Employee with id % does not exist.', p_employee_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM car_dealership.Configuration
        WHERE id          = p_configuration_id
          AND customer_id = p_customer_id
          AND vin         = p_vin
    ) THEN
        RAISE EXCEPTION 'Configuration % does not belong to customer % or does not match vehicle %.',
            p_configuration_id, p_customer_id, p_vin;
    END IF;

    -- is_vehicle_available covers both existence and In Stock status.
    IF NOT car_dealership.is_vehicle_available(p_vin) THEN
        RAISE EXCEPTION 'Vehicle % does not exist or is not available for ordering.', p_vin;
    END IF;

    SELECT id INTO v_reserved_status_id
    FROM car_dealership.Status
    WHERE status = 'Reserved';

    IF v_reserved_status_id IS NULL THEN
        RAISE EXCEPTION 'Status ''Reserved'' does not exist in the Status table.';
    END IF;

    INSERT INTO car_dealership."Order"(customer_id, configuration_id, employee_id, date, status)
    VALUES (p_customer_id, p_configuration_id, p_employee_id, CURRENT_DATE, 'Pending')
    RETURNING id INTO v_order_id;

    UPDATE car_dealership.Vehicle
    SET status_id = v_reserved_status_id
    WHERE vin = p_vin;

    RAISE NOTICE 'Order % created for customer %, vehicle % reserved.', v_order_id, p_customer_id, p_vin;
END;
$$;


-- =========================================================
-- PROCEDURE 4: Complete sale from order
--
-- NOTE: Business logic (marking the order Completed and the vehicle Sold)
-- lives here rather than in a trigger so that the execution path is explicit
-- and auditable. Sale rows must only ever be inserted through this procedure;
-- direct inserts will leave orders and vehicles in an inconsistent state.
-- The UNIQUE constraints on Contract(order_id) and Contract(vin) added above
-- provide a hard concurrency guard on top of the IF EXISTS soft checks.
-- =========================================================

CREATE OR REPLACE PROCEDURE car_dealership.complete_sale_from_order(
    p_order_id       INT,
    p_contract_type  VARCHAR,
    p_payment_type   VARCHAR,
    p_notes          VARCHAR DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_customer_id      INT;
    v_employee_id      INT;
    v_configuration_id INT;
    v_order_status     VARCHAR;
    v_vin              VARCHAR(17);
    v_total_price      NUMERIC(10, 2);
    v_contract_id      INT;
    v_sale_id          INT;
    v_sold_status_id   INT;
BEGIN
    IF p_contract_type NOT IN ('Standard', 'Finance', 'Fleet') THEN
        RAISE EXCEPTION 'Invalid contract type: %', p_contract_type;
    END IF;

    IF p_payment_type NOT IN ('Cash', 'Installment', 'Leasing') THEN
        RAISE EXCEPTION 'Invalid payment type: %', p_payment_type;
    END IF;

    SELECT o.customer_id, o.employee_id, o.configuration_id, o.status, c.vin
    INTO   v_customer_id, v_employee_id, v_configuration_id, v_order_status, v_vin
    FROM   car_dealership."Order" o
    JOIN   car_dealership.Configuration c ON c.id = o.configuration_id
    WHERE  o.id = p_order_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order with id % does not exist.', p_order_id;
    END IF;

    IF v_order_status NOT IN ('Pending', 'Confirmed') THEN
        RAISE EXCEPTION 'Only Pending or Confirmed orders can be completed. Current status: %', v_order_status;
    END IF;

    -- Soft duplicate guards; the UNIQUE constraints are the hard concurrency backstop.
    IF EXISTS (SELECT 1 FROM car_dealership.Contract WHERE order_id = p_order_id) THEN
        RAISE EXCEPTION 'Contract already exists for order %.', p_order_id;
    END IF;

    IF EXISTS (SELECT 1 FROM car_dealership.Contract WHERE vin = v_vin) THEN
        RAISE EXCEPTION 'Vehicle with VIN % already has a contract and cannot be sold again.', v_vin;
    END IF;

    SELECT id INTO v_sold_status_id
    FROM car_dealership.Status
    WHERE status = 'Sold';

    IF v_sold_status_id IS NULL THEN
        RAISE EXCEPTION 'Status ''Sold'' does not exist in the Status table.';
    END IF;

    v_total_price := car_dealership.calculate_configuration_total(v_configuration_id);

    INSERT INTO car_dealership.Contract(employee_id, notes, date, type, order_id, customer_id, vin)
    VALUES (v_employee_id, p_notes, CURRENT_DATE, p_contract_type, p_order_id, v_customer_id, v_vin)
    RETURNING id INTO v_contract_id;

    INSERT INTO car_dealership.Sale(date, employee_id, contract_id, customer_id)
    VALUES (CURRENT_DATE, v_employee_id, v_contract_id, v_customer_id)
    RETURNING id INTO v_sale_id;

    INSERT INTO car_dealership.Payment(type, amount, sale_id)
    VALUES (p_payment_type, v_total_price, v_sale_id);

    UPDATE car_dealership."Order"
    SET status = 'Completed'
    WHERE id = p_order_id;

    UPDATE car_dealership.Vehicle
    SET status_id = v_sold_status_id
    WHERE vin = v_vin;

    RAISE NOTICE 'Sale completed. Contract %, Sale %, Amount %.', v_contract_id, v_sale_id, v_total_price;
END;
$$;


-- =========================================================
-- PROCEDURE 5: Create test drive
-- =========================================================

CREATE OR REPLACE PROCEDURE car_dealership.create_test_drive(
    p_customer_id INT,
    p_vin         VARCHAR,
    p_date        DATE,
    p_time_start  TIMESTAMP,
    p_time_end    TIMESTAMP,
    p_result      VARCHAR DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_test_drive_id INT;
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM car_dealership.Customer WHERE id = p_customer_id
    ) THEN
        RAISE EXCEPTION 'Customer with id % does not exist.', p_customer_id;
    END IF;

    -- is_vehicle_available returns false for non-existent VINs,
    -- but we check existence separately to give a clearer error message.
    IF NOT EXISTS (
        SELECT 1 FROM car_dealership.Vehicle WHERE vin = p_vin
    ) THEN
        RAISE EXCEPTION 'Vehicle with VIN % does not exist.', p_vin;
    END IF;

    IF NOT car_dealership.is_vehicle_available(p_vin) THEN
        RAISE EXCEPTION 'Vehicle % is not available for a test drive.', p_vin;
    END IF;

    IF p_time_end <= p_time_start THEN
        RAISE EXCEPTION 'Test drive end time must be after start time.';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM car_dealership.TestDrive td
        WHERE td.vin        = p_vin
          AND td.date       = p_date
          AND p_time_start  < td.time_end
          AND p_time_end    > td.time_start
    ) THEN
        RAISE EXCEPTION 'Vehicle % already has a test drive scheduled in this time period.', p_vin;
    END IF;

    INSERT INTO car_dealership.TestDrive(customer_id, vin, date, time_start, time_end, result)
    VALUES (p_customer_id, p_vin, p_date, p_time_start, p_time_end, p_result)
    RETURNING id INTO v_test_drive_id;

    RAISE NOTICE 'Test drive % created for customer %, vehicle %.', v_test_drive_id, p_customer_id, p_vin;
END;
$$;


-- =========================================================
-- PROCEDURE 6: Refresh dashboard materialized views
-- =========================================================

CREATE OR REPLACE PROCEDURE car_dealership.refresh_dealership_dashboard_views()
LANGUAGE plpgsql
AS $$
BEGIN
    REFRESH MATERIALIZED VIEW car_dealership.BrandPopularityView;
    REFRESH MATERIALIZED VIEW car_dealership.PopularConfigurationsView;
    REFRESH MATERIALIZED VIEW car_dealership.AvailableVehiclesByBudgetView;

    RAISE NOTICE 'Dealership dashboard materialized views refreshed.';
END;
$$;


-- =========================================================
-- TRIGGER FUNCTION 1: Update configuration total
-- Kept as a trigger because it is pure derived-data maintenance with
-- no business rules — the correct use case for a trigger.
-- =========================================================

CREATE OR REPLACE FUNCTION car_dealership.trg_update_configuration_total()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        UPDATE car_dealership.Configuration
        SET total_price = car_dealership.calculate_configuration_total(OLD.configuration_id)
        WHERE id = OLD.configuration_id;
        RETURN OLD;
    END IF;

    UPDATE car_dealership.Configuration
    SET total_price = car_dealership.calculate_configuration_total(NEW.configuration_id)
    WHERE id = NEW.configuration_id;

    -- If a row is moved to a different configuration, recalculate the old one too.
    IF TG_OP = 'UPDATE' AND OLD.configuration_id <> NEW.configuration_id THEN
        UPDATE car_dealership.Configuration
        SET total_price = car_dealership.calculate_configuration_total(OLD.configuration_id)
        WHERE id = OLD.configuration_id;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS update_configuration_total_after_package_change
ON car_dealership.ConfigurationPackage;

CREATE TRIGGER update_configuration_total_after_package_change
AFTER INSERT OR UPDATE OR DELETE
ON car_dealership.ConfigurationPackage
FOR EACH ROW
EXECUTE FUNCTION car_dealership.trg_update_configuration_total();


-- =========================================================
-- REMOVE OLD TRIGGERS AND THEIR FUNCTIONS
-- Business logic has been moved into procedures.
-- =========================================================

DROP TRIGGER IF EXISTS validate_test_drive          ON car_dealership.TestDrive;
DROP TRIGGER IF EXISTS complete_order_and_sell_vehicle ON car_dealership.Sale;
DROP TRIGGER IF EXISTS validate_payment              ON car_dealership.Payment;

DROP FUNCTION IF EXISTS car_dealership.trg_validate_test_drive();
DROP FUNCTION IF EXISTS car_dealership.trg_complete_order_and_sell_vehicle();
DROP FUNCTION IF EXISTS car_dealership.trg_validate_payment();