-- Migration 005: Schema Separation for Dev/Testing/Production Environments
-- This migration creates separate schemas to avoid data collision between environments.
-- Each service should use the DB_SCHEMA environment variable to set search_path.

-- ============================================
-- PART 1: Create Environment Schemas
-- ============================================

-- Development schema (default)
CREATE SCHEMA IF NOT EXISTS dev;

-- Testing/QA schema
CREATE SCHEMA IF NOT EXISTS testing;

-- Production schema
CREATE SCHEMA IF NOT EXISTS prod;

-- ============================================
-- PART 2: Grant Permissions
-- ============================================

-- Grant usage on all schemas to the application user
-- Note: Adjust 'user' to match your DB_USER if different
DO $$
BEGIN
    -- Grant permissions to the default user
    EXECUTE format('GRANT ALL ON SCHEMA dev TO %I', current_user);
    EXECUTE format('GRANT ALL ON SCHEMA testing TO %I', current_user);
    EXECUTE format('GRANT ALL ON SCHEMA prod TO %I', current_user);
END $$;

-- ============================================
-- PART 3: Create Tables in Each Schema
-- ============================================

-- Note: The following creates the base tables in each schema.
-- This ensures each environment has isolated data.

-- Function to replicate tables from public schema to a target schema
CREATE OR REPLACE FUNCTION clone_tables_to_schema(target_schema TEXT)
RETURNS VOID AS $$
DECLARE
    tbl RECORD;
    sql_stmt TEXT;
BEGIN
    -- Clone each table from public schema
    FOR tbl IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = 'public'
        AND tablename NOT LIKE 'pg_%'
    LOOP
        -- Create table if not exists (structure only)
        sql_stmt := format(
            'CREATE TABLE IF NOT EXISTS %I.%I (LIKE public.%I INCLUDING ALL)',
            target_schema, tbl.tablename, tbl.tablename
        );
        EXECUTE sql_stmt;
    END LOOP;

    -- Clone sequences
    FOR tbl IN
        SELECT sequence_name
        FROM information_schema.sequences
        WHERE sequence_schema = 'public'
    LOOP
        sql_stmt := format(
            'CREATE SEQUENCE IF NOT EXISTS %I.%I',
            target_schema, tbl.sequence_name
        );
        BEGIN
            EXECUTE sql_stmt;
        EXCEPTION WHEN duplicate_table THEN
            -- Sequence already exists, skip
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Clone tables to each schema
SELECT clone_tables_to_schema('dev');
SELECT clone_tables_to_schema('testing');
SELECT clone_tables_to_schema('prod');

-- Copy existing data from public to dev schema (for development continuity)
DO $$
DECLARE
    tbl RECORD;
    sql_stmt TEXT;
BEGIN
    FOR tbl IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = 'public'
        AND tablename NOT LIKE 'pg_%'
    LOOP
        -- Copy data only if target table is empty
        sql_stmt := format(
            'INSERT INTO dev.%I SELECT * FROM public.%I WHERE NOT EXISTS (SELECT 1 FROM dev.%I LIMIT 1)',
            tbl.tablename, tbl.tablename, tbl.tablename
        );
        BEGIN
            EXECUTE sql_stmt;
        EXCEPTION WHEN OTHERS THEN
            -- Log and continue if copy fails
            RAISE NOTICE 'Could not copy data for table %: %', tbl.tablename, SQLERRM;
        END;
    END LOOP;
END $$;

-- ============================================
-- PART 4: Cleanup
-- ============================================

-- Drop the helper function
DROP FUNCTION IF EXISTS clone_tables_to_schema(TEXT);

-- ============================================
-- USAGE NOTES:
-- ============================================
-- To use a specific schema in your Go service, set search_path in the connection:
--
-- connStr := fmt.Sprintf(
--     "user=%s password=%s dbname=%s host=%s sslmode=%s search_path=%s",
--     user, password, name, host, sslMode, schema
-- )
--
-- Or execute after connection:
-- db.Exec(fmt.Sprintf("SET search_path TO %s", schema))
--
-- Environment Variable: DB_SCHEMA (dev, testing, prod)