-- Keep existing records intact while introducing reusable property and organization records.

DO $$ BEGIN
  CREATE TYPE public.organization_role AS ENUM ('owner','administrator','facility_manager','viewer');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

CREATE TABLE public.organizations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id uuid NOT NULL,
  name text NOT NULL,
  organization_type text NOT NULL DEFAULT 'property_management',
  country public.country_code NOT NULL DEFAULT 'NG',
  currency text NOT NULL DEFAULT 'NGN',
  timezone text NOT NULL DEFAULT 'Africa/Lagos',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.organizations TO authenticated;
GRANT ALL ON public.organizations TO service_role;
ALTER TABLE public.organizations ENABLE ROW LEVEL SECURITY;

CREATE TABLE public.organization_members (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
  user_id uuid NOT NULL,
  role public.organization_role NOT NULL DEFAULT 'viewer',
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (organization_id, user_id)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.organization_members TO authenticated;
GRANT ALL ON public.organization_members TO service_role;
ALTER TABLE public.organization_members ENABLE ROW LEVEL SECURITY;

CREATE TABLE public.properties (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id uuid NOT NULL,
  organization_id uuid REFERENCES public.organizations(id) ON DELETE SET NULL,
  name text NOT NULL,
  property_type text NOT NULL DEFAULT 'Residential',
  street_address text NOT NULL,
  city text,
  state_region text,
  postcode text,
  country public.country_code NOT NULL DEFAULT 'NG',
  currency text NOT NULL DEFAULT 'NGN',
  timezone text NOT NULL DEFAULT 'Africa/Lagos',
  measurement_system text NOT NULL DEFAULT 'metric',
  notes text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.properties TO authenticated;
GRANT ALL ON public.properties TO service_role;
ALTER TABLE public.properties ENABLE ROW LEVEL SECURITY;

CREATE TABLE public.buildings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id uuid NOT NULL REFERENCES public.properties(id) ON DELETE CASCADE,
  name text NOT NULL,
  address text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.buildings TO authenticated;
GRANT ALL ON public.buildings TO service_role;
ALTER TABLE public.buildings ENABLE ROW LEVEL SECURITY;

CREATE TABLE public.units (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  building_id uuid NOT NULL REFERENCES public.buildings(id) ON DELETE CASCADE,
  name text NOT NULL,
  unit_type text NOT NULL DEFAULT 'Unit',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.units TO authenticated;
GRANT ALL ON public.units TO service_role;
ALTER TABLE public.units ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION public.organizations_add_owner()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  INSERT INTO public.organization_members (organization_id, user_id, role)
  VALUES (NEW.id, NEW.owner_id, 'owner')
  ON CONFLICT (organization_id, user_id) DO NOTHING;
  RETURN NEW;
END;
$$;
CREATE TRIGGER organizations_add_owner AFTER INSERT ON public.organizations
  FOR EACH ROW EXECUTE FUNCTION public.organizations_add_owner();
REVOKE EXECUTE ON FUNCTION public.organizations_add_owner() FROM anon, authenticated, public;

CREATE OR REPLACE FUNCTION public.touch_property_updated_at()
RETURNS trigger LANGUAGE plpgsql SET search_path = public AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$;
CREATE TRIGGER organizations_touch BEFORE UPDATE ON public.organizations FOR EACH ROW EXECUTE FUNCTION public.touch_property_updated_at();
CREATE TRIGGER properties_touch BEFORE UPDATE ON public.properties FOR EACH ROW EXECUTE FUNCTION public.touch_property_updated_at();
CREATE TRIGGER buildings_touch BEFORE UPDATE ON public.buildings FOR EACH ROW EXECUTE FUNCTION public.touch_property_updated_at();
CREATE TRIGGER units_touch BEFORE UPDATE ON public.units FOR EACH ROW EXECUTE FUNCTION public.touch_property_updated_at();

CREATE POLICY "organization members read organization" ON public.organizations
  FOR SELECT TO authenticated
  USING (owner_id = auth.uid() OR public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.organization_members om
    WHERE om.organization_id = organizations.id AND om.user_id = auth.uid()
  ));
CREATE POLICY "users create own organization" ON public.organizations
  FOR INSERT TO authenticated
  WITH CHECK (owner_id = auth.uid());
CREATE POLICY "organization admins update organization" ON public.organizations
  FOR UPDATE TO authenticated
  USING (owner_id = auth.uid() OR public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.organization_members om
    WHERE om.organization_id = organizations.id AND om.user_id = auth.uid() AND om.role IN ('owner','administrator')
  ))
  WITH CHECK (owner_id = auth.uid() OR public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.organization_members om
    WHERE om.organization_id = organizations.id AND om.user_id = auth.uid() AND om.role IN ('owner','administrator')
  ));
CREATE POLICY "organization owner deletes organization" ON public.organizations
  FOR DELETE TO authenticated
  USING (owner_id = auth.uid() OR public.has_role(auth.uid(),'admin'));

CREATE POLICY "organization members read membership" ON public.organization_members
  FOR SELECT TO authenticated
  USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.organization_members own
    WHERE own.organization_id = organization_members.organization_id AND own.user_id = auth.uid()
  ));
CREATE POLICY "organization admins manage membership" ON public.organization_members
  FOR INSERT TO authenticated
  WITH CHECK (public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.organization_members own
    WHERE own.organization_id = organization_members.organization_id AND own.user_id = auth.uid() AND own.role IN ('owner','administrator')
  ));
CREATE POLICY "organization admins update membership" ON public.organization_members
  FOR UPDATE TO authenticated
  USING (public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.organization_members own
    WHERE own.organization_id = organization_members.organization_id AND own.user_id = auth.uid() AND own.role IN ('owner','administrator')
  ))
  WITH CHECK (public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.organization_members own
    WHERE own.organization_id = organization_members.organization_id AND own.user_id = auth.uid() AND own.role IN ('owner','administrator')
  ));
CREATE POLICY "organization admins delete membership" ON public.organization_members
  FOR DELETE TO authenticated
  USING (public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.organization_members own
    WHERE own.organization_id = organization_members.organization_id AND own.user_id = auth.uid() AND own.role IN ('owner','administrator')
  ));

CREATE POLICY "property owners and authorized organizations read properties" ON public.properties
  FOR SELECT TO authenticated
  USING (owner_id = auth.uid() OR public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.organization_members om
    WHERE om.organization_id = properties.organization_id AND om.user_id = auth.uid()
  ));
CREATE POLICY "users create own properties" ON public.properties
  FOR INSERT TO authenticated
  WITH CHECK (
    owner_id = auth.uid()
    AND (organization_id IS NULL OR public.has_role(auth.uid(),'admin') OR EXISTS (
      SELECT 1 FROM public.organization_members om
      WHERE om.organization_id = properties.organization_id AND om.user_id = auth.uid() AND om.role IN ('owner','administrator','facility_manager')
    ))
  );
CREATE POLICY "property owners and managers update properties" ON public.properties
  FOR UPDATE TO authenticated
  USING (owner_id = auth.uid() OR public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.organization_members om
    WHERE om.organization_id = properties.organization_id AND om.user_id = auth.uid() AND om.role IN ('owner','administrator','facility_manager')
  ))
  WITH CHECK (owner_id = auth.uid() OR public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.organization_members om
    WHERE om.organization_id = properties.organization_id AND om.user_id = auth.uid() AND om.role IN ('owner','administrator','facility_manager')
  ));
CREATE POLICY "property owners delete properties" ON public.properties
  FOR DELETE TO authenticated
  USING (owner_id = auth.uid() OR public.has_role(auth.uid(),'admin'));

CREATE POLICY "authorized users read buildings" ON public.buildings
  FOR SELECT TO authenticated
  USING (public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.properties p
    WHERE p.id = buildings.property_id AND (p.owner_id = auth.uid() OR EXISTS (
      SELECT 1 FROM public.organization_members om WHERE om.organization_id = p.organization_id AND om.user_id = auth.uid()
    ))
  ));
CREATE POLICY "property managers manage buildings" ON public.buildings
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.properties p
    WHERE p.id = buildings.property_id AND (p.owner_id = auth.uid() OR EXISTS (
      SELECT 1 FROM public.organization_members om WHERE om.organization_id = p.organization_id AND om.user_id = auth.uid() AND om.role IN ('owner','administrator','facility_manager')
    ))
  ))
  WITH CHECK (public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.properties p
    WHERE p.id = buildings.property_id AND (p.owner_id = auth.uid() OR EXISTS (
      SELECT 1 FROM public.organization_members om WHERE om.organization_id = p.organization_id AND om.user_id = auth.uid() AND om.role IN ('owner','administrator','facility_manager')
    ))
  ));

CREATE POLICY "authorized users read units" ON public.units
  FOR SELECT TO authenticated
  USING (public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.buildings b JOIN public.properties p ON p.id = b.property_id
    WHERE b.id = units.building_id AND (p.owner_id = auth.uid() OR EXISTS (
      SELECT 1 FROM public.organization_members om WHERE om.organization_id = p.organization_id AND om.user_id = auth.uid()
    ))
  ));
CREATE POLICY "property managers manage units" ON public.units
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.buildings b JOIN public.properties p ON p.id = b.property_id
    WHERE b.id = units.building_id AND (p.owner_id = auth.uid() OR EXISTS (
      SELECT 1 FROM public.organization_members om WHERE om.organization_id = p.organization_id AND om.user_id = auth.uid() AND om.role IN ('owner','administrator','facility_manager')
    ))
  ))
  WITH CHECK (public.has_role(auth.uid(),'admin') OR EXISTS (
    SELECT 1 FROM public.buildings b JOIN public.properties p ON p.id = b.property_id
    WHERE b.id = units.building_id AND (p.owner_id = auth.uid() OR EXISTS (
      SELECT 1 FROM public.organization_members om WHERE om.organization_id = p.organization_id AND om.user_id = auth.uid() AND om.role IN ('owner','administrator','facility_manager')
    ))
  ));

CREATE INDEX organizations_owner_idx ON public.organizations(owner_id);
CREATE INDEX organization_members_user_idx ON public.organization_members(user_id);
CREATE INDEX organization_members_org_idx ON public.organization_members(organization_id);
CREATE INDEX properties_owner_idx ON public.properties(owner_id);
CREATE INDEX properties_organization_idx ON public.properties(organization_id);
CREATE INDEX buildings_property_idx ON public.buildings(property_id);
CREATE INDEX units_building_idx ON public.units(building_id);

-- Provider access: company admins retain company-wide access; other staff only see assigned jobs.
DROP POLICY IF EXISTS "company staff read own bookings" ON public.bookings;
DROP POLICY IF EXISTS "company staff update own bookings" ON public.bookings;
CREATE POLICY "company admins read company bookings" ON public.bookings
  FOR SELECT TO authenticated
  USING (company_id IS NOT NULL AND public.has_company_role(auth.uid(), company_id, 'company_admin'));
CREATE POLICY "assigned staff read assigned bookings" ON public.bookings
  FOR SELECT TO authenticated
  USING (company_id IS NOT NULL AND assigned_to = auth.uid());
CREATE POLICY "company admins update company bookings" ON public.bookings
  FOR UPDATE TO authenticated
  USING (company_id IS NOT NULL AND public.has_company_role(auth.uid(), company_id, 'company_admin'))
  WITH CHECK (company_id IS NOT NULL AND public.has_company_role(auth.uid(), company_id, 'company_admin'));
CREATE POLICY "assigned staff update assigned bookings" ON public.bookings
  FOR UPDATE TO authenticated
  USING (company_id IS NOT NULL AND assigned_to = auth.uid())
  WITH CHECK (company_id IS NOT NULL AND assigned_to = auth.uid());

DROP POLICY IF EXISTS "company staff read own reports" ON public.inspection_reports;
DROP POLICY IF EXISTS "company staff write own reports" ON public.inspection_reports;
DROP POLICY IF EXISTS "company staff update own reports" ON public.inspection_reports;
CREATE POLICY "company admins read company reports" ON public.inspection_reports
  FOR SELECT TO authenticated
  USING (company_id IS NOT NULL AND public.has_company_role(auth.uid(), company_id, 'company_admin'));
CREATE POLICY "assigned staff read assigned reports" ON public.inspection_reports
  FOR SELECT TO authenticated
  USING (company_id IS NOT NULL AND EXISTS (
    SELECT 1 FROM public.bookings b WHERE b.id = inspection_reports.booking_id AND b.assigned_to = auth.uid()
  ));
CREATE POLICY "company admins create reports" ON public.inspection_reports
  FOR INSERT TO authenticated
  WITH CHECK (company_id IS NOT NULL AND public.has_company_role(auth.uid(), company_id, 'company_admin'));
CREATE POLICY "assigned staff create reports" ON public.inspection_reports
  FOR INSERT TO authenticated
  WITH CHECK (company_id IS NOT NULL AND EXISTS (
    SELECT 1 FROM public.bookings b WHERE b.id = inspection_reports.booking_id AND b.company_id = inspection_reports.company_id AND b.assigned_to = auth.uid()
  ));
CREATE POLICY "company admins update reports" ON public.inspection_reports
  FOR UPDATE TO authenticated
  USING (company_id IS NOT NULL AND public.has_company_role(auth.uid(), company_id, 'company_admin'))
  WITH CHECK (company_id IS NOT NULL AND public.has_company_role(auth.uid(), company_id, 'company_admin'));
CREATE POLICY "assigned staff update assigned reports" ON public.inspection_reports
  FOR UPDATE TO authenticated
  USING (company_id IS NOT NULL AND EXISTS (
    SELECT 1 FROM public.bookings b WHERE b.id = inspection_reports.booking_id AND b.assigned_to = auth.uid()
  ))
  WITH CHECK (company_id IS NOT NULL AND EXISTS (
    SELECT 1 FROM public.bookings b WHERE b.id = inspection_reports.booking_id AND b.company_id = inspection_reports.company_id AND b.assigned_to = auth.uid()
  ));

DROP POLICY IF EXISTS "report photos read" ON public.report_photos;
CREATE POLICY "report photos read" ON public.report_photos
  FOR SELECT TO authenticated
  USING (EXISTS (
    SELECT 1 FROM public.inspection_reports r
    WHERE r.id = report_id AND (
      (r.user_id = auth.uid() AND r.published)
      OR public.has_role(auth.uid(),'admin')
      OR (r.company_id IS NOT NULL AND public.has_company_role(auth.uid(), r.company_id, 'company_admin'))
      OR EXISTS (SELECT 1 FROM public.bookings b WHERE b.id = r.booking_id AND b.assigned_to = auth.uid())
    )
  ));
CREATE POLICY "provider staff write assigned report photos" ON public.report_photos
  FOR INSERT TO authenticated
  WITH CHECK (EXISTS (
    SELECT 1 FROM public.inspection_reports r
    WHERE r.id = report_id AND (
      public.has_role(auth.uid(),'admin')
      OR (r.company_id IS NOT NULL AND public.has_company_role(auth.uid(), r.company_id, 'company_admin'))
      OR EXISTS (SELECT 1 FROM public.bookings b WHERE b.id = r.booking_id AND b.assigned_to = auth.uid())
    )
  ));
CREATE POLICY "provider staff update assigned report photos" ON public.report_photos
  FOR UPDATE TO authenticated
  USING (EXISTS (
    SELECT 1 FROM public.inspection_reports r
    WHERE r.id = report_id AND (
      public.has_role(auth.uid(),'admin')
      OR (r.company_id IS NOT NULL AND public.has_company_role(auth.uid(), r.company_id, 'company_admin'))
      OR EXISTS (SELECT 1 FROM public.bookings b WHERE b.id = r.booking_id AND b.assigned_to = auth.uid())
    )
  ))
  WITH CHECK (EXISTS (
    SELECT 1 FROM public.inspection_reports r
    WHERE r.id = report_id AND (
      public.has_role(auth.uid(),'admin')
      OR (r.company_id IS NOT NULL AND public.has_company_role(auth.uid(), r.company_id, 'company_admin'))
      OR EXISTS (SELECT 1 FROM public.bookings b WHERE b.id = r.booking_id AND b.assigned_to = auth.uid())
    )
  ));