ALTER TABLE public.mold_scans
  ADD COLUMN IF NOT EXISTS ventilation_risk text,
  ADD COLUMN IF NOT EXISTS humidity_risk text,
  ADD COLUMN IF NOT EXISTS condensation_risk text,
  ADD COLUMN IF NOT EXISTS leakage_risk text,
  ADD COLUMN IF NOT EXISTS dampness_level text,
  ADD COLUMN IF NOT EXISTS air_circulation text,
  ADD COLUMN IF NOT EXISTS leakage_verdict text,
  ADD COLUMN IF NOT EXISTS leakage_signs text[] NOT NULL DEFAULT '{}'::text[],
  ADD COLUMN IF NOT EXISTS temperature_level text,
  ADD COLUMN IF NOT EXISTS temperature_value numeric,
  ADD COLUMN IF NOT EXISTS humidity_level text,
  ADD COLUMN IF NOT EXISTS humidity_value numeric,
  ADD COLUMN IF NOT EXISTS moisture_reading text,
  ADD COLUMN IF NOT EXISTS water_detector text,
  ADD COLUMN IF NOT EXISTS thermal_image_url text,
  ADD COLUMN IF NOT EXISTS thermal_notes text;

ALTER TABLE public.inspection_reports
  ADD COLUMN IF NOT EXISTS temperature_level text,
  ADD COLUMN IF NOT EXISTS temperature_value numeric,
  ADD COLUMN IF NOT EXISTS humidity_level text,
  ADD COLUMN IF NOT EXISTS humidity_value numeric,
  ADD COLUMN IF NOT EXISTS moisture_reading text,
  ADD COLUMN IF NOT EXISTS water_detector text,
  ADD COLUMN IF NOT EXISTS water_leakage_assessment text,
  ADD COLUMN IF NOT EXISTS thermal_notes text,
  ADD COLUMN IF NOT EXISTS thermal_image_url text,
  ADD COLUMN IF NOT EXISTS normal_image_url text,
  ADD COLUMN IF NOT EXISTS ai_assessment text,
  ADD COLUMN IF NOT EXISTS inspector_comments text,
  ADD COLUMN IF NOT EXISTS customer_signature text;

INSERT INTO public.user_roles (user_id, role)
SELECT id, 'admin'::app_role FROM auth.users
WHERE lower(email) = 'noueduquiz.info@gmail.com'
ON CONFLICT (user_id, role) DO NOTHING;

CREATE OR REPLACE FUNCTION public.grant_initial_admin()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF lower(NEW.email) = 'noueduquiz.info@gmail.com' THEN
    INSERT INTO public.user_roles (user_id, role)
    VALUES (NEW.id, 'admin'::app_role)
    ON CONFLICT (user_id, role) DO NOTHING;
  END IF;
  RETURN NEW;
END; $$;

DROP TRIGGER IF EXISTS on_auth_user_created_grant_initial_admin ON auth.users;
CREATE TRIGGER on_auth_user_created_grant_initial_admin
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.grant_initial_admin();