= Phase P7: Advanced Database Development (SQL DDL) =
This phase introduces advanced database mechanisms that help enforce business rules directly at database level. 
In our Wedding Planner database (PostgreSQL via DBeaver), we use indexes, constraints, triggers and views to improve performance, prevent invalid data and automate important logic.

== Overview ==
In this phase we implement:
 * Indexes – faster searching/filtering in large tables
 * Constraints – enforce validity and prevent inconsistent records
 * Triggers – automate rules (price calculation, venue capacity control)
 * Views – simplify reporting queries and provide reusable reports

== What we cover in this phase ==
 * Indexes for frequent query patterns (events, bookings, RSVP)
 * Constraints for data validation + preventing overlapping bookings
 * Triggers to automate derived values (venue price) and enforce capacity
 * Views for simplified summaries (RSVP counts, confirmed bookings, guest counts)

----

== Indexes (Performance Improvement) ==

=== Index: Event Date and Time Search Optimization ===
This improves performance when searching or filtering events by date and time interval.

*Tables affected:* `event`  
*Index type:* B-tree composite indexes

{{{
CREATE INDEX idx_event_date_start ON event("date", start_time);
CREATE INDEX idx_event_date_end   ON event("date", end_time);
}}}

*Explanation:*  
The indexes store pre-sorted combinations of (`date`, `start_time`) and (`date`, `end_time`), which allows fast retrieval of events for a specific day/time window without scanning the entire table.

---

=== Index: Booking Status Filtering ===
These indexes optimize availability checks and booking filtering by date and status.

*Tables affected:* `venue_booking`, `photographer_booking`, `band_booking`  
*Index type:* B-tree composite indexes

{{{
CREATE INDEX idx_venue_booking_status        ON venue_booking("date", status);
CREATE INDEX idx_photographer_booking_status ON photographer_booking("date", status);
CREATE INDEX idx_band_booking_status         ON band_booking("date", status);
}}}

*Explanation:*  
Booking records are organized by (`date`, `status`), allowing fast retrieval of rows such as: “confirmed bookings on a given date”.

---

=== Index: RSVP Status Filtering ===
This index speeds up reports of RSVP responses per event.

*Table affected:* `event_rsvp`  
*Index type:* Composite B-tree index

{{{
CREATE INDEX idx_rsvp_event_status ON event_rsvp (event_id, status);
}}}

*Explanation:*  
RSVP rows are grouped by event and status, so queries like “all accepted guests for event X” are executed efficiently.

----

== Constraints (Data Integrity) ==

=== Constraint: Prevent Overlapping Bookings ===
This prevents double-booking of the same venue/photographer/band at overlapping time intervals.

To support this constraint, we use:
 * `btree_gist` extension
 * generated timestamp columns (`start_ts`, `end_ts`)
 * GiST exclusion constraints

{{{
CREATE EXTENSION IF NOT EXISTS btree_gist;
}}}

*Generated timestamps (date + time):*
{{{
ALTER TABLE venue_booking
ADD COLUMN start_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + start_time) STORED,
ADD COLUMN end_ts   TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + end_time) STORED;

ALTER TABLE photographer_booking
ADD COLUMN start_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + start_time) STORED,
ADD COLUMN end_ts   TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + end_time) STORED;

ALTER TABLE band_booking
ADD COLUMN start_ts TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + start_time) STORED,
ADD COLUMN end_ts   TIMESTAMP NOT NULL GENERATED ALWAYS AS ("date" + end_time) STORED;
}}}

*Exclude overlap constraints:*
{{{
ALTER TABLE venue_booking
ADD CONSTRAINT exclude_overlap_vb EXCLUDE USING gist (
    venue_id WITH =,
    tsrange(start_ts, end_ts) WITH &&
);

ALTER TABLE photographer_booking
ADD CONSTRAINT exclude_overlap_pb EXCLUDE USING gist (
    photographer_id WITH =,
    tsrange(start_ts, end_ts) WITH &&
);

ALTER TABLE band_booking
ADD CONSTRAINT exclude_overlap_bb EXCLUDE USING gist (
    band_id WITH =,
    tsrange(start_ts, end_ts) WITH &&
);
}}}

*Explanation:*  
The database rejects inserting a booking if the interval overlaps with another booking for the same resource (venue/band/photographer).

---

=== Constraint: Status and Value Validation ===
We prevent invalid values for status/role/budget using CHECK constraints.

{{{
ALTER TABLE event
ADD CONSTRAINT chk_event_status
CHECK (status IN ('scheduled', 'confirmed', 'cancelled'));

ALTER TABLE event_rsvp
ADD CONSTRAINT chk_event_rsvp_status
CHECK (status IN ('accepted', 'declined', 'pending'));

ALTER TABLE venue_booking
ADD CONSTRAINT chk_venue_booking_status
CHECK (status IN ('pending', 'confirmed', 'cancelled'));

ALTER TABLE photographer_booking
ADD CONSTRAINT chk_photographer_booking_status
CHECK (status IN ('pending', 'confirmed', 'cancelled'));

ALTER TABLE band_booking
ADD CONSTRAINT chk_band_booking_status
CHECK (status IN ('pending', 'confirmed', 'cancelled'));

ALTER TABLE attendance
ADD CONSTRAINT chk_attendance_status
CHECK (status IN ('attending', 'not_attending'));

ALTER TABLE wedding
ADD CONSTRAINT chk_budget
CHECK (budget > 0);

ALTER TABLE attendance
ADD CONSTRAINT chk_att_role
CHECK (role IN ('Guest', 'Bride', 'Groom', 'Best Man', 'Maid of Honor'));
}}}

*Explanation:*  
If an invalid value is inserted or updated, PostgreSQL rejects the operation and keeps the database consistent.

== Triggers (Automation of Business Logic) ==

=== Trigger: Automatic Venue Price Calculation ===
This trigger calculates the total venue booking price based on:
 * venue price per guest
 * current number of guests for that wedding

*Tables involved:* `venue_booking`, `venue`, `guest`  
*Trigger type:* BEFORE INSERT on `venue_booking`

{{{
CREATE OR REPLACE FUNCTION calc_venue_price()
RETURNS trigger AS $$
BEGIN
    IF NEW.price IS NULL THEN
        SELECT price_per_guest * COUNT(*)
        INTO NEW.price
        FROM venue v
        JOIN guest g ON g.wedding_id = NEW.wedding_id
        WHERE v.venue_id = NEW.venue_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_calc_venue_price
BEFORE INSERT ON venue_booking
FOR EACH ROW
EXECUTE FUNCTION calc_venue_price();
}}}

*Reason for use:*  
This ensures price is always correct and prevents manual calculation errors inside the application.

---

=== Trigger: Venue Capacity Enforcement ===
This trigger prevents adding guests if the confirmed venue capacity will be exceeded.

*Tables involved:* `guest`, `venue_booking`, `venue`  
*Trigger type:* BEFORE INSERT OR UPDATE on `guest`

{{{
CREATE OR REPLACE FUNCTION check_guest_capacity()
RETURNS trigger AS $$
DECLARE
    guest_count INTEGER;
    venue_cap   INTEGER;
BEGIN
    SELECT v.capacity
    INTO venue_cap
    FROM venue_booking vb
    JOIN venue v ON v.venue_id = vb.venue_id
    WHERE vb.wedding_id = NEW.wedding_id
      AND vb.status = 'confirmed';

    IF venue_cap IS NULL THEN
        RETURN NEW;
    END IF;

    SELECT COUNT(*) INTO guest_count
    FROM guest
    WHERE wedding_id = NEW.wedding_id
      AND (TG_OP = 'INSERT' OR guest_id <> OLD.guest_id);

    IF guest_count + 1 > venue_cap THEN
        RAISE EXCEPTION 'Cannot add guest: venue capacity (%) exceeded', venue_cap;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_guest_capacity
BEFORE INSERT OR UPDATE ON guest
FOR EACH ROW
EXECUTE FUNCTION check_guest_capacity();
}}}

*Reason for use:*  
This enforces a real-world rule: guest count cannot exceed venue capacity.

----

== Views (Simplified Reports) ==

=== View: RSVP summary per event ===
Shows RSVP counts per status for each event.

{{{
CREATE VIEW vw_event_rsvp_summary AS
SELECT
    event_id,
    SUM(CASE WHEN status = 'accepted' THEN 1 ELSE 0 END) AS yes_count,
    SUM(CASE WHEN status = 'declined' THEN 1 ELSE 0 END) AS no_count,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS maybe_count
FROM event_rsvp
GROUP BY event_id;
}}}

---

=== View: Confirmed bookings by date (all types) ===
A unified report for all confirmed bookings in one result.

{{{
CREATE VIEW vw_confirmed_bookings_by_date AS
SELECT
    "date",
    'VENUE' AS booking_type,
    venue_id AS resource_id,
    start_time,
    end_time
FROM venue_booking
WHERE status = 'confirmed'

UNION ALL

SELECT
    "date",
    'PHOTOGRAPHER' AS booking_type,
    photographer_id AS resource_id,
    start_time,
    end_time
FROM photographer_booking
WHERE status = 'confirmed'

UNION ALL

SELECT
    "date",
    'BAND' AS booking_type,
    band_id AS resource_id,
    start_time,
    end_time
FROM band_booking
WHERE status = 'confirmed';
}}}

---

=== View: Guest count per wedding ===
A reusable summary of how many guests exist for each wedding.

{{{
CREATE VIEW vw_wedding_guest_count AS
SELECT
    wedding_id,
    COUNT(*) AS total_guests
FROM guest
GROUP BY wedding_id;
}}}

----

== Conclusion ==
In this phase we implemented advanced database mechanisms that improve scalability and correctness of our Wedding Planner system.
Indexes help queries run faster as the number of bookings/events grows.
Constraints enforce strict data rules and prevent invalid states, such as double-booking.
Triggers automate derived values and business rules like venue capacity control.
Finally, views provide reusable reports and simplify querying for common summaries.