-- ============= OBRA MATERIALS =============
CREATE TABLE public.obra_materials (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  goal_id UUID NOT NULL,
  name TEXT NOT NULL,
  unit TEXT NOT NULL DEFAULT 'un',
  total_qty NUMERIC NOT NULL DEFAULT 0,
  bought_qty NUMERIC NOT NULL DEFAULT 0,
  unit_price NUMERIC NOT NULL DEFAULT 0,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.obra_materials ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own materials" ON public.obra_materials FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users insert own materials" ON public.obra_materials FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users update own materials" ON public.obra_materials FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users delete own materials" ON public.obra_materials FOR DELETE USING (auth.uid() = user_id);
CREATE TRIGGER trg_obra_materials_updated_at BEFORE UPDATE ON public.obra_materials
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE INDEX idx_obra_materials_goal ON public.obra_materials(goal_id);

-- ============= OBRA PHOTOS =============
CREATE TABLE public.obra_photos (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  goal_id UUID NOT NULL,
  storage_path TEXT NOT NULL,
  phase TEXT,
  description TEXT,
  taken_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.obra_photos ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own photos" ON public.obra_photos FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users insert own photos" ON public.obra_photos FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users update own photos" ON public.obra_photos FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users delete own photos" ON public.obra_photos FOR DELETE USING (auth.uid() = user_id);
CREATE INDEX idx_obra_photos_goal ON public.obra_photos(goal_id);

-- ============= OBRA TEAM =============
CREATE TABLE public.obra_team (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  goal_id UUID NOT NULL,
  name TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'trabalhador',
  daily_rate NUMERIC NOT NULL DEFAULT 0,
  days_worked INTEGER NOT NULL DEFAULT 0,
  phone TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.obra_team ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own team" ON public.obra_team FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users insert own team" ON public.obra_team FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users update own team" ON public.obra_team FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users delete own team" ON public.obra_team FOR DELETE USING (auth.uid() = user_id);
CREATE TRIGGER trg_obra_team_updated_at BEFORE UPDATE ON public.obra_team
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE INDEX idx_obra_team_goal ON public.obra_team(goal_id);

-- ============= OBRA TIMELINE =============
CREATE TABLE public.obra_timeline (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  goal_id UUID NOT NULL,
  title TEXT NOT NULL,
  event_type TEXT NOT NULL DEFAULT 'nota',
  event_date TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.obra_timeline ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own timeline" ON public.obra_timeline FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users insert own timeline" ON public.obra_timeline FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users update own timeline" ON public.obra_timeline FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users delete own timeline" ON public.obra_timeline FOR DELETE USING (auth.uid() = user_id);
CREATE INDEX idx_obra_timeline_goal ON public.obra_timeline(goal_id);

-- ============= OBRA DIARY =============
CREATE TABLE public.obra_diary (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL,
  goal_id UUID NOT NULL,
  entry_date DATE NOT NULL DEFAULT CURRENT_DATE,
  team TEXT,
  hours NUMERIC NOT NULL DEFAULT 0,
  weather TEXT NOT NULL DEFAULT 'sol',
  temp_c NUMERIC,
  activities TEXT[] NOT NULL DEFAULT '{}',
  issues TEXT[] NOT NULL DEFAULT '{}',
  notes TEXT,
  photos_count INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'ok',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE public.obra_diary ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users view own diary" ON public.obra_diary FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users insert own diary" ON public.obra_diary FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users update own diary" ON public.obra_diary FOR UPDATE USING (auth.uid() = user_id);
CREATE POLICY "Users delete own diary" ON public.obra_diary FOR DELETE USING (auth.uid() = user_id);
CREATE TRIGGER trg_obra_diary_updated_at BEFORE UPDATE ON public.obra_diary
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
CREATE INDEX idx_obra_diary_goal ON public.obra_diary(goal_id);

-- ============= STORAGE BUCKET (private) =============
INSERT INTO storage.buckets (id, name, public) VALUES ('obra-photos', 'obra-photos', false)
ON CONFLICT (id) DO NOTHING;

CREATE POLICY "Users view own obra photos"
  ON storage.objects FOR SELECT
  USING (bucket_id = 'obra-photos' AND auth.uid()::text = (storage.foldername(name))[1]);

CREATE POLICY "Users upload own obra photos"
  ON storage.objects FOR INSERT
  WITH CHECK (bucket_id = 'obra-photos' AND auth.uid()::text = (storage.foldername(name))[1]);

CREATE POLICY "Users update own obra photos"
  ON storage.objects FOR UPDATE
  USING (bucket_id = 'obra-photos' AND auth.uid()::text = (storage.foldername(name))[1]);

CREATE POLICY "Users delete own obra photos"
  ON storage.objects FOR DELETE
  USING (bucket_id = 'obra-photos' AND auth.uid()::text = (storage.foldername(name))[1]);