-- Add inspection_frequency and last_inspection_date columns to tenancies
ALTER TABLE public.tenancies 
ADD COLUMN IF NOT EXISTS inspection_frequency integer DEFAULT NULL,
ADD COLUMN IF NOT EXISTS last_inspection_date date DEFAULT NULL;

-- Create index for inspection alerts query
CREATE INDEX IF NOT EXISTS idx_tenancies_inspection ON public.tenancies(inspection_frequency, last_inspection_date) 
WHERE deleted_at IS NULL;