create_netcdf_table.sql

-- Create netcdf_files table for storing netCDF file metadata and compliance reports
-- Run this query in your Supabase SQL Editor

CREATE TABLE IF NOT EXISTS netcdf_files (
    id BIGSERIAL PRIMARY KEY,
    original_filename TEXT NOT NULL,
    file_location TEXT NOT NULL, -- Location in Supabase storage
    status TEXT NOT NULL DEFAULT 'pending',
    file_type TEXT DEFAULT 'netcdf',
    compliance_report JSONB, -- Full compliance report (similar structure to QC report)
    metadata_payload JSONB, -- Additional metadata (dimensions, variables, attributes)
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create an index on file_location for faster lookups
CREATE INDEX IF NOT EXISTS idx_netcdf_files_file_location 
ON netcdf_files(file_location);

-- Create an index on status for filtering
CREATE INDEX IF NOT EXISTS idx_netcdf_files_status 
ON netcdf_files(status);

-- Create an index on created_at for sorting
CREATE INDEX IF NOT EXISTS idx_netcdf_files_created_at 
ON netcdf_files(created_at DESC);

-- Create an index on original_filename for searching
CREATE INDEX IF NOT EXISTS idx_netcdf_files_original_filename 
ON netcdf_files(original_filename);

-- Optional: Enable Row Level Security (RLS) if needed
-- ALTER TABLE netcdf_files ENABLE ROW LEVEL SECURITY;

-- Optional: Create a policy to allow all operations (adjust as needed for your security requirements)
-- CREATE POLICY "Allow all operations on netcdf_files" 
-- ON netcdf_files FOR ALL 
-- USING (true) 
-- WITH CHECK (true);

COMMENT ON TABLE netcdf_files IS 'Stores netCDF files with compliance checker reports. Files are validated against CF/ACDD standards using IOOS Compliance Checker.';