Views, Stored Procedures & Triggers

-- BASIC VIEW: hide a complex join behind a simple name
CREATE VIEW order_summary AS
SELECT
    o.order_id,
    o.order_date,
    o.status,
    o.total_amount,
    c.name        AS customer_name,
    c.city        AS customer_city,
    r.name        AS restaurant_name,
    r.cuisine_type
FROM orders o
JOIN customers c   ON o.customer_id   = c.customer_id
JOIN restaurants r ON o.restaurant_id = r.restaurant_id;

-- Query the view exactly like a table:
SELECT customer_name, SUM(total_amount) AS total_spent
FROM order_summary
WHERE status = 'delivered'
GROUP BY customer_name
ORDER BY total_spent DESC;

-- The database internally rewrites this to:
-- SELECT c.name AS customer_name, SUM(o.total_amount) AS total_spent
-- FROM orders o JOIN customers c ON ... JOIN restaurants r ON ...
-- WHERE o.status = 'delivered'
-- GROUP BY c.name ORDER BY total_spent DESC;

-- SECURITY VIEW: expose only specific rows and columns
CREATE VIEW customer_own_orders AS
SELECT order_id, order_date, status, total_amount
FROM orders
WHERE customer_id = current_setting('app.current_customer_id')::INT;
-- Application sets: SET LOCAL app.current_customer_id = '42';
-- Each customer sees only their own orders through this view

-- COLUMN-MASKING VIEW: hide sensitive data
CREATE VIEW employee_public AS
SELECT employee_id, name, department, title, hire_date
FROM employees;
-- salary, bank_account, pan_number columns are excluded
-- Grant SELECT on employee_public to reporting role — they never see salary

-- CREATE OR REPLACE: modify an existing view without dropping it
CREATE OR REPLACE VIEW order_summary AS
SELECT
    o.order_id,
    o.order_date,
    o.status,
    o.total_amount,
    o.delivery_fee,              -- added column
    c.name    AS customer_name,
    c.city    AS customer_city,
    r.name    AS restaurant_name,
    r.cuisine_type
FROM orders o
JOIN customers c   ON o.customer_id   = c.customer_id
JOIN restaurants r ON o.restaurant_id = r.restaurant_id;
-- Existing queries that use order_summary still work
-- New queries can additionally use delivery_fee

-- DROP a view:
DROP VIEW order_summary;
DROP VIEW IF EXISTS order_summary;               -- no error if it doesn't exist
DROP VIEW order_summary CASCADE;                 -- also drop dependent views
DROP VIEW order_summary RESTRICT;                -- fail if any dependent views exist

-- Your query:
SELECT customer_name, total_amount
FROM order_summary
WHERE customer_city = 'Bengaluru'
  AND total_amount > 500;

-- AFTER QUERY REWRITING (what the optimiser actually sees):
SELECT c.name AS customer_name, o.total_amount
FROM orders o
JOIN customers c   ON o.customer_id   = c.customer_id
JOIN restaurants r ON o.restaurant_id = r.restaurant_id
WHERE c.city = 'Bengaluru'          -- ← predicate pushed INTO the view's query
  AND o.total_amount > 500;          -- ← predicate pushed INTO the view's query

-- The optimiser can now:
-- 1. Use index on customers.city to filter Bengaluru customers first
-- 2. Use index on orders.total_amount for the > 500 filter
-- 3. Choose optimal join order
-- Exactly the same plan as if you'd written the join yourself

-- VERIFY how PostgreSQL expands a view:
EXPLAIN SELECT * FROM order_summary WHERE customer_city = 'Bengaluru';
-- The plan shows the underlying tables, not the view name
-- Confirms that query rewriting happened and predicates were pushed down

-- AUTOMATICALLY UPDATABLE VIEW (one table, no aggregates, no joins):
CREATE VIEW active_customers AS
SELECT customer_id, name, email, city
FROM customers
WHERE is_active = true;

-- These DML operations work automatically:
UPDATE active_customers SET city = 'Bengaluru' WHERE customer_id = 42;
-- Translates to: UPDATE customers SET city = 'Bengaluru' WHERE customer_id = 42

INSERT INTO active_customers (name, email, city)
VALUES ('New User', 'new@email.com', 'Pune');
-- Translates to: INSERT INTO customers (name, email, city) VALUES (...)
-- NOTE: is_active column not in view — inserted with its DEFAULT value

DELETE FROM active_customers WHERE customer_id = 42;
-- Translates to: DELETE FROM customers WHERE customer_id = 42

-- NON-UPDATABLE VIEW (join across multiple tables):
CREATE VIEW order_summary AS
SELECT o.order_id, c.name, o.total_amount
FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

UPDATE order_summary SET name = 'New Name' WHERE order_id = 1;
-- ERROR: cannot update view "order_summary"
-- DETAIL: Views that do not select from a single table or view are not automatically updatable.

-- WITH CHECK OPTION: prevent updates that would make the row disappear from the view
CREATE VIEW high_value_orders AS
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > 500
WITH CHECK OPTION;

UPDATE high_value_orders SET total_amount = 200 WHERE order_id = 1;
-- ERROR: new row violates check option for view "high_value_orders"
-- The update would change total_amount to 200 < 500 — the row would vanish from the view
-- WITH CHECK OPTION prevents this inconsistency

-- LOCAL vs CASCADED check option:
-- WITH LOCAL CHECK OPTION: check only this view's WHERE condition
-- WITH CASCADED CHECK OPTION: check this view AND all underlying views' conditions (default)

-- Non-updatable join view:
CREATE VIEW order_with_customer AS
SELECT
    o.order_id,
    o.total_amount,
    o.status,
    c.customer_id,
    c.name    AS customer_name,
    c.email   AS customer_email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- Define INSTEAD OF trigger for UPDATE:
CREATE OR REPLACE FUNCTION update_order_with_customer()
RETURNS TRIGGER AS $$
BEGIN
    -- Update the orders table for order-specific columns
    IF NEW.total_amount IS DISTINCT FROM OLD.total_amount
    OR NEW.status       IS DISTINCT FROM OLD.status THEN
        UPDATE orders
        SET total_amount = NEW.total_amount,
            status       = NEW.status
        WHERE order_id = OLD.order_id;
    END IF;

    -- Update the customers table for customer-specific columns
    IF NEW.customer_name  IS DISTINCT FROM OLD.customer_name
    OR NEW.customer_email IS DISTINCT FROM OLD.customer_email THEN
        UPDATE customers
        SET name  = NEW.customer_name,
            email = NEW.customer_email
        WHERE customer_id = OLD.customer_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_order_with_customer
INSTEAD OF UPDATE ON order_with_customer
FOR EACH ROW EXECUTE FUNCTION update_order_with_customer();

-- Now this works:
UPDATE order_with_customer
SET status = 'delivered', customer_name = 'Rahul S.'
WHERE order_id = 1001;
-- Trigger fires: updates orders.status AND customers.name correctly

-- CREATE MATERIALIZED VIEW:
CREATE MATERIALIZED VIEW restaurant_monthly_stats AS
SELECT
    r.restaurant_id,
    r.name                              AS restaurant_name,
    r.city,
    DATE_TRUNC('month', o.order_date)   AS month,
    COUNT(o.order_id)                   AS total_orders,
    SUM(o.total_amount)                 AS total_revenue,
    ROUND(AVG(o.total_amount), 2)       AS avg_order_value,
    COUNT(DISTINCT o.customer_id)       AS unique_customers
FROM restaurants r
JOIN orders o ON r.restaurant_id = o.restaurant_id
WHERE o.status = 'delivered'
GROUP BY r.restaurant_id, r.name, r.city, DATE_TRUNC('month', o.order_date);

-- QUERY the materialised view (fast — reads pre-computed data):
SELECT restaurant_name, city, total_revenue
FROM restaurant_monthly_stats
WHERE month = '2024-01-01'
ORDER BY total_revenue DESC
LIMIT 10;
-- Sub-millisecond: reads pre-computed rows, no joins executed

-- ADD INDEXES on the materialised view for even faster access:
CREATE INDEX idx_rmstats_month ON restaurant_monthly_stats(month);
CREATE INDEX idx_rmstats_city  ON restaurant_monthly_stats(city, month);
-- A materialised view is a real table — supports all index types

-- REFRESH: update the materialised view with current data
REFRESH MATERIALIZED VIEW restaurant_monthly_stats;
-- Blocks all reads during refresh (acquires ACCESS EXCLUSIVE lock)
-- Safe for less-busy periods

-- REFRESH CONCURRENTLY: refresh without blocking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY restaurant_monthly_stats;
-- Requires: at least one UNIQUE index on the materialised view
CREATE UNIQUE INDEX idx_rmstats_unique
    ON restaurant_monthly_stats(restaurant_id, month);
-- Now concurrent refresh is possible:
REFRESH MATERIALIZED VIEW CONCURRENTLY restaurant_monthly_stats;
-- Builds new data in the background, swaps atomically — reads continue uninterrupted

-- SCHEDULE automatic refresh (using pg_cron extension):
SELECT cron.schedule(
    'refresh-restaurant-stats',    -- job name
    '0 2 * * *',                  -- cron expression: 2am every day
    'REFRESH MATERIALIZED VIEW CONCURRENTLY restaurant_monthly_stats'
);

-- CHECK when a materialised view was last refreshed:
SELECT schemaname, matviewname, ispopulated
FROM pg_matviews
WHERE matviewname = 'restaurant_monthly_stats';

-- DROP a materialised view:
DROP MATERIALIZED VIEW restaurant_monthly_stats;

-- BASIC FUNCTION: returns a scalar value
CREATE OR REPLACE FUNCTION get_customer_total_spent(p_customer_id INT)
RETURNS DECIMAL(12,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_total DECIMAL(12,2);  -- local variable
BEGIN
    SELECT SUM(total_amount)
    INTO v_total            -- assign query result to variable
    FROM orders
    WHERE customer_id = p_customer_id
      AND status = 'delivered';

    RETURN COALESCE(v_total, 0.00);  -- return 0 if no orders
END;
$$;

-- CALL THE FUNCTION:
SELECT get_customer_total_spent(42);
-- Returns: 1250.00

-- USE IN A QUERY:
SELECT customer_id, name, get_customer_total_spent(customer_id) AS lifetime_value
FROM customers
ORDER BY lifetime_value DESC;

-- ─────────────────────────────────────────────────────────────────
-- FUNCTION WITH MULTIPLE STATEMENTS AND CONTROL FLOW:
-- ─────────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION assign_customer_tier(p_customer_id INT)
RETURNS VARCHAR(20)
LANGUAGE plpgsql
AS $$
DECLARE
    v_total_spent   DECIMAL(12,2);
    v_order_count   INT;
    v_tier          VARCHAR(20);
BEGIN
    -- Get customer statistics
    SELECT
        COALESCE(SUM(total_amount), 0),
        COUNT(*)
    INTO v_total_spent, v_order_count
    FROM orders
    WHERE customer_id = p_customer_id
      AND status = 'delivered';

    -- Determine tier using IF/ELSIF/ELSE
    IF v_total_spent >= 10000 THEN
        v_tier := 'Platinum';
    ELSIF v_total_spent >= 5000 THEN
        v_tier := 'Gold';
    ELSIF v_total_spent >= 1000 THEN
        v_tier := 'Silver';
    ELSIF v_order_count > 0 THEN
        v_tier := 'Bronze';
    ELSE
        v_tier := 'Inactive';
    END IF;

    -- Update the customer record
    UPDATE customers
    SET tier = v_tier, tier_updated_at = CURRENT_TIMESTAMP
    WHERE customer_id = p_customer_id;

    RETURN v_tier;
END;
$$;

-- ─────────────────────────────────────────────────────────────────
-- FUNCTION RETURNING A TABLE (set-returning function):
-- ─────────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION get_top_customers(p_city VARCHAR, p_limit INT DEFAULT 10)
RETURNS TABLE(
    customer_id   INT,
    customer_name VARCHAR(100),
    total_spent   DECIMAL(12,2),
    order_count   BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        c.customer_id,
        c.name,
        SUM(o.total_amount)::DECIMAL(12,2),
        COUNT(o.order_id)
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
                      AND o.status = 'delivered'
    WHERE c.city = p_city
    GROUP BY c.customer_id, c.name
    ORDER BY SUM(o.total_amount) DESC NULLS LAST
    LIMIT p_limit;
END;
$$;

-- USE SET-RETURNING FUNCTION IN FROM CLAUSE:
SELECT * FROM get_top_customers('Bengaluru', 5);
SELECT customer_name, total_spent
FROM get_top_customers('Bengaluru')
WHERE total_spent > 500;

-- STORED PROCEDURE: can COMMIT and ROLLBACK internally
CREATE OR REPLACE PROCEDURE process_order_batch(p_batch_size INT DEFAULT 100)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id      INT;
    v_processed     INT := 0;
    v_failed        INT := 0;
    order_cursor    CURSOR FOR
        SELECT order_id FROM orders
        WHERE status = 'pending'
        ORDER BY order_date
        LIMIT p_batch_size;
BEGIN
    OPEN order_cursor;

    LOOP
        FETCH order_cursor INTO v_order_id;
        EXIT WHEN NOT FOUND;

        BEGIN
            -- Try to process this order
            UPDATE orders
            SET status = 'confirmed',
                confirmed_at = CURRENT_TIMESTAMP
            WHERE order_id = v_order_id;

            -- Decrement inventory for each item in this order
            UPDATE inventory i
            SET stock = i.stock - oi.quantity
            FROM order_items oi
            WHERE oi.order_id = v_order_id
              AND i.product_id = oi.item_id;

            -- Check for negative stock
            IF EXISTS (
                SELECT 1 FROM inventory WHERE stock < 0
            ) THEN
                RAISE EXCEPTION 'Inventory went negative for order %', v_order_id;
            END IF;

            v_processed := v_processed + 1;

            -- COMMIT each order individually (procedures can do this!)
            COMMIT;

        EXCEPTION WHEN OTHERS THEN
            -- Rollback just this order
            ROLLBACK;
            -- Log the failure
            INSERT INTO order_processing_errors(order_id, error_msg, failed_at)
            VALUES (v_order_id, SQLERRM, CURRENT_TIMESTAMP);
            v_failed := v_failed + 1;
        END;
    END LOOP;

    CLOSE order_cursor;

    RAISE NOTICE 'Processed: %, Failed: %', v_processed, v_failed;
END;
$$;

-- CALL a procedure (not SELECT — procedures don't return values):
CALL process_order_batch(50);
CALL process_order_batch();  -- uses default p_batch_size = 100

-- DIFFERENCE: FUNCTION vs PROCEDURE
-- Function: always within caller's transaction, returns a value, used in SELECT
-- Procedure: can manage own transactions, called with CALL, no return value

-- EXCEPTION HANDLING in PL/pgSQL:
CREATE OR REPLACE FUNCTION safe_transfer(
    p_from_account INT,
    p_to_account   INT,
    p_amount       DECIMAL(12,2)
)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    -- Debit the sender
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_from_account
      AND balance >= p_amount;  -- optimistic check

    -- Check if update actually happened
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient funds or account not found: %', p_from_account
            USING ERRCODE = 'P0001';  -- custom error code
    END IF;

    -- Credit the receiver
    UPDATE accounts
    SET balance = balance + p_amount
    WHERE account_id = p_to_account;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Destination account not found: %', p_to_account
            USING ERRCODE = 'P0002';
    END IF;

    RETURN true;

EXCEPTION
    WHEN SQLSTATE 'P0001' THEN
        -- Insufficient funds — log and return false
        INSERT INTO transfer_failures(from_acct, to_acct, amount, reason, failed_at)
        VALUES (p_from_account, p_to_account, p_amount, 'Insufficient funds', NOW());
        RETURN false;

    WHEN SQLSTATE 'P0002' THEN
        INSERT INTO transfer_failures(from_acct, to_acct, amount, reason, failed_at)
        VALUES (p_from_account, p_to_account, p_amount, 'Account not found', NOW());
        RETURN false;

    WHEN OTHERS THEN
        -- Unexpected error — re-raise to caller
        RAISE;
END;
$$;

-- COMMON EXCEPTION NAMES:
-- unique_violation    (SQLSTATE '23505'): duplicate key
-- foreign_key_violation (SQLSTATE '23503'): FK constraint
-- not_null_violation  (SQLSTATE '23502'): NULL in NOT NULL column
-- check_violation     (SQLSTATE '23514'): CHECK constraint
-- division_by_zero    (SQLSTATE '22012')
-- OTHERS: catches everything not matched above

-- USE CASE: Automatically normalise data before storing
-- Trim whitespace from name, standardise email to lowercase, set default city

CREATE OR REPLACE FUNCTION normalise_customer_before_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Trim whitespace and normalise case
    NEW.name  := TRIM(INITCAP(NEW.name));   -- "  rahul sharma  " → "Rahul Sharma"
    NEW.email := LOWER(TRIM(NEW.email));    -- "  RAHUL@EMAIL.COM  " → "rahul@email.com"

    -- Set default city if not provided
    IF NEW.city IS NULL OR NEW.city = '' THEN
        NEW.city := 'Unknown';
    END IF;

    -- Validate phone format (must be 10 digits)
    IF NEW.phone IS NOT NULL AND NEW.phone !~ '^d{10}$' THEN
        RAISE EXCEPTION 'Invalid phone format: %. Must be 10 digits.', NEW.phone
            USING ERRCODE = '22023';
    END IF;

    -- Set created_at if not provided
    NEW.created_at := COALESCE(NEW.created_at, CURRENT_TIMESTAMP);

    RETURN NEW;  -- Return modified NEW row — this is what gets stored
    -- If we returned NULL: the INSERT would be silently cancelled
END;
$$;

CREATE TRIGGER trg_normalise_customer
BEFORE INSERT ON customers
FOR EACH ROW
EXECUTE FUNCTION normalise_customer_before_insert();

-- TEST:
INSERT INTO customers (name, email, phone, city)
VALUES ('  rahul sharma  ', '  RAHUL@EMAIL.COM  ', '9876543210', NULL);
-- Stored as: ('Rahul Sharma', 'rahul@email.com', '9876543210', 'Unknown')

-- USE CASE: Complete audit trail — log every change to a sensitive table

-- Create audit table first:
CREATE TABLE orders_audit (
    audit_id       SERIAL        PRIMARY KEY,
    operation      CHAR(1)       NOT NULL CHECK (operation IN ('I', 'U', 'D')),
    -- I = Insert, U = Update, D = Delete
    order_id       INT,
    old_status     VARCHAR(20),
    new_status     VARCHAR(20),
    old_amount     DECIMAL(10,2),
    new_amount     DECIMAL(10,2),
    changed_by     TEXT,          -- database user
    changed_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    app_user_id    INT            -- application-level user (from session variable)
);

-- Trigger function:
CREATE OR REPLACE FUNCTION audit_orders()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_app_user_id INT;
BEGIN
    -- Get application user from session variable (set by app layer)
    BEGIN
        v_app_user_id := current_setting('app.current_user_id')::INT;
    EXCEPTION WHEN OTHERS THEN
        v_app_user_id := NULL;  -- gracefully handle if not set
    END;

    IF (TG_OP = 'INSERT') THEN
        INSERT INTO orders_audit(operation, order_id, new_status, new_amount, changed_by, app_user_id)
        VALUES ('I', NEW.order_id, NEW.status, NEW.total_amount, SESSION_USER, v_app_user_id);
        RETURN NEW;

    ELSIF (TG_OP = 'UPDATE') THEN
        -- Only log if something meaningful changed
        IF NEW.status IS DISTINCT FROM OLD.status
        OR NEW.total_amount IS DISTINCT FROM OLD.total_amount THEN
            INSERT INTO orders_audit(
                operation, order_id,
                old_status, new_status,
                old_amount, new_amount,
                changed_by, app_user_id
            )
            VALUES (
                'U', NEW.order_id,
                OLD.status, NEW.status,
                OLD.total_amount, NEW.total_amount,
                SESSION_USER, v_app_user_id
            );
        END IF;
        RETURN NEW;

    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO orders_audit(operation, order_id, old_status, old_amount, changed_by, app_user_id)
        VALUES ('D', OLD.order_id, OLD.status, OLD.total_amount, SESSION_USER, v_app_user_id);
        RETURN OLD;
    END IF;
END;
$$;

-- Create trigger for all three operations:
CREATE TRIGGER trg_audit_orders
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION audit_orders();

-- TG_OP: special variable containing 'INSERT', 'UPDATE', or 'DELETE'
-- NEW: the new row version (NULL for DELETE)
-- OLD: the old row version (NULL for INSERT)

-- USE CASE: When a restaurant's rating changes, update the denormalised
-- avg_rating column on the restaurants table after every review insert/update.

CREATE OR REPLACE FUNCTION update_restaurant_avg_rating()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_restaurant_id INT;
    v_new_avg       DECIMAL(3,2);
BEGIN
    -- Determine which restaurant is affected
    -- TG_OP tells us the operation type
    IF TG_OP = 'DELETE' THEN
        v_restaurant_id := OLD.restaurant_id;
    ELSE
        v_restaurant_id := NEW.restaurant_id;
    END IF;

    -- Compute the new average from all current reviews
    SELECT ROUND(AVG(rating)::NUMERIC, 2)
    INTO v_new_avg
    FROM reviews
    WHERE restaurant_id = v_restaurant_id;

    -- Update the denormalised column
    UPDATE restaurants
    SET avg_rating = COALESCE(v_new_avg, 0),
        rating_count = (SELECT COUNT(*) FROM reviews WHERE restaurant_id = v_restaurant_id)
    WHERE restaurant_id = v_restaurant_id;

    RETURN NULL;  -- AFTER triggers on row level: return value is ignored but convention is NULL
END;
$$;

CREATE TRIGGER trg_update_restaurant_rating
AFTER INSERT OR UPDATE OF rating OR DELETE ON reviews
FOR EACH ROW
EXECUTE FUNCTION update_restaurant_avg_rating();
-- "UPDATE OF rating": only fires when the rating column is specifically updated
-- (not when comment is updated without changing rating)

-- TEST:
INSERT INTO reviews (customer_id, restaurant_id, order_id, rating)
VALUES (1, 5, 1001, 4);
-- Trigger fires automatically → restaurants.avg_rating for restaurant 5 is updated

-- USE CASE: Instead of permanently deleting records, mark them as deleted.
-- Any application DELETE statement on customers is intercepted and converted.

CREATE OR REPLACE FUNCTION soft_delete_customer()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Instead of deleting, mark as deleted
    UPDATE customers
    SET
        is_active  = false,
        deleted_at = CURRENT_TIMESTAMP,
        deleted_by = SESSION_USER
    WHERE customer_id = OLD.customer_id;

    RETURN NULL;  -- Returning NULL from BEFORE trigger CANCELS the DELETE
    -- The actual DELETE never happens — only our UPDATE was executed
END;
$$;

CREATE TRIGGER trg_soft_delete_customer
BEFORE DELETE ON customers
FOR EACH ROW
EXECUTE FUNCTION soft_delete_customer();

-- Application code: DELETE FROM customers WHERE customer_id = 42
-- What actually happens:
-- 1. BEFORE DELETE trigger fires
-- 2. UPDATE customers SET is_active=false, deleted_at=now() WHERE customer_id=42
-- 3. Trigger returns NULL → DELETE is cancelled
-- 4. Customer row remains but is marked inactive

-- Companion: views that always exclude deleted records
CREATE VIEW active_customers_view AS
SELECT * FROM customers WHERE is_active = true AND deleted_at IS NULL;

-- FOR EACH ROW: fires once per affected row
-- FOR EACH STATEMENT: fires once per SQL statement regardless of rows affected

-- Statement-level trigger — log bulk operations:
CREATE OR REPLACE FUNCTION log_bulk_price_update()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO bulk_operation_log(operation, table_name, executed_by, executed_at)
    VALUES (TG_OP, TG_TABLE_NAME, SESSION_USER, CURRENT_TIMESTAMP);
    -- TG_TABLE_NAME: name of the table that fired the trigger
    -- Note: NEW and OLD are not available in statement-level triggers
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_log_menu_bulk_update
AFTER UPDATE ON menu_items
FOR EACH STATEMENT    -- fires ONCE even if 10,000 rows updated
EXECUTE FUNCTION log_bulk_price_update();

-- Comparison:
-- UPDATE menu_items SET price = price * 1.10 WHERE restaurant_id = 5;
-- Affects 47 rows.
-- FOR EACH ROW trigger: fires 47 times
-- FOR EACH STATEMENT trigger: fires 1 time

-- PERFORMANCE NOTE:
-- FOR EACH ROW: significant overhead for bulk operations (N trigger invocations)
-- FOR EACH STATEMENT: constant overhead regardless of rows affected
-- Use FOR EACH STATEMENT for audit-style logging of bulk operations
-- Use FOR EACH ROW when you need to access individual OLD/NEW values

-- ─────────────────────────────────────────────────────────────────
-- COLUMN-LEVEL SECURITY: hide sensitive columns via views
-- ─────────────────────────────────────────────────────────────────

-- Original table has sensitive columns:
CREATE TABLE employees (
    employee_id  INT PRIMARY KEY,
    name         VARCHAR(100),
    email        VARCHAR(150),
    salary       DECIMAL(12,2),   -- SENSITIVE
    pan_number   CHAR(10),        -- SENSITIVE
    department   VARCHAR(100),
    hire_date    DATE
);

-- Public view: excludes sensitive columns
CREATE VIEW employees_public AS
SELECT employee_id, name, email, department, hire_date
FROM employees;

-- Create role with access only to the view, NOT the base table:
CREATE ROLE hr_analyst;
GRANT SELECT ON employees_public TO hr_analyst;
-- hr_analyst can see employee_id, name, email, department, hire_date
-- hr_analyst CANNOT see salary or pan_number (those columns don't exist in the view)

-- Payroll role: needs salary but not PAN
CREATE VIEW employees_payroll AS
SELECT employee_id, name, department, salary
FROM employees;

CREATE ROLE payroll_processor;
GRANT SELECT ON employees_payroll TO payroll_processor;

-- ─────────────────────────────────────────────────────────────────
-- ROW-LEVEL SECURITY: each user sees only their own data
-- ─────────────────────────────────────────────────────────────────

-- Approach 1: View using session variable
CREATE VIEW my_orders AS
SELECT * FROM orders
WHERE customer_id = current_setting('app.user_id')::INT;
-- Application sets: SET LOCAL app.user_id = '42'; before queries

-- Approach 2: PostgreSQL Row Level Security (more robust)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY customer_sees_own_orders ON orders
    FOR SELECT
    USING (customer_id = current_setting('app.user_id')::INT);

CREATE POLICY customer_inserts_own_orders ON orders
    FOR INSERT
    WITH CHECK (customer_id = current_setting('app.user_id')::INT);
-- Now every SELECT on orders automatically adds the WHERE clause
-- No special view needed — enforced at the table level

-- ─────────────────────────────────────────────────────────────────
-- STORED PROCEDURE SECURITY: SECURITY DEFINER
-- ─────────────────────────────────────────────────────────────────

-- SECURITY DEFINER: procedure runs with the privileges of its creator
-- (not the caller). Like Unix setuid.
CREATE OR REPLACE FUNCTION get_my_salary(p_employee_id INT)
RETURNS DECIMAL(12,2)
LANGUAGE plpgsql
SECURITY DEFINER  -- runs with creator's privileges
AS $$
DECLARE
    v_salary DECIMAL(12,2);
    v_caller_id INT;
BEGIN
    -- Get caller's employee_id from session variable
    v_caller_id := current_setting('app.employee_id')::INT;

    -- Only allow employees to see their own salary
    IF v_caller_id != p_employee_id THEN
        RAISE EXCEPTION 'Access denied: you can only view your own salary';
    END IF;

    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;

    RETURN v_salary;
END;
$$;

-- Grant EXECUTE to all authenticated users:
GRANT EXECUTE ON FUNCTION get_my_salary(INT) TO authenticated_role;
-- authenticated_role has NO SELECT on employees table
-- But CAN call get_my_salary — which runs as the superuser-level creator
-- and performs the SELECT on their behalf, with application-level auth check