CREATE TABLE public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users ON DELETE CASCADE,
  display_name TEXT NOT NULL DEFAULT 'Utilizador KUYA',
  phone TEXT,
  avatar_url TEXT,
  is_verified BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT ON public.profiles TO anon;
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 POLICY "profiles_public_read" ON public.profiles FOR SELECT USING (true);
CREATE POLICY "profiles_insert_own" ON public.profiles FOR INSERT TO authenticated WITH CHECK (id = auth.uid());
CREATE POLICY "profiles_update_own" ON public.profiles FOR UPDATE TO authenticated USING (id = auth.uid()) WITH CHECK (id = auth.uid());

CREATE TABLE public.listings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  seller_id UUID REFERENCES auth.users ON DELETE CASCADE,
  seller_name TEXT NOT NULL DEFAULT 'Vendedor KUYA',
  seller_verified BOOLEAN NOT NULL DEFAULT false,
  title TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  price NUMERIC(14,2) NOT NULL DEFAULT 0,
  category TEXT NOT NULL DEFAULT 'other',
  condition TEXT NOT NULL DEFAULT 'used',
  province TEXT NOT NULL DEFAULT 'Luanda',
  municipality TEXT NOT NULL DEFAULT '',
  images TEXT[] NOT NULL DEFAULT '{}',
  instant_buy BOOLEAN NOT NULL DEFAULT false,
  is_promoted BOOLEAN NOT NULL DEFAULT false,
  stock INTEGER NOT NULL DEFAULT 1,
  status TEXT NOT NULL DEFAULT 'active',
  views INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX listings_category_idx ON public.listings (category);
CREATE INDEX listings_created_idx ON public.listings (created_at DESC);
GRANT SELECT ON public.listings TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.listings TO authenticated;
GRANT ALL ON public.listings TO service_role;
ALTER TABLE public.listings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "listings_public_read" ON public.listings FOR SELECT USING (status = 'active' OR seller_id = auth.uid());
CREATE POLICY "listings_insert_own" ON public.listings FOR INSERT TO authenticated WITH CHECK (seller_id = auth.uid());
CREATE POLICY "listings_update_own" ON public.listings FOR UPDATE TO authenticated USING (seller_id = auth.uid()) WITH CHECK (seller_id = auth.uid());
CREATE POLICY "listings_delete_own" ON public.listings FOR DELETE TO authenticated USING (seller_id = auth.uid());

CREATE TABLE public.favorites (
  user_id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,
  listing_id UUID NOT NULL REFERENCES public.listings ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, listing_id)
);
GRANT SELECT, INSERT, DELETE ON public.favorites TO authenticated;
GRANT ALL ON public.favorites TO service_role;
ALTER TABLE public.favorites ENABLE ROW LEVEL SECURITY;
CREATE POLICY "favorites_own" ON public.favorites FOR ALL TO authenticated USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());

CREATE TABLE public.orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  buyer_id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,
  contact_name TEXT NOT NULL,
  contact_phone TEXT NOT NULL,
  address TEXT NOT NULL,
  payment_method TEXT NOT NULL DEFAULT 'escrow',
  subtotal NUMERIC(14,2) NOT NULL DEFAULT 0,
  delivery_fee NUMERIC(14,2) NOT NULL DEFAULT 0,
  total NUMERIC(14,2) NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT ON public.orders TO authenticated;
GRANT ALL ON public.orders TO service_role;
ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY "orders_own_read" ON public.orders FOR SELECT TO authenticated USING (buyer_id = auth.uid());
CREATE POLICY "orders_own_insert" ON public.orders FOR INSERT TO authenticated WITH CHECK (buyer_id = auth.uid());

CREATE TABLE public.order_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID NOT NULL REFERENCES public.orders ON DELETE CASCADE,
  listing_id UUID REFERENCES public.listings ON DELETE SET NULL,
  title TEXT NOT NULL,
  unit_price NUMERIC(14,2) NOT NULL DEFAULT 0,
  quantity INTEGER NOT NULL DEFAULT 1,
  image_url TEXT
);
GRANT SELECT, INSERT ON public.order_items TO authenticated;
GRANT ALL ON public.order_items TO service_role;
ALTER TABLE public.order_items ENABLE ROW LEVEL SECURITY;
CREATE POLICY "order_items_own_read" ON public.order_items FOR SELECT TO authenticated USING (EXISTS (SELECT 1 FROM public.orders o WHERE o.id = order_id AND o.buyer_id = auth.uid()));
CREATE POLICY "order_items_own_insert" ON public.order_items FOR INSERT TO authenticated WITH CHECK (EXISTS (SELECT 1 FROM public.orders o WHERE o.id = order_id AND o.buyer_id = auth.uid()));

INSERT INTO public.listings (seller_name, seller_verified, title, description, price, category, condition, province, municipality, images, instant_buy, is_promoted, stock, views) VALUES
('ResiVenda Imóveis', true, 'Apartamento T3 remodelado na Ilha do Bispo', 'Apartamento T3 totalmente remodelado, 128 m², 2 casas de banho, cozinha equipada e varanda com vista. Condomínio com segurança 24h.', 42500000, 'property', 'new', 'Luanda', 'Ilha do Bispo', ARRAY['/__l5e/assets-v1/ee7230bb-1195-4b51-b981-e2ca4133314b/apartment.jpg'], false, true, 1, 482),
('AutoElite LDA', true, 'Toyota Corolla 2019 1.8 VVT-i', 'Sedan em excelente estado, 42.000 km, revisões feitas na marca, ar condicionado, GPS e sensores de estacionamento.', 14800000, 'vehicles', 'used', 'Luanda', 'Talatona', ARRAY['/__l5e/assets-v1/56d9e9cc-e8b2-4760-acc1-776242594a25/corolla.jpg'], false, true, 1, 361),
('TecnoLuanda', true, 'Telemóvel 256GB Titânio — selado', 'Novo, selado na caixa, com garantia de 12 meses da loja. Entrega em Luanda no mesmo dia.', 685000, 'electronics', 'new', 'Luanda', 'Maianga', ARRAY['/__l5e/assets-v1/368bfd6a-80b8-4466-8ac2-97ea304cccae/phone.jpg'], true, true, 6, 1204),
('Atelier Kianda', true, 'Vestido em chitenge exclusivo', 'Peça única cosida à mão em tecido chitenge de alta qualidade. Tamanhos S, M e L disponíveis.', 95000, 'fashion', 'new', 'Luanda', 'Alvalade', ARRAY['/__l5e/assets-v1/1f832a81-5c18-44c9-adf2-06dedcb0f560/dress.jpg'], true, false, 12, 288),
('TecnoLuanda', true, 'Portátil 13" 512GB SSD', 'Portátil ultraleve, 16GB de RAM, bateria com autonomia de 15 horas. Ideal para trabalho e estudo.', 1150000, 'electronics', 'new', 'Luanda', 'Cacuaco', ARRAY['/__l5e/assets-v1/ce0d7ca4-59ce-4ed0-a536-d51c44106baf/laptop.jpg'], true, false, 4, 517),
('LuandaHomes', true, 'Duplex T2 com jardim no Benfica', 'Duplex de 2 quartos com jardim privado, garagem para 2 carros e área de churrasco.', 18200000, 'property', 'used', 'Luanda', 'Benfica', ARRAY['/__l5e/assets-v1/35de0db5-fab1-4cc0-bb3b-aae424bb9fd5/duplex.jpg'], false, false, 1, 203),
('Casa & Barro', false, 'Jogo de 6 canecas de cerâmica artesanal', 'Canecas feitas à mão, esmalte cor de creme, resistentes à máquina de lavar.', 42000, 'home', 'new', 'Benguela', 'Benguela', ARRAY['/__l5e/assets-v1/6c9cf362-6f64-4d91-893d-b237ef48b1f3/mugs.jpg'], true, false, 25, 96),
('VendeMais Auto', true, 'Pick-up 4x4 Double Cab a gasóleo', 'Pick-up robusta 4x4, 96.000 km, pneus novos, perfeita para obra e viagens fora de Luanda.', 19800000, 'vehicles', 'used', 'Huíla', 'Lubango', ARRAY['/__l5e/assets-v1/9b7aa785-42fe-4a55-9b2d-091f19995c5c/pickup.jpg'], false, false, 1, 174),
('TecnoLuanda', true, 'Telemóvel 128GB — como novo', 'Aparelho usado durante 4 meses, sem riscos, com carregador e capa incluídos.', 385000, 'electronics', 'used', 'Luanda', 'Rangel', ARRAY['/__l5e/assets-v1/368bfd6a-80b8-4466-8ac2-97ea304cccae/phone.jpg'], true, false, 2, 344),
('Atelier Kianda', false, 'Vestido de festa em tecido africano', 'Corte moderno, tecido leve, ideal para casamentos e eventos.', 78000, 'fashion', 'new', 'Luanda', 'Kilamba', ARRAY['/__l5e/assets-v1/1f832a81-5c18-44c9-adf2-06dedcb0f560/dress.jpg'], true, false, 8, 122),
('KilambaHomes', true, 'T2 no Kilamba — arrendamento mensal', 'Apartamento T2 mobilado, 5.º andar com elevador, água e luz incluídas.', 150000, 'property', 'used', 'Luanda', 'Kilamba', ARRAY['/__l5e/assets-v1/ee7230bb-1195-4b51-b981-e2ca4133314b/apartment.jpg'], false, false, 1, 640),
('Casa & Barro', false, 'Mesa de jantar em madeira maciça', 'Mesa artesanal para 6 pessoas em madeira de mussivi tratada.', 320000, 'home', 'new', 'Luanda', 'Viana', ARRAY['/__l5e/assets-v1/6c9cf362-6f64-4d91-893d-b237ef48b1f3/mugs.jpg'], false, false, 3, 88);