DROP TABLE IF EXISTS review CASCADE;
DROP TABLE IF EXISTS appointmentpackageusage CASCADE;
DROP TABLE IF EXISTS appointmentservice CASCADE;
DROP TABLE IF EXISTS payment CASCADE;
DROP TABLE IF EXISTS userpackagepurchase CASCADE;
DROP TABLE IF EXISTS packageservice CASCADE;
DROP TABLE IF EXISTS loyaltycard CASCADE;
DROP TABLE IF EXISTS appointment CASCADE;
DROP TABLE IF EXISTS service CASCADE;
DROP TABLE IF EXISTS package CASCADE;
DROP TABLE IF EXISTS category CASCADE;
DROP TABLE IF EXISTS userrole CASCADE;
DROP TABLE IF EXISTS role CASCADE;
DROP TABLE IF EXISTS status CASCADE;
DROP TABLE IF EXISTS availability CASCADE;
DROP TABLE IF EXISTS "User" CASCADE;

DROP TYPE IF EXISTS appointment_type CASCADE;

CREATE TYPE appointment_type AS ENUM ('pre-booked', 'walk-in');

CREATE TABLE "User" (
                        user_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                        full_name varchar NOT NULL,
                        email varchar NOT NULL,
                        phone varchar NOT NULL,
                        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        password_hash text NOT NULL,
                        CONSTRAINT user_email_key UNIQUE (email)
);

CREATE TABLE role (
                      role_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                      name varchar NOT NULL,
                      CONSTRAINT role_name_key UNIQUE (name)
);

CREATE TABLE category (
                          category_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                          name varchar NOT NULL,
                          CONSTRAINT category_name_key UNIQUE (name)
);

CREATE TABLE status (
                        status_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                        name varchar NOT NULL,
                        CONSTRAINT status_name_key UNIQUE (name)
);

CREATE TABLE package (
                         package_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                         name varchar NOT NULL,
                         max_usage integer NOT NULL,
                         total_price numeric NOT NULL DEFAULT 0,
                         CONSTRAINT package_max_usage_check CHECK (max_usage > 0),
                         CONSTRAINT package_total_price_nonnegative CHECK (total_price >= 0)
);

CREATE TABLE service (
                         service_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                         name varchar NOT NULL,
                         price numeric NOT NULL,
                         duration_minutes integer NOT NULL,
                         category_id integer NOT NULL,
                         CONSTRAINT fk_service_category
                             FOREIGN KEY (category_id) REFERENCES category(category_id)
                                 ON DELETE RESTRICT,
                         CONSTRAINT service_price_check CHECK (price >= 0),
                         CONSTRAINT service_duration_minutes_check CHECK (duration_minutes > 0)
);

CREATE TABLE appointment (
                             appointment_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                             appointment_time timestamp NOT NULL,
                             end_time timestamp NOT NULL,
                             notes text,
                             type appointment_type NOT NULL,
                             total_price numeric NOT NULL,
                             user_id integer NOT NULL,
                             status_id integer NOT NULL,
                             points_awarded boolean NOT NULL DEFAULT false,
                             CONSTRAINT fk_appointment_user
                                 FOREIGN KEY (user_id) REFERENCES "User"(user_id)
                                     ON DELETE CASCADE,
                             CONSTRAINT fk_appointment_status
                                 FOREIGN KEY (status_id) REFERENCES status(status_id)
                                     ON DELETE RESTRICT,
                             CONSTRAINT chk_appointment_times CHECK (end_time > appointment_time),
                             CONSTRAINT appointment_total_price_check CHECK (total_price >= 0)
);

CREATE TABLE loyaltycard (
                             card_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                             points integer NOT NULL DEFAULT 0,
                             user_id integer NOT NULL,
                             CONSTRAINT fk_loyaltycard_user
                                 FOREIGN KEY (user_id) REFERENCES "User"(user_id)
                                     ON DELETE CASCADE,
                             CONSTRAINT loyaltycard_user_id_key UNIQUE (user_id),
                             CONSTRAINT loyaltycard_points_nonnegative CHECK (points >= 0)
);

CREATE TABLE userrole (
                          user_id integer NOT NULL,
                          role_id integer NOT NULL,
                          CONSTRAINT userrole_pkey PRIMARY KEY (user_id, role_id),
                          CONSTRAINT fk_userrole_user
                              FOREIGN KEY (user_id) REFERENCES "User"(user_id)
                                  ON DELETE CASCADE,
                          CONSTRAINT fk_userrole_role
                              FOREIGN KEY (role_id) REFERENCES role(role_id)
                                  ON DELETE CASCADE
);

CREATE TABLE packageservice (
                                package_id integer NOT NULL,
                                service_id integer NOT NULL,
                                discounted_price numeric NOT NULL,
                                CONSTRAINT packageservice_pkey PRIMARY KEY (package_id, service_id),
                                CONSTRAINT fk_packageservice_package
                                    FOREIGN KEY (package_id) REFERENCES package(package_id)
                                        ON DELETE CASCADE,
                                CONSTRAINT fk_packageservice_service
                                    FOREIGN KEY (service_id) REFERENCES service(service_id)
                                        ON DELETE CASCADE,
                                CONSTRAINT packageservice_discounted_price_check CHECK (discounted_price >= 0)
);

CREATE TABLE userpackagepurchase (
                                     purchase_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                                     user_id integer NOT NULL,
                                     package_id integer NOT NULL,
                                     purchased_at timestamp NOT NULL DEFAULT now(),
                                     total_uses integer NOT NULL,
                                     remaining_uses integer NOT NULL,
                                     status text NOT NULL,
                                     expires_at date,
                                     CONSTRAINT userpackagepurchase_user_id_fkey
                                         FOREIGN KEY (user_id) REFERENCES "User"(user_id)
                                             ON DELETE CASCADE,
                                     CONSTRAINT userpackagepurchase_package_id_fkey
                                         FOREIGN KEY (package_id) REFERENCES package(package_id)
                                             ON DELETE RESTRICT,
                                     CONSTRAINT userpackagepurchase_status_check
                                         CHECK (status IN ('ACTIVE', 'EXPIRED', 'CANCELLED')),
                                     CONSTRAINT userpackagepurchase_uses_check
                                         CHECK (total_uses >= 0 AND remaining_uses >= 0)
);

CREATE TABLE appointmentservice (
                                    appointment_id integer NOT NULL,
                                    service_id integer NOT NULL,
                                    CONSTRAINT appointmentservice_pkey PRIMARY KEY (appointment_id, service_id),
                                    CONSTRAINT fk_appointmentservice_appointment
                                        FOREIGN KEY (appointment_id) REFERENCES appointment(appointment_id)
                                            ON DELETE CASCADE,
                                    CONSTRAINT fk_appointmentservice_service
                                        FOREIGN KEY (service_id) REFERENCES service(service_id)
                                            ON DELETE CASCADE
);

CREATE TABLE appointmentpackageusage (
                                         appointment_id integer PRIMARY KEY,
                                         purchase_id integer NOT NULL,
                                         service_id integer NOT NULL,
                                         used_units integer NOT NULL DEFAULT 1,
                                         finalized_at timestamp NULL,
                                         CONSTRAINT appointmentpackageusage_appointment_id_fkey
                                             FOREIGN KEY (appointment_id) REFERENCES appointment(appointment_id)
                                                 ON DELETE CASCADE,
                                         CONSTRAINT appointmentpackageusage_purchase_id_fkey
                                             FOREIGN KEY (purchase_id) REFERENCES userpackagepurchase(purchase_id)
                                                 ON DELETE RESTRICT,
                                         CONSTRAINT appointmentpackageusage_service_id_fkey
                                             FOREIGN KEY (service_id) REFERENCES service(service_id)
                                                 ON DELETE RESTRICT,
                                         CONSTRAINT appointmentpackageusage_used_units_check CHECK (used_units > 0)
);

CREATE TABLE payment (
                         payment_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                         amount numeric NOT NULL,
                         method varchar NOT NULL,
                         "timestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         status varchar NOT NULL,
                         appointment_id integer NULL,
                         points_used integer NOT NULL DEFAULT 0,
                         package_purchase_id integer NULL,
                         CONSTRAINT fk_payment_appointment
                             FOREIGN KEY (appointment_id) REFERENCES appointment(appointment_id)
                                 ON DELETE CASCADE,
                         CONSTRAINT fk_payment_package_purchase
                             FOREIGN KEY (package_purchase_id) REFERENCES userpackagepurchase(purchase_id)
                                 ON DELETE SET NULL,
                         CONSTRAINT payment_amount_check CHECK (amount >= 0),
                         CONSTRAINT payment_points_used_nonnegative CHECK (points_used >= 0),
                         CONSTRAINT payment_status_allowed CHECK (status IN ('PENDING', 'PAID', 'FAILED', 'REFUNDED')),
                         CONSTRAINT payment_target_required CHECK (appointment_id IS NOT NULL OR package_purchase_id IS NOT NULL)
);

CREATE TABLE review (
                        review_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                        rating integer NOT NULL,
                        comment text,
                        created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                        payment_id integer NOT NULL,
                        CONSTRAINT fk_review_payment
                            FOREIGN KEY (payment_id) REFERENCES payment(payment_id)
                                ON DELETE CASCADE,
                        CONSTRAINT review_rating_range CHECK (rating >= 1 AND rating <= 5),
                        CONSTRAINT review_unique_payment UNIQUE (payment_id)
);

CREATE TABLE availability (
                              availability_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                              date date NOT NULL,
                              start_time time NOT NULL,
                              end_time time NOT NULL,
                              is_closed boolean NOT NULL DEFAULT false,
                              CONSTRAINT availability_end_after_start CHECK (end_time > start_time)
);

CREATE UNIQUE INDEX idx_payment_paid_unique
    ON payment (appointment_id)
    WHERE status = 'PAID';

CREATE UNIQUE INDEX idx_payment_package_purchase_paid_unique
    ON payment (package_purchase_id)
    WHERE status = 'PAID' AND appointment_id IS NULL;

CREATE UNIQUE INDEX idx_availability_unique_window
    ON availability (date, start_time, end_time);



