-- 1. DROP (Clean Slate) drop table if exists route_logs; drop table if exists community_reports; -- 2. ROUTE LOGS (For Model Training) create table route_logs ( id bigint generated by default as identity primary key, created_at timestamp with time zone default timezone('utc'::text, now()) not null, user_email text default 'guest', start_point text, end_point text, fast_dist_km float, cool_dist_km float, dist_diff_km float, shade_gain_pct int, temp_c float, weather_station text ); -- 3. COMMUNITY REPORTS (For "Waze" Logic) create table community_reports ( id bigint generated by default as identity primary key, created_at timestamp with time zone default timezone('utc'::text, now()) not null, user_email text, -- Who reported it? lat float not null, lon float not null, report_type text not null, -- 'too_hot', 'blocked', 'shade_oasis', 'construction' description text, -- Validation Metrics (For the Algorithm) upvotes int default 0, -- Higher votes = More influence on route verified boolean default false -- Admin override ); -- 4. SECURITY (Open for MVP) alter table route_logs enable row level security; alter table community_reports enable row level security; create policy "Public Insert Logs" on route_logs for insert with check (true); create policy "Public Read Logs" on route_logs for select using (true); create policy "Public Insert Reports" on community_reports for insert with check (true); create policy "Public Read Reports" on community_reports for select using (true);