File size: 1,544 Bytes
bb56ce1
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- 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);