-- ══════════════════════════════════════════════════════
-- SaFa Naturals IoT Farm · Supabase Schema
-- Run in: Supabase Dashboard → SQL Editor → New Query
-- ══════════════════════════════════════════════════════

-- 1. SENSOR READINGS (60-second interval, per room)
CREATE TABLE sensor_readings (
  id           BIGSERIAL PRIMARY KEY,
  created_at   TIMESTAMPTZ DEFAULT NOW(),
  room_id      TEXT NOT NULL,           -- 'A', 'B', 'C'
  temperature  NUMERIC(5,2),            -- °C
  humidity     NUMERIC(5,2),            -- %RH
  co2_ppm      INTEGER,                 -- ppm
  o2_pct       NUMERIC(4,2),            -- %
  airflow_ms   NUMERIC(4,2),            -- m/s
  vpd_kpa      NUMERIC(4,3),            -- kPa (calculated)
  light_lux    INTEGER,                 -- lux
  dew_point    NUMERIC(5,2)             -- °C (calculated)
);

-- 2. SUBSTRATE READINGS (5-minute interval, per room)
CREATE TABLE substrate_readings (
  id            BIGSERIAL PRIMARY KEY,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  room_id       TEXT NOT NULL,
  moisture_pct  NUMERIC(5,2),
  substrate_temp NUMERIC(5,2),
  substrate_ph  NUMERIC(4,2),
  water_ph      NUMERIC(4,2),
  water_ec_ms   NUMERIC(5,3),           -- mS/cm
  water_temp    NUMERIC(5,2),
  water_used_l  NUMERIC(7,3)
);

-- 3. PRODUCTION BATCHES
CREATE TABLE farm_batches (
  id              TEXT PRIMARY KEY,      -- e.g. 'BAT-2025-041'
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  species         TEXT NOT NULL,
  room_id         TEXT NOT NULL,
  inoculation_date DATE NOT NULL,
  substrate_kg    NUMERIC(6,3),          -- dry weight
  spawn_pct       NUMERIC(4,1),
  stage           TEXT DEFAULT 'Inoculated',
  colonisation_pct INTEGER DEFAULT 0,
  pin_date        DATE,
  expected_harvest DATE,
  status          TEXT DEFAULT 'active', -- active | completed | contaminated
  notes           TEXT
);

-- 4. HARVEST EVENTS
CREATE TABLE harvest_events (
  id          BIGSERIAL PRIMARY KEY,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  batch_id    TEXT REFERENCES farm_batches(id),
  flush_num   SMALLINT NOT NULL,
  harvest_date DATE NOT NULL,
  yield_kg    NUMERIC(7,3),
  be_pct      NUMERIC(5,2),             -- biological efficiency
  room_id     TEXT,
  notes       TEXT
);

-- 5. CONTAMINATION LOG
CREATE TABLE contamination_log (
  id            BIGSERIAL PRIMARY KEY,
  created_at    TIMESTAMPTZ DEFAULT NOW(),
  batch_id      TEXT REFERENCES farm_batches(id),
  room_id       TEXT,
  type          TEXT,                    -- Bacterial | Mould | Trichoderma
  bags_affected INTEGER,
  total_bags    INTEGER,
  action_taken  TEXT,
  resolved      BOOLEAN DEFAULT FALSE
);

-- 6. ENERGY LOG (15-minute interval)
CREATE TABLE energy_log (
  id          BIGSERIAL PRIMARY KEY,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  device_id   TEXT NOT NULL,            -- 'hvac-a', 'humidifier-b', etc.
  room_id     TEXT,
  watts       NUMERIC(8,2),
  is_on       BOOLEAN DEFAULT TRUE,
  kwh_total   NUMERIC(10,4)
);

-- 7. ALERT LOG
CREATE TABLE alert_log (
  id          BIGSERIAL PRIMARY KEY,
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  room_id     TEXT,
  severity    TEXT NOT NULL,            -- 'ok' | 'warn' | 'crit'
  parameter   TEXT,
  value       NUMERIC,
  threshold   NUMERIC,
  message     TEXT,
  resolved_at TIMESTAMPTZ
);

-- ── INDEXES for time-series queries ──
CREATE INDEX idx_sensor_room_time   ON sensor_readings (room_id, created_at DESC);
CREATE INDEX idx_substrate_room     ON substrate_readings (room_id, created_at DESC);
CREATE INDEX idx_harvest_batch      ON harvest_events (batch_id, flush_num);
CREATE INDEX idx_energy_device_time ON energy_log (device_id, created_at DESC);
CREATE INDEX idx_alerts_severity    ON alert_log (severity, created_at DESC);

-- ── ROW LEVEL SECURITY ──
ALTER TABLE sensor_readings ENABLE ROW LEVEL SECURITY;
ALTER TABLE farm_batches    ENABLE ROW LEVEL SECURITY;
ALTER TABLE harvest_events  ENABLE ROW LEVEL SECURITY;
ALTER TABLE energy_log      ENABLE ROW LEVEL SECURITY;
ALTER TABLE alert_log       ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users (admin) full access
CREATE POLICY "admin_all" ON sensor_readings   FOR ALL TO authenticated USING (true);
CREATE POLICY "admin_all" ON farm_batches      FOR ALL TO authenticated USING (true);
CREATE POLICY "admin_all" ON harvest_events    FOR ALL TO authenticated USING (true);
CREATE POLICY "admin_all" ON energy_log        FOR ALL TO authenticated USING (true);
CREATE POLICY "admin_all" ON alert_log         FOR ALL TO authenticated USING (true);