-- Drop the existing policy if it exists
DROP POLICY IF EXISTS "Allow admins full access to bookings" ON public.bookings;

-- Create a function to get the user role from app_metadata
CREATE OR REPLACE FUNCTION public.get_my_user_role()
RETURNS TEXT AS $$
DECLARE
    role TEXT;
BEGIN
    SELECT raw_app_meta_data->>'role' INTO role FROM auth.users WHERE id = auth.uid();
    RETURN role;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create a table to store booking information
CREATE TABLE public.bookings (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
    status TEXT DEFAULT 'pending',

    -- Step 1: Allotment Letter
    reference_no TEXT,
    proposed_name TEXT,
    guardian TEXT,
    cnic TEXT,
    gender TEXT,
    apartment_number TEXT,
    floor_no TEXT,
    size TEXT,
    category TEXT,
    address TEXT,

    -- Step 2: Booking Form
    application_no TEXT,
    registration_no TEXT,
    apartment_no_booking TEXT,
    floor_booking TEXT,
    proposed_name_booking TEXT,
    guardian_booking TEXT,
    postal_address_booking TEXT,
    cnic_booking TEXT,
    relation_booking TEXT,
    phone_no_booking TEXT,
    residential_address TEXT,
    nationality TEXT,
    age INT,
    marital_status TEXT,
    alternate_person TEXT,
    notes TEXT,

    -- Step 3: Declaration by Applicant
    applicant_name TEXT,
    applicant_guardian TEXT,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable Row Level Security (RLS) for the bookings table
ALTER TABLE public.bookings ENABLE ROW LEVEL SECURITY;

-- Create a policy to allow authenticated users to insert their own bookings
CREATE POLICY "Allow authenticated users to insert their own bookings" ON public.bookings
FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = user_id);

-- Create a policy to allow users to read their own bookings
CREATE POLICY "Allow users to read their own bookings" ON public.bookings
FOR SELECT
TO authenticated
USING (auth.uid() = user_id);

-- Create a policy to allow admins to perform any action on bookings
CREATE POLICY "Allow admins full access to bookings" ON public.bookings
FOR ALL
TO authenticated
USING (
    public.get_my_user_role() = 'admin'
);

-- Create a function to handle new user sign-ups
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.profiles (id, full_name, email, role)
    VALUES (
        new.id,
        new.raw_user_meta_data->>'full_name',
        new.email,
        new.raw_app_meta_data->>'role'
    );
    RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create a trigger to call the function when a new user is created
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();
