CREATE OR REPLACE FUNCTION fn_customer_status(status TEXT)
RETURNS TEXT 
LANGUAGE plpgsql 
AS $$ 
BEGIN
    IF status = 'Y' THEN 
        RETURN 'Active'; 
    ELSEIF status = 'N' THEN
        RETURN 'Inactive'; 
    ELSE 
        RETURN 'unknown'; 
    END IF; 
END; 
$$; 

CREATE OR REPLACE FUNCTION fn_days_since_last_purchase(last_update TIMESTAMP)
RETURNS INT 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
RETURN (CURRENT_DATE - last_update::DATE)::INT; 
END; 
$$; 


SELECT
    c.customer_id, 
    c.first_name, 
    c.last_update, 
    fn_customer_status( 
        CASE WHEN c.active = 1 THEN 'Y' ELSE 'N' END
    ) AS customer_status
FROM public.customer AS c; 


--Detailtable 
CREATE TABLE customer_activity_detail( 
customer_id INT, 
    first_name VARCHAR(50), 
    last_name VARCHAR(50), 
    store_id INT, 
    inventory_id INT, 
    film_id INT, 
    city_id INT, 
    last_update TIMESTAMP, 
    customer_status VARCHAR(50), 
    days_since_last_update INT 
    );

INSERT INTO customer_activity_detail( 
customer_id, 
    first_name, 
    last_name, 
    store_id, 
    inventory_id, 
    film_id, 
    city_id, 
    last_update, 
    customer_status, 
    days_since_last_update
    )


    SELECT 
    c.customer_id, 
    c.first_name, 
    s.store_id, 
    i.inventory_id, 
    i.film_id, 
    ci.city_id, 
    c.last_update, 
    fn_customer_status(c.active::TEXT) AS customer_status,
    fn_days_since_last_purchase(c.last_update) AS days_since_last_update 

    FROM customer c 
    JOIN store s ON c.store_id = s.store_id 
    JOIN inventory i ON i.store_id = s.store_id
    JOIN address a ON s.address_id = a.address_id
    JOIN city ci ON a.city_id = ci.city_id; 

-- Summary table 
    CREATE TABLE IF NOT EXISTS customer_activity_summary( 
        customer_status,
        total_customers, 
        avg_days_since_last_update
    )

SELECT 
    d.customer_status, 
    COUNT(*) AS total_customers, 
    LEAST (999.99, GREATEST (0, AVG(d.days_since_last_update)))::NUMERIC(5,2)
        AS avg_days_since_last_update 
FROM customer_activity_detail AS d
GROUP BY d.customer_status; 


CREATE OR REPLACE FUNCTION fn_update_summary_after_insert()
RETURNS TRIGGER 
LANGUAGE plpgsql 
AS $$ 
DECLARE
    v_status TEXT := NEW.customer_status; 
    v_days INT := NEW_days_since_last_update; 
BEGIN 
    INSERT INTO customer_activity_summary( 
    customer_status, 
    total_customers, 
    avg_days_since_last_update
    )
    VALUES (v_status, 1, v_days)
    ON CONFLICT(customer_status)
    DO UPDATE SET 
        total_customers = customer_activity_summary.total_customers + 1, 
        avg_days_since_last_update = 
        ( 
            (customer_activity_summary.avg_days_since_last_update * customer_activity_summary.total_customers)
        + v_days
        ) / (customers_activity_summary_total_customers + 1);
            RETURN NEW; 
    END; 
    $$; 

-- Trigger on detail table 
DROP TRIGGER IF EXISTS trg_update_summary_after_insert ON customer_activity_detail; 

CREATE TRIGGER trg_update_summary_after_insert 
AFTER INSERT ON customer_activity_detail 
FOR EACH ROW 
EXECUTE FUNCTION fn_update_summary_after_insert(); 

-- Stored Procedure
CREATE OR REPLACE PROCEDURE sp_refresh_customer_activity()
LANGUAGE plpgsql 
AS $$ 
BEGIN 
    TRUNCATE TABLE customer_activity_detail; 
    TRUNCATE TABLE customer_activity_summary; 

    INSERT INTO customer_activity_detail( 
    customer_id, 
        first_name, 
        last_name, 
        store_id, 
        inventory_id, 
        film_id, 
        city_id, 
        last_update, 
        customer_status, 
        days_since_last_update
    )

SELECT 
    c.customer_id, 
    c.first_name, 
    s.store_id, 
    i.inventory_id, 
    i.film_id, 
    ci.city_id, 
    c.last_update, 
    fn_customer_status(c.active), 
    fn_days_since_last_purchase(c.last_update)
FROM customer c 
JOIN store s ON c.store_id = s.store_id 
JOIN inventory i ON i.store_id = s.store_id 
JOIN address a ON s.address_id = a.address_id 
JOIN city ci ON a.city_id = ci.city_id; 
END; 
$$;