-- CREATE TABLES

CREATE TABLE public.boxes (
    box_id integer NOT NULL,
    box_name character varying(10) NOT NULL,
    size character varying(1) NOT NULL,
    location character varying(50) NOT NULL,
    price_per_day numeric(10,2) NOT NULL,
    price_per_hour numeric(10,2) NOT NULL,
    status character varying(10) NOT NULL,
    description character varying(255),
    type_reservation character varying(10) DEFAULT 'walkin'::character varying NOT NULL,
    CONSTRAINT boxes_size_check CHECK (size = ANY (ARRAY['S','M','L'])),
    CONSTRAINT boxes_status_check CHECK (status = ANY (ARRAY['free','occupied'])),
    CONSTRAINT type_reservation_check CHECK (type_reservation = ANY (ARRAY['walkin','daily']))
);

ALTER TABLE public.boxes ALTER COLUMN box_id ADD GENERATED BY DEFAULT AS IDENTITY;
ALTER TABLE ONLY public.boxes ADD CONSTRAINT boxes_pkey PRIMARY KEY (box_id);

CREATE TABLE public.messages (
    message_id integer NOT NULL,
    name character varying(30),
    email character varying(50),
    message text,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE ONLY public.messages ADD CONSTRAINT messages_pkey PRIMARY KEY (message_id);

CREATE TABLE public.payments (
    payment_id integer NOT NULL,
    reservation_id integer NOT NULL,
    amount numeric(10,2) NOT NULL,
    payment_date timestamp without time zone NOT NULL,
    payment_method character varying(20) NOT NULL,
    payment_status character varying(20) NOT NULL,
    CONSTRAINT payments_payment_method_check CHECK (payment_method = ANY (ARRAY['transfer','card'])),
    CONSTRAINT payments_payment_status_check CHECK (payment_status = ANY (ARRAY['pending','paid','cancelled']))
);

ALTER TABLE ONLY public.payments ADD CONSTRAINT payments_pkey PRIMARY KEY (payment_id);

CREATE TABLE public.reservation_boxes (
    reservation_id integer NOT NULL,
    box_id integer NOT NULL
);

ALTER TABLE ONLY public.reservation_boxes ADD CONSTRAINT reservation_boxes_pkey PRIMARY KEY (reservation_id, box_id);

CREATE TABLE public.reservations (
    reservation_id integer NOT NULL,
    users_user_id integer NOT NULL,
    total_price numeric(10,2) NOT NULL,
    reservation_date timestamp without time zone NOT NULL,
    status character varying(20) NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    date_from timestamp without time zone,
    date_to timestamp without time zone,
    CONSTRAINT reservations_status_check CHECK (status = ANY (ARRAY['pending','confirmed','cancelled','completed']))
);

ALTER TABLE public.reservations ALTER COLUMN reservation_id ADD GENERATED ALWAYS AS IDENTITY;
ALTER TABLE ONLY public.reservations ADD CONSTRAINT reservations_pkey PRIMARY KEY (reservation_id);

CREATE TABLE public.reset_codes (
    id integer NOT NULL,
    email character varying(255) NOT NULL,
    code character varying(255) NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    expires_at timestamp without time zone DEFAULT (CURRENT_TIMESTAMP + '01:00:00'::interval)
);

ALTER TABLE ONLY public.reset_codes ADD CONSTRAINT reset_codes_pkey PRIMARY KEY (id);

CREATE TABLE public.users (
    user_id integer NOT NULL,
    name character varying(50) NOT NULL,
    email character varying(100) NOT NULL,
    password character varying(255) NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE public.users ALTER COLUMN user_id ADD GENERATED ALWAYS AS IDENTITY;
ALTER TABLE ONLY public.users ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);
ALTER TABLE ONLY public.users ADD CONSTRAINT users_email_key UNIQUE (email);

-- FOREIGN KEYS

ALTER TABLE ONLY public.payments
    ADD CONSTRAINT payments_reservation_id_fkey FOREIGN KEY (reservation_id) REFERENCES public.reservations(reservation_id);

ALTER TABLE ONLY public.reservation_boxes
    ADD CONSTRAINT reservation_boxes_reservation_id_fkey FOREIGN KEY (reservation_id) REFERENCES public.reservations(reservation_id) ON DELETE CASCADE;

ALTER TABLE ONLY public.reservations
    ADD CONSTRAINT reservations_users_user_id_fkey FOREIGN KEY (users_user_id) REFERENCES public.users(user_id);
