# Check Supabase connection
supabase status

# Link to remote project (if not linked)
supabase link --project-ref <project-ref>

# Pull latest schema
supabase db pull

# Create new migration
supabase migration new <migration_name>

# Example
supabase migration new add_farm_statistics_table

-- supabase/migrations/[timestamp]_[name].sql

-- ===========================================
-- Migration: [Description]
-- Created: [Date]
-- ===========================================

-- 1. Create Tables
CREATE TABLE IF NOT EXISTS [table_name] (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

  -- Business columns
  cvr_number VARCHAR(8) NOT NULL,
  name TEXT NOT NULL,

  -- Constraints
  CONSTRAINT valid_cvr CHECK (cvr_number ~ '^\d{8}$')
);

-- 2. Enable RLS
ALTER TABLE [table_name] ENABLE ROW LEVEL SECURITY;

-- 3. Create RLS Policies
CREATE POLICY "Allow public read access"
  ON [table_name]
  FOR SELECT
  USING (true);

-- 4. Create Indexes
CREATE INDEX idx_[table]_cvr ON [table_name] (cvr_number);
CREATE INDEX idx_[table]_created ON [table_name] (created_at);

-- 5. Create Triggers (if needed)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER [table]_updated_at
  BEFORE UPDATE ON [table_name]
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- 6. Comments
COMMENT ON TABLE [table_name] IS '[Description of table purpose]';
COMMENT ON COLUMN [table_name].[column] IS '[Description]';

CREATE POLICY "Allow public read access"
  ON [table_name]
  FOR SELECT
  USING (true);

CREATE POLICY "Allow authenticated read"
  ON [table_name]
  FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Users can only see own data"
  ON [table_name]
  FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Admins can do everything"
  ON [table_name]
  FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM user_roles
      WHERE user_id = auth.uid()
      AND role = 'admin'
    )
  );

-- Enable PostGIS (usually already enabled)
CREATE EXTENSION IF NOT EXISTS postgis;

-- Add geometry column
ALTER TABLE [table_name]
ADD COLUMN geom GEOMETRY(Point, 4326);

-- Or for polygons
ALTER TABLE [table_name]
ADD COLUMN boundary GEOMETRY(Polygon, 4326);

-- Create spatial index
CREATE INDEX idx_[table]_geom ON [table_name] USING GIST (geom);

-- Convert from Danish UTM to WGS84
SELECT ST_Transform(geom, 4326) FROM ...

-- Set SRID
SELECT ST_SetSRID(geom, 4326) FROM ...

-- Find points within polygon
SELECT * FROM farms
WHERE ST_Within(geom, (SELECT boundary FROM regions WHERE name = 'Jutland'));

-- Distance query (in meters)
SELECT *, ST_Distance(geom::geography, point::geography) as distance
FROM farms
WHERE ST_DWithin(geom::geography, point::geography, 10000)
ORDER BY distance;

-- Centroid of polygon
SELECT ST_Centroid(boundary) FROM fields;

-- B-tree (default, good for equality and range)
CREATE INDEX idx_name ON table (column);

-- GiST (for geometry)
CREATE INDEX idx_geom ON table USING GIST (geom);

-- GIN (for arrays, JSONB, full-text search)
CREATE INDEX idx_data ON table USING GIN (data_jsonb);

-- Partial index (filtered)
CREATE INDEX idx_active ON table (column) WHERE is_active = true;

-- Always index these columns
CREATE INDEX idx_[table]_cvr ON [table] (cvr_number);
CREATE INDEX idx_[table]_chr ON [table] (chr_number);
CREATE INDEX idx_[table]_bfe ON [table] (bfe_number);
CREATE INDEX idx_[table]_geom ON [table] USING GIST (geom);

-- Create materialized view
CREATE MATERIALIZED VIEW farm_statistics AS
SELECT
  cvr_number,
  COUNT(*) as field_count,
  SUM(area_ha) as total_area,
  array_agg(DISTINCT crop_type) as crop_types
FROM fields
GROUP BY cvr_number;

-- Create index on materialized view
CREATE UNIQUE INDEX idx_farm_stats_cvr ON farm_statistics (cvr_number);

-- Refresh (run periodically)
REFRESH MATERIALIZED VIEW CONCURRENTLY farm_statistics;

# Apply to local database
supabase db push

# Reset local database (caution: loses data)
supabase db reset

# Check migration status
supabase migration list

# Generate types from database schema
supabase gen types typescript --local > frontend/src/types/supabase.ts

# Or from remote
supabase gen types typescript --project-id <project-id> > frontend/src/types/supabase.ts

-- Migration to rollback previous change
DROP TABLE IF EXISTS [table_name];

-- up migration: 001_create_table.sql
CREATE TABLE ...

-- down migration: 001_drop_table.sql (keep separately)
DROP TABLE ...

# Check SQL syntax
supabase db lint

-- Temporarily check without RLS (for debugging)
SET LOCAL ROLE postgres;
SELECT * FROM [table];

CREATE EXTENSION IF NOT EXISTS postgis;