supabase_schema.sql

-- Supabase Database Schema for Species Storage
-- Run this in your Supabase SQL Editor to create the required tables

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create species table (combined mushrooms and plants)
CREATE TABLE IF NOT EXISTS public.species (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  sno INTEGER NOT NULL,
  species_name TEXT NOT NULL,
  species_type TEXT NOT NULL CHECK (species_type IN ('plant', 'mushroom')),
  location TEXT NOT NULL,
  latitude NUMERIC NOT NULL,
  longitude NUMERIC NOT NULL,
  added_by TEXT DEFAULT 'system',
  notes TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  UNIQUE(species_name, location)
);

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_species_type ON public.species(species_type);
CREATE INDEX IF NOT EXISTS idx_species_name ON public.species(species_name);
CREATE INDEX IF NOT EXISTS idx_location ON public.species(location);

-- Create recognition logs table
CREATE TABLE IF NOT EXISTS public.recognition_logs (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  species_name TEXT NOT NULL,
  confidence NUMERIC NOT NULL,
  location TEXT DEFAULT 'N/A',
  latitude TEXT DEFAULT 'N/A',
  longitude TEXT DEFAULT 'N/A',
  status TEXT NOT NULL,
  identified_by TEXT DEFAULT 'BioCLIP',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

-- Create new species submissions table
CREATE TABLE IF NOT EXISTS public.species_submissions (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  species_name TEXT NOT NULL,
  species_type TEXT NOT NULL CHECK (species_type IN ('plant', 'mushroom')),
  location TEXT NOT NULL,
  user_id TEXT,
  user_email TEXT,
  notes TEXT,
  image_url TEXT,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

-- Enable Row Level Security (RLS) - Public read access for species
ALTER TABLE public.species ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.recognition_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.species_submissions ENABLE ROW LEVEL SECURITY;

-- Allow public read access to species (everyone can view MCC species)
CREATE POLICY "Species are viewable by everyone"
  ON public.species FOR SELECT
  USING (true);

-- Allow public insert for new species submissions (will be reviewed)
CREATE POLICY "Anyone can submit new species"
  ON public.species_submissions FOR INSERT
  WITH CHECK (true);

-- Allow public read of own submissions
CREATE POLICY "Anyone can view submissions"
  ON public.species_submissions FOR SELECT
  USING (true);

-- Allow public insert for recognition logs
CREATE POLICY "Anyone can log recognitions"
  ON public.recognition_logs FOR INSERT
  WITH CHECK (true);

-- Create function to handle updated_at
CREATE OR REPLACE FUNCTION handle_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create triggers for updated_at
DROP TRIGGER IF EXISTS set_updated_at ON public.species;
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON public.species
  FOR EACH ROW
  EXECUTE FUNCTION handle_updated_at();

DROP TRIGGER IF EXISTS set_updated_at_submissions ON public.species_submissions;
CREATE TRIGGER set_updated_at_submissions
  BEFORE UPDATE ON public.species_submissions
  FOR EACH ROW
  EXECUTE FUNCTION handle_updated_at();

-- Success message
DO $$
BEGIN
  RAISE NOTICE 'Database schema created successfully!';
  RAISE NOTICE 'Next step: Run migrate_csv_to_supabase.py to import your existing data';
END $$;