create_metadata_table.sql

-- Create metadata_sagar table for storing file processing metadata
-- Run this query in your Supabase SQL Editor

CREATE TABLE IF NOT EXISTS metadata_sagar (
    id BIGSERIAL PRIMARY KEY,
    original_filename TEXT NOT NULL,
    processed_file_location TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    file_type TEXT,
    metadata_payload JSONB,
    geom TEXT, -- WKT geometry string (e.g., POLYGON for bounding boxes)
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create an index on processed_file_location for faster lookups
CREATE INDEX IF NOT EXISTS idx_metadata_sagar_processed_file_location 
ON metadata_sagar(processed_file_location);

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

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

-- Optional: Enable Row Level Security (RLS) if needed
-- ALTER TABLE metadata_sagar 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 metadata_sagar" 
-- ON metadata_sagar FOR ALL 
-- USING (true) 
-- WITH CHECK (true);

COMMENT ON TABLE metadata_sagar IS 'Stores metadata for processed data files including original filename, processed file location, status, file type, metadata payload, and geometry information';