-- 1. Create Enums
CREATE TYPE user_role AS ENUM ('owner', 'marketer', 'buyer', 'tenant');
CREATE TYPE property_transaction_type AS ENUM ('sale', 'rent', 'marketing');
CREATE TYPE property_type AS ENUM ('villa', 'apartment', 'land', 'commercial');

-- 2. Profiles Table
CREATE TABLE profiles (
  id UUID REFERENCES auth.users ON DELETE CASCADE PRIMARY KEY,
  phone_number TEXT UNIQUE NOT NULL,
  full_name TEXT DEFAULT '',
  role user_role DEFAULT 'buyer',
  profile_image_url TEXT,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 3. Properties Table
CREATE TABLE properties (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  owner_id UUID REFERENCES profiles(id) ON DELETE CASCADE NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  price DECIMAL NOT NULL,
  area DECIMAL NOT NULL,
  location TEXT NOT NULL, -- District
  transaction_type property_transaction_type NOT NULL,
  property_type property_type NOT NULL,
  images TEXT[] DEFAULT '{}',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 4. RLS Policies
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE properties ENABLE ROW LEVEL SECURITY;

-- Profiles: Users can view all, but update only their own
CREATE POLICY "Public profiles are viewable by everyone" ON profiles FOR SELECT USING (true);
CREATE POLICY "Users can update their own profile" ON profiles FOR UPDATE USING (auth.uid() = id);

-- Properties: Viewable by everyone, insert/update by owners
CREATE POLICY "Properties are viewable by everyone" ON properties FOR SELECT USING (true);
CREATE POLICY "Users can insert their own properties" ON properties FOR INSERT WITH CHECK (auth.uid() = owner_id);
CREATE POLICY "Users can update their own properties" ON properties FOR UPDATE USING (auth.uid() = owner_id);

-- 5. Trigger for profile creation on auth signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, phone_number)
  VALUES (new.id, new.phone);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

-- 6. Storage Setup (Run these or use dashboard)
-- Note: inserting into storage.buckets requires superuser or using the dashboard
-- Here we provide the RLS for when they are created.

-- Create buckets via SQL if possible (requires extensions/permissions)
-- INSERT INTO storage.buckets (id, name, public) VALUES ('avatars', 'avatars', true);
-- INSERT INTO storage.buckets (id, name, public) VALUES ('properties', 'properties', true);

-- Storage RLS Policies
CREATE POLICY "Avatar images are public" ON storage.objects FOR SELECT USING (bucket_id = 'avatars');
CREATE POLICY "Users can upload their own avatar" ON storage.objects FOR INSERT WITH CHECK (bucket_id = 'avatars' AND auth.uid()::text = (storage.foldername(name))[1]);

CREATE POLICY "Property images are public" ON storage.objects FOR SELECT USING (bucket_id = 'properties');
CREATE POLICY "Users can upload property images" ON storage.objects FOR INSERT WITH CHECK (bucket_id = 'properties' AND auth.uid() IS NOT NULL);

-- 7. Views for Dashboard Statistics
CREATE OR REPLACE VIEW property_stats AS
SELECT 
  count(*) as total_properties,
  sum(price) filter (where transaction_type = 'sale') as total_sales_volume,
  count(*) filter (where transaction_type = 'rent') as total_rent_listings,
  location as district
FROM properties
GROUP BY location;