CREATE TYPE public.app_role AS ENUM ('admin','customer');
CREATE TYPE public.booking_status AS ENUM ('requested','confirmed','inspector_assigned','inspection_completed','treatment_scheduled','completed','cancelled');
CREATE TYPE public.quote_status AS ENUM ('draft','sent','accepted','declined');

CREATE TABLE public.profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name text,
  phone text,
  address text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE ON public.profiles TO authenticated;
GRANT ALL ON public.profiles TO service_role;
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

CREATE TABLE public.user_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role public.app_role NOT NULL,
  UNIQUE (user_id, role)
);
GRANT SELECT ON public.user_roles TO authenticated;
GRANT ALL ON public.user_roles TO service_role;
ALTER TABLE public.user_roles ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role public.app_role)
RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT EXISTS (SELECT 1 FROM public.user_roles WHERE user_id = _user_id AND role = _role)
$$;

CREATE POLICY "own profile read" ON public.profiles FOR SELECT TO authenticated USING (auth.uid() = id OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "own profile insert" ON public.profiles FOR INSERT TO authenticated WITH CHECK (auth.uid() = id);
CREATE POLICY "own profile update" ON public.profiles FOR UPDATE TO authenticated USING (auth.uid() = id OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "roles read own" ON public.user_roles FOR SELECT TO authenticated USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'));

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, phone)
  VALUES (NEW.id, NEW.raw_user_meta_data ->> 'full_name', NEW.raw_user_meta_data ->> 'phone')
  ON CONFLICT (id) DO NOTHING;
  INSERT INTO public.user_roles (user_id, role) VALUES (NEW.id, 'customer') ON CONFLICT DO NOTHING;
  RETURN NEW;
END; $$;
CREATE TRIGGER on_auth_user_created AFTER INSERT ON auth.users FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

CREATE OR REPLACE FUNCTION public.touch_updated_at() RETURNS trigger LANGUAGE plpgsql SET search_path = public AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;

CREATE TABLE public.services (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug text NOT NULL UNIQUE,
  name text NOT NULL,
  category text NOT NULL,
  short_description text NOT NULL,
  description text NOT NULL,
  benefits text[] NOT NULL DEFAULT '{}',
  process text[] NOT NULL DEFAULT '{}',
  price_from numeric,
  price_unit text,
  active boolean NOT NULL DEFAULT true,
  sort_order int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.services TO anon, authenticated;
GRANT ALL ON public.services TO service_role;
ALTER TABLE public.services ENABLE ROW LEVEL SECURITY;
CREATE POLICY "services public read" ON public.services FOR SELECT USING (active);
CREATE POLICY "services admin all" ON public.services FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.bookings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  service_id uuid REFERENCES public.services(id) ON DELETE SET NULL,
  service_name text NOT NULL,
  full_name text NOT NULL,
  phone text NOT NULL,
  email text NOT NULL,
  address text NOT NULL,
  gps_location text,
  property_type text,
  rooms int,
  preferred_date date,
  preferred_time text,
  notes text,
  photo_urls text[] NOT NULL DEFAULT '{}',
  status public.booking_status NOT NULL DEFAULT 'requested',
  inspector_name text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX bookings_user_idx ON public.bookings(user_id);
CREATE INDEX bookings_status_idx ON public.bookings(status);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.bookings TO authenticated;
GRANT ALL ON public.bookings TO service_role;
ALTER TABLE public.bookings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "bookings own read" ON public.bookings FOR SELECT TO authenticated USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "bookings own insert" ON public.bookings FOR INSERT TO authenticated WITH CHECK (user_id = auth.uid());
CREATE POLICY "bookings own update" ON public.bookings FOR UPDATE TO authenticated USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin')) WITH CHECK (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "bookings admin delete" ON public.bookings FOR DELETE TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE TRIGGER bookings_touch BEFORE UPDATE ON public.bookings FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

CREATE TABLE public.mold_scans (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  image_url text,
  verdict text NOT NULL,
  confidence int NOT NULL DEFAULT 0,
  severity text NOT NULL DEFAULT 'Low',
  factors text[] NOT NULL DEFAULT '{}',
  recommendations text[] NOT NULL DEFAULT '{}',
  summary text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX mold_scans_user_idx ON public.mold_scans(user_id);
GRANT SELECT, INSERT, DELETE ON public.mold_scans TO authenticated;
GRANT ALL ON public.mold_scans TO service_role;
ALTER TABLE public.mold_scans ENABLE ROW LEVEL SECURITY;
CREATE POLICY "scans own all" ON public.mold_scans FOR ALL TO authenticated USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin')) WITH CHECK (user_id = auth.uid());

CREATE TABLE public.quotations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  booking_id uuid REFERENCES public.bookings(id) ON DELETE SET NULL,
  reference text NOT NULL DEFAULT concat('QT-', to_char(now(),'YYMMDD'), '-', substr(gen_random_uuid()::text,1,4)),
  status public.quote_status NOT NULL DEFAULT 'draft',
  notes text,
  total numeric NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.quotations TO authenticated;
GRANT ALL ON public.quotations TO service_role;
ALTER TABLE public.quotations ENABLE ROW LEVEL SECURITY;
CREATE POLICY "quotes own read" ON public.quotations FOR SELECT TO authenticated USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "quotes own update" ON public.quotations FOR UPDATE TO authenticated USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin')) WITH CHECK (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "quotes insert" ON public.quotations FOR INSERT TO authenticated WITH CHECK (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "quotes admin delete" ON public.quotations FOR DELETE TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE TRIGGER quotations_touch BEFORE UPDATE ON public.quotations FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

CREATE TABLE public.quotation_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  quotation_id uuid NOT NULL REFERENCES public.quotations(id) ON DELETE CASCADE,
  description text NOT NULL,
  quantity numeric NOT NULL DEFAULT 1,
  unit_price numeric NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX quotation_items_q_idx ON public.quotation_items(quotation_id);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.quotation_items TO authenticated;
GRANT ALL ON public.quotation_items TO service_role;
ALTER TABLE public.quotation_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY "quote items read" ON public.quotation_items FOR SELECT TO authenticated USING (EXISTS (SELECT 1 FROM public.quotations q WHERE q.id = quotation_id AND (q.user_id = auth.uid() OR public.has_role(auth.uid(),'admin'))));
CREATE POLICY "quote items admin write" ON public.quotation_items FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.inspection_reports (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  booking_id uuid REFERENCES public.bookings(id) ON DELETE SET NULL,
  reference text NOT NULL DEFAULT concat('IR-', to_char(now(),'YYMMDD'), '-', substr(gen_random_uuid()::text,1,4)),
  inspection_date date NOT NULL DEFAULT current_date,
  inspector_name text NOT NULL,
  property_info text,
  areas_inspected text[] NOT NULL DEFAULT '{}',
  severity text,
  likely_causes text[] NOT NULL DEFAULT '{}',
  recommendations text[] NOT NULL DEFAULT '{}',
  treatment_performed text,
  signature_name text,
  published boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX reports_user_idx ON public.inspection_reports(user_id);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.inspection_reports TO authenticated;
GRANT ALL ON public.inspection_reports TO service_role;
ALTER TABLE public.inspection_reports ENABLE ROW LEVEL SECURITY;
CREATE POLICY "reports own read" ON public.inspection_reports FOR SELECT TO authenticated USING ((user_id = auth.uid() AND published) OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "reports admin write" ON public.inspection_reports FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE TRIGGER reports_touch BEFORE UPDATE ON public.inspection_reports FOR EACH ROW EXECUTE FUNCTION public.touch_updated_at();

CREATE TABLE public.report_photos (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  report_id uuid NOT NULL REFERENCES public.inspection_reports(id) ON DELETE CASCADE,
  url text NOT NULL,
  kind text NOT NULL DEFAULT 'before',
  caption text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX report_photos_report_idx ON public.report_photos(report_id);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.report_photos TO authenticated;
GRANT ALL ON public.report_photos TO service_role;
ALTER TABLE public.report_photos ENABLE ROW LEVEL SECURITY;
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'))));
CREATE POLICY "report photos admin write" ON public.report_photos FOR ALL TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));

CREATE TABLE public.reviews (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  author_name text NOT NULL,
  rating int NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment text,
  photo_urls text[] NOT NULL DEFAULT '{}',
  approved boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT ON public.reviews TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.reviews TO authenticated;
GRANT ALL ON public.reviews TO service_role;
ALTER TABLE public.reviews ENABLE ROW LEVEL SECURITY;
CREATE POLICY "reviews public read" ON public.reviews FOR SELECT USING (approved);
CREATE POLICY "reviews own read" ON public.reviews FOR SELECT TO authenticated USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'));
CREATE POLICY "reviews own insert" ON public.reviews FOR INSERT TO authenticated WITH CHECK (user_id = auth.uid());
CREATE POLICY "reviews admin write" ON public.reviews FOR UPDATE TO authenticated USING (public.has_role(auth.uid(),'admin')) WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE POLICY "reviews delete" ON public.reviews FOR DELETE TO authenticated USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'));

CREATE TABLE public.notifications (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  title text NOT NULL,
  body text,
  read boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX notifications_user_idx ON public.notifications(user_id);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.notifications TO authenticated;
GRANT ALL ON public.notifications TO service_role;
ALTER TABLE public.notifications ENABLE ROW LEVEL SECURITY;
CREATE POLICY "notifications own" ON public.notifications FOR ALL TO authenticated USING (user_id = auth.uid() OR public.has_role(auth.uid(),'admin')) WITH CHECK (user_id = auth.uid() OR public.has_role(auth.uid(),'admin'));

INSERT INTO public.services (slug,name,category,short_description,description,benefits,process,price_from,price_unit,sort_order) VALUES
('mold-inspection','Mold Inspection','Mold','Certified visual and moisture inspection of your property.','A thorough room-by-room inspection using moisture meters and visual assessment to locate mold growth and the moisture source feeding it.',ARRAY['Find hidden moisture sources','Documented findings with photos','Clear treatment roadmap'],ARRAY['Book an appointment','On-site inspection & moisture readings','Photo documentation','Written report with recommendations'],80,'per visit',1),
('mold-treatment','Mold Treatment','Mold','Targeted antimicrobial treatment of affected surfaces.','Professional treatment of contaminated surfaces with approved antimicrobial products, followed by preventive sealing.',ARRAY['Kills surface mold growth','Reduces musty odours','Preventive protective coating'],ARRAY['Containment setup','Surface treatment','Air scrubbing','Post-treatment verification'],150,'per room',2),
('mold-removal','Mold Removal','Mold','Safe removal of contaminated materials and remediation.','Full remediation including safe removal of affected drywall, wood or fabric, HEPA filtration and restoration guidance.',ARRAY['Removes the contamination source','HEPA filtration','Restoration guidance'],ARRAY['Containment & PPE setup','Material removal','HEPA vacuum & clean','Final clearance check'],250,'per area',3),
('house-cleaning','House Cleaning','Cleaning','Reliable routine cleaning for a healthier home.','Complete house cleaning covering living areas, bedrooms, kitchen and bathrooms with health-first products.',ARRAY['Healthier indoor air','Trained, vetted cleaners','Flexible scheduling'],ARRAY['Walkthrough','Room-by-room cleaning','Sanitising of high-touch areas','Quality check'],60,'per visit',4),
('deep-cleaning','Deep Cleaning','Cleaning','Intensive top-to-bottom clean of every surface.','A detailed deep clean reaching skirting boards, vents, behind appliances and other neglected areas where spores settle.',ARRAY['Removes built-up grime','Reduces allergens','Ideal before or after treatment'],ARRAY['Assessment','Detail cleaning','Disinfection','Final inspection'],120,'per visit',5),
('office-cleaning','Office Cleaning','Cleaning','Professional cleaning for workplaces of any size.','Scheduled office cleaning that keeps workspaces, kitchens and washrooms hygienic and presentable.',ARRAY['Healthier staff environment','Out-of-hours scheduling','Consistent standards'],ARRAY['Site survey','Cleaning schedule agreed','Regular service','Supervisor checks'],100,'per visit',6),
('furniture-cleaning','Furniture Cleaning','Cleaning','Upholstery, mattress and carpet deep cleaning.','Hot-water extraction and safe shampooing to lift stains, dust mites and mold spores from soft furnishings.',ARRAY['Extends furniture life','Removes dust mites','Fast drying methods'],ARRAY['Fabric test','Pre-treatment','Extraction clean','Deodorising'],50,'per item',7),
('fumigation','Fumigation','Fumigation','Certified fumigation for homes and commercial spaces.','Licensed fumigation treatment that eliminates infestations while keeping occupants safe with clear re-entry guidance.',ARRAY['Licensed technicians','Safe re-entry guidance','Long-lasting protection'],ARRAY['Site assessment','Preparation instructions','Fumigation','Clearance & advice'],180,'per property',8),
('pest-control','Pest Control','Fumigation','Targeted control of rodents, roaches and crawling pests.','Integrated pest management with baiting, spraying and entry-point sealing plus a follow-up visit.',ARRAY['Integrated pest management','Follow-up visit included','Child & pet conscious products'],ARRAY['Inspection','Treatment plan','Application','Follow-up visit'],90,'per visit',9),
('move-in-cleaning','Move-in Cleaning','Cleaning','Start fresh in a spotless, sanitised new home.','A full sanitising clean of an empty property before you move in, including mold spot checks.',ARRAY['Sanitised from day one','Mold spot check included','Cupboards & appliances cleaned'],ARRAY['Empty property walkthrough','Deep clean','Sanitising','Handover'],130,'per property',10),
('move-out-cleaning','Move-out Cleaning','Cleaning','Leave the property in inspection-ready condition.','End-of-tenancy cleaning designed to meet landlord and agency standards.',ARRAY['Deposit-friendly standard','Detailed checklist','Fast turnaround'],ARRAY['Checklist agreed','Deep clean','Final inspection','Report of works'],130,'per property',11);