-- Create event_types table to store event type configurations as JSONB
CREATE TABLE IF NOT EXISTS event_types (
    id TEXT PRIMARY KEY DEFAULT generate_random_string(24),
    user_id UUID NOT NULL,
    config JSONB NOT NULL DEFAULT '{}'::jsonb,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
    
    -- Foreign key constraint to users table (auth.users)
    CONSTRAINT fk_event_types_user_id 
        FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE
);

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_event_types_user_id ON event_types(user_id);
CREATE INDEX IF NOT EXISTS idx_event_types_is_active ON event_types(is_active);
CREATE INDEX IF NOT EXISTS idx_event_types_deleted_at ON event_types(deleted_at);

-- Create GIN index for JSONB config column for efficient querying
CREATE INDEX IF NOT EXISTS idx_event_types_config_gin ON event_types USING GIN (config);

-- Add constraint to ensure config JSONB has required fields
ALTER TABLE event_types ADD CONSTRAINT event_types_config_check 
    CHECK (
        config ? 'name' AND 
        (config->>'name')::text <> '' AND
        config ? 'duration' AND 
        (config->>'duration')::int > 0
    );

-- Create function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_event_types_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create trigger to automatically update updated_at
CREATE TRIGGER update_event_types_updated_at
    BEFORE UPDATE ON event_types
    FOR EACH ROW
    EXECUTE FUNCTION update_event_types_updated_at();

-- Enable Row Level Security
ALTER TABLE event_types ENABLE ROW LEVEL SECURITY;

-- Policy to allow users to view their own event types
CREATE POLICY "Users can view their own event types" ON event_types
    FOR SELECT
    TO authenticated
    USING (user_id = auth.uid());

-- Policy to allow users to insert their own event types
CREATE POLICY "Users can insert their own event types" ON event_types
    FOR INSERT
    TO authenticated
    WITH CHECK (user_id = auth.uid());

-- Policy to allow users to update their own event types
CREATE POLICY "Users can update their own event types" ON event_types
    FOR UPDATE
    TO authenticated
    USING (user_id = auth.uid())
    WITH CHECK (user_id = auth.uid());

-- Policy to allow users to delete their own event types (soft delete)
CREATE POLICY "Users can delete their own event types" ON event_types
    FOR UPDATE
    TO authenticated
    USING (user_id = auth.uid() AND deleted_at IS NULL)
    WITH CHECK (user_id = auth.uid());

-- Add helpful comments
COMMENT ON TABLE event_types IS 
    'Event type configurations stored as JSONB with Row Level Security';

COMMENT ON COLUMN event_types.id IS 
    'Primary key: random 24-character alphanumeric string';

COMMENT ON COLUMN event_types.user_id IS 
    'Foreign key reference to auth.users.id';

COMMENT ON COLUMN event_types.config IS 
    'JSONB configuration containing: name (required), description (optional), duration (required), bufferTime, maxBookingsPerDay, requiresApproval, price, location, minAdvanceBooking, etc.';

COMMENT ON COLUMN event_types.is_active IS 
    'Whether this event type is active and available for booking';

COMMENT ON COLUMN event_types.created_at IS 
    'Timestamp when the event type was created';

COMMENT ON COLUMN event_types.updated_at IS 
    'Timestamp when the event type was last updated (auto-updated by trigger)';

COMMENT ON COLUMN event_types.deleted_at IS 
    'Timestamp for soft deletion (NULL means not deleted)';
