= Advanced Database Development

== Procedures

==== New User Free Points Gift

{{{#!div style="text-align: justify; width: 100%;"
* **Description:** This welcomes new shoppers. When a person makes a new account, this procedure checks that they are not making a fake double profile. Then, it automatically sets up their rewards page and gives them 50 free starter points to help them buy their first record.
}}}
* **Implementation:**  

==== Address and Phone Number Verifier

{{{#!div style="text-align: justify; width: 100%;"
* **Description:** This procedure checks shipping info before orders are processed. It automatically scans user profiles and makes sure that the shipping addresses and phone numbers are not blank or broken. If an address is missing, it stops the order and flags it for a manual fix so the package does not get lost.
}}}
* **Implementation:**  

==== "HOT" Item Flagging

{{{#!div style="text-align: justify; width: 100%;"
* **Description:** This procedure watches how fast items sell. If a certain vinyl or CD suddenly sells a lot of copies (for example, more than 50 in a single week), it automatically flags it as a "HOT" item. It creates a note for the admins so they can put it on the front page of the website.
}}}
* **Implementation:**  

== Triggers

==== Double Promo Blocker

{{{#!div style="text-align: justify; width: 100%;"
* **Description:** This keeps customers from using multiple promotions in a single order. If someone tries to buy a product that is already on sale, this trigger stops them from using their store loyalty points on that same order. It forces the system to only allow one discount type at a time so the store doesn't lose money.
}}}
* **Implementation:**

{{{#!div style="text-align: justify; width: 100%;"
  First, we implement a trigger function that checks if an order combines loyalty points with a discounted product and blocks it if it does. 
}}}

{{{
CREATE OR REPLACE FUNCTION project.block_double_promo()
RETURNS TRIGGER AS $$
DECLARE
    used_points INTEGER;
    discounted_product_exists BOOLEAN;
BEGIN
    SELECT o.points_used
    INTO used_points
    FROM project.orders o
    WHERE o.order_id = NEW.order_id;

    SELECT EXISTS (
        SELECT 1
        FROM project.modification_products mp
        JOIN project.modifications m
            ON m.modification_id = mp.modification_id
        WHERE mp.product_id = NEW.product_id
          AND m.type_of_modification = 'DISCOUNT'
          AND m.discount IS NOT NULL
    )
    INTO discounted_product_exists;

    IF used_points > 0 AND discounted_product_exists THEN
        RAISE EXCEPTION
            'Double promotion is not allowed: loyalty points cannot be used on an order containing discounted products.';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
}}}

{{{#!div style="text-align: justify; width: 100%;"
  We also create a trigger that runs when a product is added to an order.
}}}

{{{
DROP TRIGGER IF EXISTS trg_block_double_promo_order_products
ON project.order_products;

CREATE TRIGGER trg_block_double_promo_order_products
BEFORE INSERT OR UPDATE ON project.order_products
FOR EACH ROW
EXECUTE FUNCTION project.block_double_promo();
}}}

==== Price Drop Alert

{{{#!div style="text-align: justify; width: 100%;"
* **Description:** When a product manager drops the price of a product, this trigger would notify users that have that same product in their wishlists. It scans the wishlists of all users to see who has been eyeing that specific item. For every enthusiast found, it automatically writes a notification record into the system tables so the application knows exactly who to alert or email about the price cut.
}}}
* **Implementation:**  

{{{#!div style="text-align: justify; width: 100%;"
  First, we need to create a table that will be used to store information about users who should be notified when a product's price decreases.
}}}

{{{
DROP TABLE IF EXISTS project.price_drop_notifications;

CREATE TABLE project.price_drop_notifications (
    notification_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES project.users(user_id) ON DELETE CASCADE,
    user_email VARCHAR(255) NOT NULL,
    product_id BIGINT NOT NULL REFERENCES project.products(product_id) ON DELETE CASCADE,
    old_price NUMERIC NOT NULL,
    new_price NUMERIC NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    is_email_sent BOOLEAN NOT NULL DEFAULT FALSE
);
}}}

{{{#!div style="text-align: justify; width: 100%;"
  Then, we need to create a trigger function that defines the logic that automatically identifies interested users and creates notification records whenever a product becomes cheaper.
}}}

{{{
CREATE OR REPLACE FUNCTION project.create_price_drop_notifications()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO project.price_drop_notifications
    (
        user_id,
        user_email,
        product_id,
        old_price,
        new_price,
        message
    )
    SELECT
        u.user_id,
        u.email,
        NEW.product_id,
        OLD.price,
        NEW.price,
        'Price dropped for product: ' || NEW.product_description ||
        '. Old price: ' || OLD.price ||
        ', new price: ' || NEW.price || '.'
    FROM project.wishlists w
    JOIN project.users u
        ON u.user_id = w.user_id
    JOIN project.wishlist_products wp
        ON wp.wishlist_id = w.wishlist_id
    WHERE wp.product_id = NEW.product_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
}}}

{{{#!div style="text-align: justify; width: 100%;"
  Finally, the trigger attaches the notification logic to the products table so it executes automatically after a price reduction occurs.
}}}

{{{
DROP TRIGGER IF EXISTS trg_price_drop_alert ON project.products;

CREATE TRIGGER trg_price_drop_alert
AFTER UPDATE OF price ON project.products
FOR EACH ROW
WHEN (NEW.price < OLD.price)
EXECUTE FUNCTION project.create_price_drop_notifications();
}}}

== Views

==== Detailed View of a Release

{{{#!div style="text-align: justify; width: 100%;"
* **Description:** Combines the entire metadata of a music release into one detailed overview. For any given release, it aggregates its main artists, features, tracks, total track duration, and provides a real-time summary breakdown of available warehouse stock and pricing for all physical formats simultaneously.
}}}
* **Implementation:

{{{
DROP VIEW IF EXISTS project.detailed_release_view;

CREATE VIEW project.detailed_release_view AS
WITH main_artists AS (
    SELECT
        ra.release_id,
        string_agg(a.artist_name, ', ' ORDER BY ra.release_ordinal) AS main_artists
    FROM project.release_artists ra
    JOIN project.artists a ON a.artist_id = ra.artist_id
    WHERE ra.type = 'MAIN'
    GROUP BY ra.release_id
),
album_tracks AS (
    SELECT
        al.release_id,
        jsonb_agg(
            jsonb_build_object(
                'song_name', s.song_name,
                'duration', s.song_duration,
                'features', COALESCE(sf.features, ' ')
            )
            ORDER BY s.song_id
        ) AS tracks,
        SUM(
            split_part(s.song_duration, ':', 1)::INT * INTERVAL '1 minute'
            + split_part(s.song_duration, ':', 2)::INT * INTERVAL '1 second'
        ) AS total_duration
    FROM project.albums al
    JOIN project.album_songs als ON als.album_id = al.release_id
    JOIN project.songs s ON s.song_id = als.song_id
    LEFT JOIN (
        SELECT
            sa.song_id,
            string_agg(a.artist_name, ', ' ORDER BY sa.song_ordinal) AS features
        FROM project.song_artists sa
        JOIN project.artists a ON a.artist_id = sa.artist_id
        WHERE sa.song_ordinal > 1
        GROUP BY sa.song_id
    ) sf ON sf.song_id = s.song_id
    GROUP BY al.release_id
),
single_tracks AS (
    SELECT
        sr.release_id,
        jsonb_build_array(
            jsonb_build_object(
                'song_name', r.title,
                'duration', sr.duration,
                'features', COALESCE(sf.features, 'No features')
            )
        ) AS tracks,
        split_part(sr.duration, ':', 1)::INT * INTERVAL '1 minute'
        + split_part(sr.duration, ':', 2)::INT * INTERVAL '1 second' AS total_duration
    FROM project.single_releases sr
    JOIN project.releases r ON r.release_id = sr.release_id
    LEFT JOIN (
        SELECT
            ra.release_id,
            string_agg(a.artist_name, ', ' ORDER BY ra.release_ordinal) AS features
        FROM project.release_artists ra
        JOIN project.artists a ON a.artist_id = ra.artist_id
        WHERE ra.type = 'FEATURE'
        GROUP BY ra.release_id
    ) sf ON sf.release_id = sr.release_id
),
product_summary AS (
    SELECT
        p.release_id,
        jsonb_agg(
            jsonb_build_object(
                'format', p.format,
                'price', p.price,
                'stock', p.stock,
                'description', p.product_description
            )
            ORDER BY p.format
        ) AS physical_formats
    FROM project.products p
    GROUP BY p.release_id
)
SELECT
    r.title,
    r.record_label,
    r.genre,
    r.release_date,
    r.cover_photo,
    CASE
        WHEN al.release_id IS NOT NULL THEN 'ALBUM'
        ELSE 'SINGLE'
    END AS release_type,
    ma.main_artists,
    COALESCE(at.tracks, st.tracks) AS tracks,
    COALESCE(at.total_duration, st.total_duration) AS total_track_duration,
    ps.physical_formats
FROM project.releases r
LEFT JOIN project.albums al ON al.release_id = r.release_id
LEFT JOIN main_artists ma ON ma.release_id = r.release_id
LEFT JOIN album_tracks at ON at.release_id = r.release_id
LEFT JOIN single_tracks st ON st.release_id = r.release_id
LEFT JOIN product_summary ps ON ps.release_id = r.release_id;
}}}

==== Top 10 Highest Selling Products

{{{#!div style="text-align: justify; width: 100%;"
* **Description:** A detailed analytical view that uses a the previous 7-day period to show the Top 10 best-selling products. It combines data from multiple tables, calculates the total quantity sold, excludes cancelled orders, and sorts the products from highest to lowest selling.
}}}
* **Implementation:**

{{{
DROP VIEW IF EXISTS project.top_10_highest_selling_products;

CREATE VIEW project.top_10_highest_selling_products AS
SELECT
    p.format,
    p.product_description,
    r.title AS release_title,
    r.genre,
    r.record_label,
    r.release_date,
    SUM(op.quantity) AS total_quantity_sold,
    SUM(op.quantity * op.price_at_purchase) AS total_revenue
FROM project.order_products op
JOIN project.orders o ON o.order_id = op.order_id
JOIN project.products p ON p.product_id = op.product_id
JOIN project.releases r ON r.release_id = p.release_id
WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '7 days'
  AND o.purchase_date < CURRENT_DATE
  AND o.status <> 'CANCELLED'
GROUP BY
    p.product_id,
    p.format,
    p.product_description,
    r.title,
    r.genre,
    r.record_label,
    r.release_date
ORDER BY total_quantity_sold DESC, total_revenue DESC
LIMIT 10;
}}}