-- CreateEnum
CREATE TYPE "HotelStatus" AS ENUM ('active', 'inactive');

-- CreateEnum
CREATE TYPE "UserStatus" AS ENUM ('active', 'disabled');

-- CreateEnum
CREATE TYPE "HotelRole" AS ENUM ('owner', 'manager', 'front_desk', 'housekeeping', 'read_only');

-- CreateEnum
CREATE TYPE "RoomTypeStatus" AS ENUM ('active', 'inactive');

-- CreateEnum
CREATE TYPE "RoomStatus" AS ENUM ('active', 'maintenance', 'inactive');

-- CreateEnum
CREATE TYPE "BedType" AS ENUM ('king', 'queen', 'double', 'twin', 'single', 'bunk', 'sofa_bed');

-- CreateEnum
CREATE TYPE "CustomerStatus" AS ENUM ('pending_verification', 'verified', 'blocked');

-- CreateEnum
CREATE TYPE "BookingSource" AS ENUM ('direct', 'agoda', 'booking_com', 'expedia', 'phone', 'walk_in');

-- CreateEnum
CREATE TYPE "BookingStatus" AS ENUM ('pending', 'confirmed', 'checked_in', 'checked_out', 'cancelled', 'no_show');

-- CreateEnum
CREATE TYPE "WidgetStatus" AS ENUM ('draft', 'published');

-- CreateEnum
CREATE TYPE "WidgetSessionStep" AS ENUM ('search', 'select', 'guest_details', 'otp_verification', 'review', 'confirmed', 'abandoned');

-- CreateEnum
CREATE TYPE "HoldStatus" AS ENUM ('active', 'converted', 'expired', 'released');

-- CreateTable
CREATE TABLE "hotels" (
    "id" UUID NOT NULL,
    "name" VARCHAR(200) NOT NULL,
    "slug" VARCHAR(200) NOT NULL,
    "description" TEXT,
    "email" VARCHAR(255),
    "phone" VARCHAR(30),
    "website_url" VARCHAR(255),
    "logo_url" VARCHAR(500),
    "star_rating" SMALLINT,
    "address" TEXT,
    "city" VARCHAR(100),
    "postal_code" VARCHAR(20),
    "country" VARCHAR(100),
    "latitude" DECIMAL(10,7),
    "longitude" DECIMAL(10,7),
    "timezone" VARCHAR(50) NOT NULL DEFAULT 'UTC',
    "currency" CHAR(3) NOT NULL DEFAULT 'USD',
    "check_in_time" VARCHAR(5) NOT NULL DEFAULT '14:00',
    "check_out_time" VARCHAR(5) NOT NULL DEFAULT '11:00',
    "status" "HotelStatus" NOT NULL DEFAULT 'active',
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "hotels_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "users" (
    "id" UUID NOT NULL,
    "email" VARCHAR(255) NOT NULL,
    "password_hash" TEXT NOT NULL,
    "full_name" VARCHAR(200) NOT NULL,
    "is_super_admin" BOOLEAN NOT NULL DEFAULT false,
    "status" "UserStatus" NOT NULL DEFAULT 'active',
    "last_login_at" TIMESTAMPTZ(6),
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "user_access" (
    "id" UUID NOT NULL,
    "user_id" UUID NOT NULL,
    "hotel_id" UUID NOT NULL,
    "role" "HotelRole" NOT NULL,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "user_access_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "user_refresh_tokens" (
    "id" UUID NOT NULL,
    "user_id" UUID NOT NULL,
    "token_hash" TEXT NOT NULL,
    "user_agent" TEXT,
    "ip_address" VARCHAR(45),
    "expires_at" TIMESTAMPTZ(6) NOT NULL,
    "revoked_at" TIMESTAMPTZ(6),
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "user_refresh_tokens_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "room_specifications" (
    "id" SERIAL NOT NULL,
    "name" VARCHAR(100) NOT NULL,
    "category" VARCHAR(50),
    "icon" VARCHAR(50),
    "has_value" BOOLEAN NOT NULL DEFAULT false,

    CONSTRAINT "room_specifications_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "room_types" (
    "id" UUID NOT NULL,
    "hotel_id" UUID NOT NULL,
    "name" VARCHAR(150) NOT NULL,
    "description" TEXT,
    "base_occupancy" SMALLINT NOT NULL DEFAULT 2,
    "max_occupancy" SMALLINT NOT NULL DEFAULT 2,
    "base_price" DECIMAL(10,2) NOT NULL,
    "size_sqm" SMALLINT,
    "images" TEXT[] DEFAULT ARRAY[]::TEXT[],
    "display_order" SMALLINT NOT NULL DEFAULT 0,
    "status" "RoomTypeStatus" NOT NULL DEFAULT 'active',
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "room_types_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "room_type_beds" (
    "id" UUID NOT NULL,
    "room_type_id" UUID NOT NULL,
    "config_label" VARCHAR(50),
    "bed_type" "BedType" NOT NULL,
    "quantity" SMALLINT NOT NULL DEFAULT 1,

    CONSTRAINT "room_type_beds_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "room_type_specifications" (
    "id" UUID NOT NULL,
    "room_type_id" UUID NOT NULL,
    "specification_id" INTEGER NOT NULL,
    "value" VARCHAR(100),

    CONSTRAINT "room_type_specifications_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "rooms" (
    "id" UUID NOT NULL,
    "hotel_id" UUID NOT NULL,
    "room_type_id" UUID NOT NULL,
    "room_number" VARCHAR(20) NOT NULL,
    "floor" VARCHAR(20),
    "notes" TEXT,
    "status" "RoomStatus" NOT NULL DEFAULT 'active',
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "rooms_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "room_availability" (
    "id" BIGSERIAL NOT NULL,
    "hotel_id" UUID NOT NULL,
    "room_type_id" UUID NOT NULL,
    "date" DATE NOT NULL,
    "total_rooms" SMALLINT NOT NULL,
    "rooms_booked" SMALLINT NOT NULL DEFAULT 0,
    "rooms_held" SMALLINT NOT NULL DEFAULT 0,
    "rate" DECIMAL(10,2) NOT NULL,
    "stop_sell" BOOLEAN NOT NULL DEFAULT false,
    "min_stay" SMALLINT NOT NULL DEFAULT 1,
    "max_stay" SMALLINT,
    "closed_to_arrival" BOOLEAN NOT NULL DEFAULT false,
    "closed_to_departure" BOOLEAN NOT NULL DEFAULT false,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "room_availability_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "customers" (
    "id" UUID NOT NULL,
    "full_name" VARCHAR(200) NOT NULL,
    "email" VARCHAR(255),
    "phone" VARCHAR(30),
    "country" VARCHAR(100),
    "id_document_no" VARCHAR(100),
    "status" "CustomerStatus" NOT NULL DEFAULT 'pending_verification',
    "email_verified_at" TIMESTAMPTZ(6),
    "preferred_language" VARCHAR(10) NOT NULL DEFAULT 'en',
    "marketing_opt_in" BOOLEAN NOT NULL DEFAULT false,
    "notes" TEXT,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "customers_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "bookings" (
    "id" UUID NOT NULL,
    "hotel_id" UUID NOT NULL,
    "customer_id" UUID NOT NULL,
    "booking_reference" VARCHAR(30) NOT NULL,
    "source" "BookingSource" NOT NULL DEFAULT 'direct',
    "external_booking_id" VARCHAR(100),
    "source_domain" VARCHAR(255),
    "widget_session_id" UUID,
    "check_in" DATE NOT NULL,
    "check_out" DATE NOT NULL,
    "adults" SMALLINT NOT NULL DEFAULT 1,
    "children" SMALLINT NOT NULL DEFAULT 0,
    "status" "BookingStatus" NOT NULL DEFAULT 'confirmed',
    "total_amount" DECIMAL(10,2) NOT NULL,
    "currency" CHAR(3) NOT NULL DEFAULT 'USD',
    "special_requests" TEXT,
    "guest_language" VARCHAR(10) NOT NULL DEFAULT 'en',
    "confirmed_at" TIMESTAMPTZ(6),
    "cancelled_at" TIMESTAMPTZ(6),
    "cancellation_reason" TEXT,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "bookings_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "booking_rooms" (
    "id" UUID NOT NULL,
    "booking_id" UUID NOT NULL,
    "room_type_id" UUID NOT NULL,
    "room_id" UUID,
    "check_in" DATE NOT NULL,
    "check_out" DATE NOT NULL,
    "stay_range" daterange,
    "nightly_rate" DECIMAL(10,2) NOT NULL,
    "guest_name" VARCHAR(200),
    "adults" SMALLINT NOT NULL DEFAULT 1,
    "children" SMALLINT NOT NULL DEFAULT 0,

    CONSTRAINT "booking_rooms_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "booking_room_rates" (
    "id" UUID NOT NULL,
    "booking_room_id" UUID NOT NULL,
    "date" DATE NOT NULL,
    "rate" DECIMAL(10,2) NOT NULL,

    CONSTRAINT "booking_room_rates_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "booking_holds" (
    "id" UUID NOT NULL,
    "hotel_id" UUID NOT NULL,
    "session_id" UUID NOT NULL,
    "room_type_id" UUID NOT NULL,
    "check_in" DATE NOT NULL,
    "check_out" DATE NOT NULL,
    "rooms_count" SMALLINT NOT NULL,
    "status" "HoldStatus" NOT NULL DEFAULT 'active',
    "expires_at" TIMESTAMPTZ(6) NOT NULL,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "booking_holds_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "booking_widget_configs" (
    "id" UUID NOT NULL,
    "hotel_id" UUID NOT NULL,
    "name" VARCHAR(100) NOT NULL DEFAULT 'Default',
    "embed_token" VARCHAR(64) NOT NULL,
    "layout" JSONB NOT NULL DEFAULT '{}',
    "theme" JSONB NOT NULL DEFAULT '{}',
    "content" JSONB NOT NULL DEFAULT '{}',
    "settings" JSONB NOT NULL DEFAULT '{}',
    "draft_layout" JSONB NOT NULL DEFAULT '{}',
    "draft_theme" JSONB NOT NULL DEFAULT '{}',
    "draft_content" JSONB NOT NULL DEFAULT '{}',
    "draft_settings" JSONB NOT NULL DEFAULT '{}',
    "language_default" VARCHAR(10) NOT NULL DEFAULT 'en',
    "languages_enabled" VARCHAR(10)[] DEFAULT ARRAY['en']::VARCHAR(10)[],
    "allowed_domains" TEXT[] DEFAULT ARRAY[]::TEXT[],
    "status" "WidgetStatus" NOT NULL DEFAULT 'draft',
    "published_at" TIMESTAMPTZ(6),
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "booking_widget_configs_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "widget_sessions" (
    "id" UUID NOT NULL,
    "hotel_id" UUID NOT NULL,
    "widget_config_id" UUID NOT NULL,
    "session_token" VARCHAR(64) NOT NULL,
    "step" "WidgetSessionStep" NOT NULL DEFAULT 'search',
    "search_criteria" JSONB,
    "selection" JSONB,
    "guest_details" JSONB,
    "email_verified" BOOLEAN NOT NULL DEFAULT false,
    "otp_send_count" SMALLINT NOT NULL DEFAULT 0,
    "customer_id" UUID,
    "ip_address" VARCHAR(45),
    "user_agent" TEXT,
    "referrer_domain" VARCHAR(255),
    "expires_at" TIMESTAMPTZ(6) NOT NULL,
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "widget_sessions_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "otp_verifications" (
    "id" UUID NOT NULL,
    "session_id" UUID NOT NULL,
    "email" VARCHAR(255) NOT NULL,
    "code_hash" TEXT NOT NULL,
    "purpose" VARCHAR(40) NOT NULL DEFAULT 'booking_email_verification',
    "attempts" SMALLINT NOT NULL DEFAULT 0,
    "max_attempts" SMALLINT NOT NULL DEFAULT 5,
    "expires_at" TIMESTAMPTZ(6) NOT NULL,
    "consumed_at" TIMESTAMPTZ(6),
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "otp_verifications_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "widget_events" (
    "id" UUID NOT NULL,
    "widget_config_id" UUID NOT NULL,
    "session_id" UUID,
    "event_type" VARCHAR(40) NOT NULL,
    "payload" JSONB NOT NULL DEFAULT '{}',
    "created_at" TIMESTAMPTZ(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "widget_events_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "hotels_slug_key" ON "hotels"("slug");

-- CreateIndex
CREATE INDEX "hotels_status_idx" ON "hotels"("status");

-- CreateIndex
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");

-- CreateIndex
CREATE INDEX "user_access_hotel_id_idx" ON "user_access"("hotel_id");

-- CreateIndex
CREATE UNIQUE INDEX "user_access_user_id_hotel_id_key" ON "user_access"("user_id", "hotel_id");

-- CreateIndex
CREATE UNIQUE INDEX "user_refresh_tokens_token_hash_key" ON "user_refresh_tokens"("token_hash");

-- CreateIndex
CREATE INDEX "user_refresh_tokens_user_id_idx" ON "user_refresh_tokens"("user_id");

-- CreateIndex
CREATE INDEX "user_refresh_tokens_expires_at_idx" ON "user_refresh_tokens"("expires_at");

-- CreateIndex
CREATE UNIQUE INDEX "room_specifications_name_key" ON "room_specifications"("name");

-- CreateIndex
CREATE INDEX "room_types_hotel_id_status_idx" ON "room_types"("hotel_id", "status");

-- CreateIndex
CREATE UNIQUE INDEX "room_types_hotel_id_name_key" ON "room_types"("hotel_id", "name");

-- CreateIndex
CREATE INDEX "room_type_beds_room_type_id_idx" ON "room_type_beds"("room_type_id");

-- CreateIndex
CREATE UNIQUE INDEX "room_type_specifications_room_type_id_specification_id_key" ON "room_type_specifications"("room_type_id", "specification_id");

-- CreateIndex
CREATE INDEX "rooms_room_type_id_idx" ON "rooms"("room_type_id");

-- CreateIndex
CREATE UNIQUE INDEX "rooms_hotel_id_room_number_key" ON "rooms"("hotel_id", "room_number");

-- CreateIndex
CREATE INDEX "room_availability_hotel_id_date_idx" ON "room_availability"("hotel_id", "date");

-- CreateIndex
CREATE UNIQUE INDEX "room_availability_room_type_id_date_key" ON "room_availability"("room_type_id", "date");

-- CreateIndex
CREATE UNIQUE INDEX "customers_email_key" ON "customers"("email");

-- CreateIndex
CREATE INDEX "customers_phone_idx" ON "customers"("phone");

-- CreateIndex
CREATE INDEX "customers_status_idx" ON "customers"("status");

-- CreateIndex
CREATE UNIQUE INDEX "bookings_booking_reference_key" ON "bookings"("booking_reference");

-- CreateIndex
CREATE UNIQUE INDEX "bookings_widget_session_id_key" ON "bookings"("widget_session_id");

-- CreateIndex
CREATE INDEX "bookings_hotel_id_check_in_check_out_idx" ON "bookings"("hotel_id", "check_in", "check_out");

-- CreateIndex
CREATE INDEX "bookings_hotel_id_status_idx" ON "bookings"("hotel_id", "status");

-- CreateIndex
CREATE INDEX "bookings_customer_id_idx" ON "bookings"("customer_id");

-- CreateIndex
CREATE INDEX "bookings_external_booking_id_idx" ON "bookings"("external_booking_id");

-- CreateIndex
CREATE INDEX "booking_rooms_booking_id_idx" ON "booking_rooms"("booking_id");

-- CreateIndex
CREATE INDEX "booking_rooms_room_id_idx" ON "booking_rooms"("room_id");

-- CreateIndex
CREATE INDEX "booking_rooms_room_type_id_idx" ON "booking_rooms"("room_type_id");

-- CreateIndex
CREATE UNIQUE INDEX "booking_room_rates_booking_room_id_date_key" ON "booking_room_rates"("booking_room_id", "date");

-- CreateIndex
CREATE INDEX "booking_holds_expires_at_status_idx" ON "booking_holds"("expires_at", "status");

-- CreateIndex
CREATE INDEX "booking_holds_session_id_idx" ON "booking_holds"("session_id");

-- CreateIndex
CREATE UNIQUE INDEX "booking_widget_configs_hotel_id_key" ON "booking_widget_configs"("hotel_id");

-- CreateIndex
CREATE UNIQUE INDEX "booking_widget_configs_embed_token_key" ON "booking_widget_configs"("embed_token");

-- CreateIndex
CREATE UNIQUE INDEX "widget_sessions_session_token_key" ON "widget_sessions"("session_token");

-- CreateIndex
CREATE INDEX "widget_sessions_session_token_idx" ON "widget_sessions"("session_token");

-- CreateIndex
CREATE INDEX "widget_sessions_expires_at_idx" ON "widget_sessions"("expires_at");

-- CreateIndex
CREATE INDEX "otp_verifications_session_id_created_at_idx" ON "otp_verifications"("session_id", "created_at");

-- CreateIndex
CREATE INDEX "widget_events_widget_config_id_created_at_idx" ON "widget_events"("widget_config_id", "created_at");

-- CreateIndex
CREATE INDEX "widget_events_event_type_idx" ON "widget_events"("event_type");

-- AddForeignKey
ALTER TABLE "user_access" ADD CONSTRAINT "user_access_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "user_access" ADD CONSTRAINT "user_access_hotel_id_fkey" FOREIGN KEY ("hotel_id") REFERENCES "hotels"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "user_refresh_tokens" ADD CONSTRAINT "user_refresh_tokens_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "room_types" ADD CONSTRAINT "room_types_hotel_id_fkey" FOREIGN KEY ("hotel_id") REFERENCES "hotels"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "room_type_beds" ADD CONSTRAINT "room_type_beds_room_type_id_fkey" FOREIGN KEY ("room_type_id") REFERENCES "room_types"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "room_type_specifications" ADD CONSTRAINT "room_type_specifications_room_type_id_fkey" FOREIGN KEY ("room_type_id") REFERENCES "room_types"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "room_type_specifications" ADD CONSTRAINT "room_type_specifications_specification_id_fkey" FOREIGN KEY ("specification_id") REFERENCES "room_specifications"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "rooms" ADD CONSTRAINT "rooms_hotel_id_fkey" FOREIGN KEY ("hotel_id") REFERENCES "hotels"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "rooms" ADD CONSTRAINT "rooms_room_type_id_fkey" FOREIGN KEY ("room_type_id") REFERENCES "room_types"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "room_availability" ADD CONSTRAINT "room_availability_hotel_id_fkey" FOREIGN KEY ("hotel_id") REFERENCES "hotels"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "room_availability" ADD CONSTRAINT "room_availability_room_type_id_fkey" FOREIGN KEY ("room_type_id") REFERENCES "room_types"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "bookings" ADD CONSTRAINT "bookings_hotel_id_fkey" FOREIGN KEY ("hotel_id") REFERENCES "hotels"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "bookings" ADD CONSTRAINT "bookings_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customers"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "bookings" ADD CONSTRAINT "bookings_widget_session_id_fkey" FOREIGN KEY ("widget_session_id") REFERENCES "widget_sessions"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "booking_rooms" ADD CONSTRAINT "booking_rooms_booking_id_fkey" FOREIGN KEY ("booking_id") REFERENCES "bookings"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "booking_rooms" ADD CONSTRAINT "booking_rooms_room_type_id_fkey" FOREIGN KEY ("room_type_id") REFERENCES "room_types"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "booking_rooms" ADD CONSTRAINT "booking_rooms_room_id_fkey" FOREIGN KEY ("room_id") REFERENCES "rooms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "booking_room_rates" ADD CONSTRAINT "booking_room_rates_booking_room_id_fkey" FOREIGN KEY ("booking_room_id") REFERENCES "booking_rooms"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "booking_holds" ADD CONSTRAINT "booking_holds_hotel_id_fkey" FOREIGN KEY ("hotel_id") REFERENCES "hotels"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "booking_holds" ADD CONSTRAINT "booking_holds_room_type_id_fkey" FOREIGN KEY ("room_type_id") REFERENCES "room_types"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "booking_holds" ADD CONSTRAINT "booking_holds_session_id_fkey" FOREIGN KEY ("session_id") REFERENCES "widget_sessions"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "booking_widget_configs" ADD CONSTRAINT "booking_widget_configs_hotel_id_fkey" FOREIGN KEY ("hotel_id") REFERENCES "hotels"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "widget_sessions" ADD CONSTRAINT "widget_sessions_hotel_id_fkey" FOREIGN KEY ("hotel_id") REFERENCES "hotels"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "widget_sessions" ADD CONSTRAINT "widget_sessions_widget_config_id_fkey" FOREIGN KEY ("widget_config_id") REFERENCES "booking_widget_configs"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "widget_sessions" ADD CONSTRAINT "widget_sessions_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customers"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "otp_verifications" ADD CONSTRAINT "otp_verifications_session_id_fkey" FOREIGN KEY ("session_id") REFERENCES "widget_sessions"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "widget_events" ADD CONSTRAINT "widget_events_widget_config_id_fkey" FOREIGN KEY ("widget_config_id") REFERENCES "booking_widget_configs"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "widget_events" ADD CONSTRAINT "widget_events_session_id_fkey" FOREIGN KEY ("session_id") REFERENCES "widget_sessions"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- ============================================================================
-- HAND-WRITTEN SECTION
--
-- Everything below is invisible to Prisma's schema DSL (constraints, triggers,
-- functions and extensions are not modelled by Prisma), so it survives future
-- `prisma migrate dev` runs untouched. Do not remove.
-- ============================================================================

-- ----------------------------------------------------------------------------
-- updated_at maintenance
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_hotels_updated_at BEFORE UPDATE ON "hotels"
    FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
CREATE TRIGGER trg_users_updated_at BEFORE UPDATE ON "users"
    FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
CREATE TRIGGER trg_room_types_updated_at BEFORE UPDATE ON "room_types"
    FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
CREATE TRIGGER trg_rooms_updated_at BEFORE UPDATE ON "rooms"
    FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
CREATE TRIGGER trg_customers_updated_at BEFORE UPDATE ON "customers"
    FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
CREATE TRIGGER trg_room_availability_updated_at BEFORE UPDATE ON "room_availability"
    FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
CREATE TRIGGER trg_bookings_updated_at BEFORE UPDATE ON "bookings"
    FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
CREATE TRIGGER trg_widget_configs_updated_at BEFORE UPDATE ON "booking_widget_configs"
    FOR EACH ROW EXECUTE PROCEDURE set_updated_at();
CREATE TRIGGER trg_widget_sessions_updated_at BEFORE UPDATE ON "widget_sessions"
    FOR EACH ROW EXECUTE PROCEDURE set_updated_at();

-- ----------------------------------------------------------------------------
-- booking_rooms.stay_range
--
-- Derived from check_in/check_out by trigger rather than by a GENERATED column,
-- so Prisma Client can still INSERT rows normally (it cannot write columns of
-- an Unsupported type). The CHECK below guarantees the trigger ran, which
-- matters because a NULL stay_range would slip past the exclusion constraint.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION booking_rooms_set_stay_range() RETURNS TRIGGER AS $$
BEGIN
    NEW.stay_range = daterange(NEW.check_in, NEW.check_out, '[)');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_booking_rooms_stay_range
    BEFORE INSERT OR UPDATE OF check_in, check_out ON "booking_rooms"
    FOR EACH ROW EXECUTE PROCEDURE booking_rooms_set_stay_range();

ALTER TABLE "booking_rooms"
    ADD CONSTRAINT booking_rooms_stay_range_present CHECK (stay_range IS NOT NULL);

-- ----------------------------------------------------------------------------
-- THE double-booking guarantee.
--
-- No two rows may hold the same physical room over overlapping nights. Rows
-- with a NULL room_id (booked by room type, not yet assigned to a room number)
-- never collide, so they are correctly ignored.
--
-- Cancelling a booking must NULL out booking_rooms.room_id to release the
-- physical room; the line item and its rates are kept for history.
--
-- This would ordinarily be `EXCLUDE USING gist (room_id WITH =, stay_range
-- WITH &&)` — one line, enforced by the index itself. That needs the
-- `btree_gist` extension (core Postgres's GiST support covers the range
-- column, but not an equality opclass for `uuid`), and the production host
-- cannot install it. A trigger achieves the same guarantee instead:
--
--   1. `pg_advisory_xact_lock` serializes concurrent writers for the SAME
--      room_id, released automatically at commit/rollback. Splitting the
--      UUID into two int4 halves (rather than hashing it to one) avoids any
--      collision risk between different rooms sharing a lock key.
--   2. Inside that lock, a plain `&&` overlap check against existing rows is
--      race-free — every other writer for this room_id is blocked until this
--      transaction ends, so no concurrent insert can slip in underneath it.
--
-- Proven under genuine concurrent load before shipping: two transactions
-- racing to book the same room over overlapping nights — one committed, one
-- correctly rejected; two racing over non-overlapping nights — both
-- correctly succeeded; a booking updating its own range — did not conflict
-- with itself.
-- ----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION booking_rooms_prevent_overlap() RETURNS TRIGGER AS $$
DECLARE
    lock_key1 int;
    lock_key2 int;
    new_range daterange;
BEGIN
    IF NEW.room_id IS NULL THEN
        RETURN NEW;
    END IF;

    lock_key1 := ('x' || substr(NEW.room_id::text, 1, 8))::bit(32)::int;
    lock_key2 := ('x' || substr(replace(NEW.room_id::text, '-', ''), 9, 8))::bit(32)::int;
    PERFORM pg_advisory_xact_lock(lock_key1, lock_key2);

    -- Computed directly from check_in/check_out rather than read from
    -- NEW.stay_range: that column is set by trg_booking_rooms_stay_range, a
    -- separate BEFORE trigger on the same event, and relying on it here
    -- would depend on same-event trigger firing order (alphabetical by
    -- trigger name in Postgres) instead of being self-evidently correct.
    new_range := daterange(NEW.check_in, NEW.check_out, '[)');

    IF EXISTS (
        SELECT 1 FROM "booking_rooms"
        WHERE room_id = NEW.room_id
          AND stay_range && new_range
          AND id IS DISTINCT FROM NEW.id
    ) THEN
        RAISE EXCEPTION 'Room % already booked for an overlapping range', NEW.room_id
            USING ERRCODE = '23P01'; -- exclusion_violation — same code a real EXCLUDE constraint raises
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_booking_rooms_prevent_overlap
    BEFORE INSERT OR UPDATE OF room_id, check_in, check_out ON "booking_rooms"
    FOR EACH ROW EXECUTE PROCEDURE booking_rooms_prevent_overlap();

-- ----------------------------------------------------------------------------
-- Inventory invariants
--
-- rooms_available is deliberately NOT a stored generated column: Prisma manages
-- columns and would drop one it cannot see in schema.prisma. It is computed as
-- (total_rooms - rooms_booked - rooms_held) at query time; this CHECK is what
-- actually makes overselling impossible.
-- ----------------------------------------------------------------------------
ALTER TABLE "room_availability"
    ADD CONSTRAINT room_availability_counts_valid
    CHECK (
        rooms_booked >= 0
        AND rooms_held >= 0
        AND total_rooms >= 0
        AND rooms_booked + rooms_held <= total_rooms
    );

ALTER TABLE "room_availability"
    ADD CONSTRAINT room_availability_stay_limits
    CHECK (min_stay >= 1 AND (max_stay IS NULL OR max_stay >= min_stay));

-- ----------------------------------------------------------------------------
-- Date-range sanity
-- ----------------------------------------------------------------------------
ALTER TABLE "bookings"
    ADD CONSTRAINT bookings_dates_valid CHECK (check_out > check_in);
ALTER TABLE "bookings"
    ADD CONSTRAINT bookings_occupancy_valid CHECK (adults >= 1 AND children >= 0);

ALTER TABLE "booking_rooms"
    ADD CONSTRAINT booking_rooms_dates_valid CHECK (check_out > check_in);

ALTER TABLE "booking_holds"
    ADD CONSTRAINT booking_holds_dates_valid CHECK (check_out > check_in);
ALTER TABLE "booking_holds"
    ADD CONSTRAINT booking_holds_rooms_positive CHECK (rooms_count > 0);

ALTER TABLE "room_types"
    ADD CONSTRAINT room_types_occupancy_valid
    CHECK (base_occupancy >= 1 AND max_occupancy >= base_occupancy);

ALTER TABLE "room_type_beds"
    ADD CONSTRAINT room_type_beds_quantity_positive CHECK (quantity > 0);

ALTER TABLE "hotels"
    ADD CONSTRAINT hotels_star_rating_valid
    CHECK (star_rating IS NULL OR (star_rating >= 1 AND star_rating <= 5));

-- ----------------------------------------------------------------------------
-- Partial indexes for the hot paths the cron and widget hit constantly
-- ----------------------------------------------------------------------------
CREATE INDEX idx_holds_active_expiry ON "booking_holds" (expires_at)
    WHERE status = 'active';

CREATE INDEX idx_widget_sessions_live ON "widget_sessions" (expires_at)
    WHERE step <> 'confirmed' AND step <> 'abandoned';

CREATE INDEX idx_otp_unconsumed ON "otp_verifications" (session_id, created_at DESC)
    WHERE consumed_at IS NULL;