-- ============ COMPANY ROLES ============
CREATE TYPE public.company_role AS ENUM ('company_admin','inspector','technician','agent');
CREATE TYPE public.company_status AS ENUM ('pending','approved','suspended');

-- ============ COMPANIES ============
CREATE TABLE public.companies (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name text NOT NULL,
  slug text NOT NULL UNIQUE,
  logo_url text,
  phone text,
  email text,
  description text,
  service_areas text[] NOT NULL DEFAULT '{}',
  services_offered text[] NOT NULL DEFAULT '{}',
  status public.company_status NOT NULL DEFAULT 'pending',
  rating numeric(2,1),
  review_count integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.companies TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.companies TO authenticated;
GRANT ALL ON public.companies TO service_role;
ALTER TABLE public.companies ENABLE ROW LEVEL SECURITY;

CREATE TABLE public.company_members (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES public.companies(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role public.company_role NOT NULL DEFAULT 'inspector',
  full_name text,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (company_id, user_id)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.company_members TO authenticated;
GRANT ALL ON public.company_members TO service_role;
ALTER TABLE public.company_members ENABLE ROW LEVEL SECURITY;

-- ============ HELPERS ============
CREATE OR REPLACE FUNCTION public.user_company_ids(_user_id uuid)
RETURNS SETOF uuid LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT company_id FROM public.company_members WHERE user_id = _user_id AND active
$$;

CREATE OR REPLACE FUNCTION public.is_company_member(_user_id uuid, _company_id uuid)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.company_members
    WHERE user_id = _user_id AND company_id = _company_id AND active
  )
$$;

CREATE OR REPLACE FUNCTION public.has_company_role(_user_id uuid, _company_id uuid, _role public.company_role)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.company_members
    WHERE user_id = _user_id AND company_id = _company_id AND role = _role AND active
  )
$$;

REVOKE EXECUTE ON FUNCTION public.user_company_ids(uuid) FROM anon, public;
REVOKE EXECUTE ON FUNCTION public.is_company_member(uuid, uuid) FROM anon, public;
REVOKE EXECUTE ON FUNCTION public.has_company_role(uuid, uuid, public.company_role) FROM anon, public;
GRANT EXECUTE ON FUNCTION public.user_company_ids(uuid) TO authenticated;
GRANT EXECUTE ON FUNCTION public.is_company_member(uuid, uuid) TO authenticated;
GRANT EXECUTE ON FUNCTION public.has_company_role(uuid, uuid, public.company_role) TO authenticated;

-- ============ COMPANY POLICIES ============
CREATE POLICY "approved companies are public" ON public.companies
  FOR SELECT USING (status = 'approved');
CREATE POLICY "members read own company" ON public.companies
  FOR SELECT TO authenticated
  USING (owner_id = auth.uid() OR public.is_company_member(auth.uid(), id) OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "users create own company" ON public.companies
  FOR INSERT TO authenticated WITH CHECK (owner_id = auth.uid());
CREATE POLICY "company admins update own company" ON public.companies
  FOR UPDATE TO authenticated
  USING (owner_id = auth.uid() OR public.has_company_role(auth.uid(), id, 'company_admin') OR public.has_role(auth.uid(),'admin'))
  WITH CHECK (owner_id = auth.uid() OR public.has_company_role(auth.uid(), id, 'company_admin') OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "super admin deletes companies" ON public.companies
  FOR DELETE TO authenticated USING (public.has_role(auth.uid(),'admin'));

-- prevent providers from self-approving their company
CREATE OR REPLACE FUNCTION public.companies_guard()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF NOT public.has_role(auth.uid(),'admin') THEN
    IF TG_OP = 'INSERT' THEN
      NEW.status := 'pending';
    ELSE
      NEW.status := OLD.status;
      NEW.rating := OLD.rating;
      NEW.review_count := OLD.review_count;
      NEW.owner_id := OLD.owner_id;
    END IF;
  END IF;
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;
CREATE TRIGGER companies_guard BEFORE INSERT OR UPDATE ON public.companies
  FOR EACH ROW EXECUTE FUNCTION public.companies_guard();

-- owner automatically becomes company admin
CREATE OR REPLACE FUNCTION public.companies_add_owner()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  INSERT INTO public.company_members (company_id, user_id, role)
  VALUES (NEW.id, NEW.owner_id, 'company_admin')
  ON CONFLICT DO NOTHING;
  RETURN NEW;
END;
$$;
CREATE TRIGGER companies_add_owner AFTER INSERT ON public.companies
  FOR EACH ROW EXECUTE FUNCTION public.companies_add_owner();

REVOKE EXECUTE ON FUNCTION public.companies_guard() FROM anon, authenticated, public;
REVOKE EXECUTE ON FUNCTION public.companies_add_owner() FROM anon, authenticated, public;

-- ============ COMPANY MEMBER POLICIES ============
CREATE POLICY "staff read own company team" ON public.company_members
  FOR SELECT TO authenticated
  USING (user_id = auth.uid() OR public.is_company_member(auth.uid(), company_id) OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "company admins manage staff" ON public.company_members
  FOR INSERT TO authenticated
  WITH CHECK (public.has_company_role(auth.uid(), company_id, 'company_admin') OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "company admins update staff" ON public.company_members
  FOR UPDATE TO authenticated
  USING (public.has_company_role(auth.uid(), company_id, 'company_admin') OR public.has_role(auth.uid(),'admin'))
  WITH CHECK (public.has_company_role(auth.uid(), company_id, 'company_admin') OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "company admins remove staff" ON public.company_members
  FOR DELETE TO authenticated
  USING (public.has_company_role(auth.uid(), company_id, 'company_admin') OR public.has_role(auth.uid(),'admin'));

CREATE TRIGGER company_members_touch BEFORE UPDATE ON public.company_members
  FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

-- ============ BOOKINGS / REPORTS COMPANY LINKS ============
ALTER TABLE public.bookings
  ADD COLUMN IF NOT EXISTS company_id uuid REFERENCES public.companies(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS assigned_to uuid REFERENCES auth.users(id) ON DELETE SET NULL;

CREATE POLICY "company staff read own bookings" ON public.bookings
  FOR SELECT TO authenticated
  USING (company_id IS NOT NULL AND public.is_company_member(auth.uid(), company_id));
CREATE POLICY "company staff update own bookings" ON public.bookings
  FOR UPDATE TO authenticated
  USING (company_id IS NOT NULL AND public.is_company_member(auth.uid(), company_id))
  WITH CHECK (company_id IS NOT NULL AND public.is_company_member(auth.uid(), company_id));

ALTER TABLE public.inspection_reports
  ADD COLUMN IF NOT EXISTS company_id uuid REFERENCES public.companies(id) ON DELETE SET NULL;

CREATE POLICY "company staff read own reports" ON public.inspection_reports
  FOR SELECT TO authenticated
  USING (company_id IS NOT NULL AND public.is_company_member(auth.uid(), company_id));
CREATE POLICY "company staff write own reports" ON public.inspection_reports
  FOR INSERT TO authenticated
  WITH CHECK (company_id IS NOT NULL AND public.is_company_member(auth.uid(), company_id));
CREATE POLICY "company staff update own reports" ON public.inspection_reports
  FOR UPDATE TO authenticated
  USING (company_id IS NOT NULL AND public.is_company_member(auth.uid(), company_id))
  WITH CHECK (company_id IS NOT NULL AND public.is_company_member(auth.uid(), company_id));

-- ============ SCAN MEDIA ============
ALTER TABLE public.mold_scans
  ADD COLUMN IF NOT EXISTS media_type text NOT NULL DEFAULT 'photo',
  ADD COLUMN IF NOT EXISTS video_url text,
  ADD COLUMN IF NOT EXISTS frames_analyzed integer,
  ADD COLUMN IF NOT EXISTS thermal_image_url text;

-- ============ PLATFORM SETTINGS ============
CREATE TABLE public.app_settings (
  key text PRIMARY KEY,
  value jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.app_settings TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.app_settings TO authenticated;
GRANT ALL ON public.app_settings TO service_role;
ALTER TABLE public.app_settings ENABLE ROW LEVEL SECURITY;

CREATE POLICY "settings are readable" ON public.app_settings FOR SELECT USING (true);
CREATE POLICY "super admin writes settings" ON public.app_settings
  FOR ALL TO authenticated
  USING (public.has_role(auth.uid(),'admin'))
  WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE TRIGGER app_settings_touch BEFORE UPDATE ON public.app_settings
  FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

INSERT INTO public.app_settings (key, value) VALUES
  ('payments_enabled', 'false'::jsonb),
  ('ai_scan_free', 'true'::jsonb),
  ('ai_scan_price_ngn', '5000'::jsonb)
ON CONFLICT (key) DO NOTHING;

-- ============ AUDIT LOGS ============
CREATE TABLE public.audit_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  actor_id uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  action text NOT NULL,
  entity text,
  entity_id uuid,
  details jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT ON public.audit_logs TO authenticated;
GRANT ALL ON public.audit_logs TO service_role;
ALTER TABLE public.audit_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "super admin reads audit logs" ON public.audit_logs
  FOR SELECT TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE POLICY "authenticated write audit logs" ON public.audit_logs
  FOR INSERT TO authenticated WITH CHECK (actor_id = auth.uid());

CREATE INDEX idx_company_members_user ON public.company_members(user_id);
CREATE INDEX idx_bookings_company ON public.bookings(company_id);
CREATE INDEX idx_reports_company ON public.inspection_reports(company_id);
CREATE INDEX idx_audit_created ON public.audit_logs(created_at DESC);