thrilltrack-explorer/tests/integration/schema-validation.test.ts

import { test, expect } from '@playwright/test';
import { createClient } from '@supabase/supabase-js';

/**
 * Schema Validation Tests
 * 
 * These tests validate that submission tables, version tables, and main entity tables
 * have consistent schemas to prevent field mismatches during the approval pipeline.
 * 
 * Critical validations:
 * 1. Submission tables must have all fields from main tables (except auto-generated)
 * 2. Version tables must have all fields from main tables plus version metadata
 * 3. Critical functions must reference correct column names
 * 4. Required NOT NULL fields must be present in all tables
 */

const supabase = createClient(
  'https://ydvtmnrszybqnbcqbdcy.supabase.co',
  process.env.SUPABASE_SERVICE_ROLE_KEY || ''
);

interface ColumnDefinition {
  column_name: string;
  data_type: string;
  is_nullable: string;
  column_default: string | null;
}

interface TableSchema {
  [columnName: string]: ColumnDefinition;
}

// Fields that are expected to be different or missing in submission tables
const EXCLUDED_FIELDS = [
  'id', // Submission tables have their own ID
  'created_at', // Managed differently in submissions
  'updated_at', // Managed differently in submissions
  'view_count_all', // Calculated fields not in submissions
  'view_count_30d',
  'view_count_7d',
  'average_rating',
  'review_count',
  'installations_count', // Only for ride_models
  'is_test_data', // Test data flag
];

// Version-specific metadata fields (expected to be extra in version tables)
const VERSION_METADATA_FIELDS = [
  'version_id',
  'version_number',
  'change_type',
  'change_reason',
  'is_current',
  'created_by',
  'created_at',
  'submission_id',
  'is_test_data',
];

async function getTableSchema(tableName: string): Promise<TableSchema> {
  const { data, error } = await supabase
    .from('information_schema.columns' as any)
    .select('column_name, data_type, is_nullable, column_default')
    .eq('table_schema', 'public')
    .eq('table_name', tableName);

  if (error) throw error;

  const schema: TableSchema = {};
  data?.forEach((col: any) => {
    schema[col.column_name] = col;
  });

  return schema;
}

function normalizeColumnName(name: string): string {
  // Handle known version table variations
  const mapping: { [key: string]: string } = {
    'height_requirement_cm': 'height_requirement',
    'gforce_max': 'max_g_force',
    'inversions_count': 'inversions',
    'height_meters': 'max_height_meters',
    'drop_meters': 'drop_height_meters',
  };

  return mapping[name] || name;
}

test.describe('Schema Validation - Entity Tables', () => {
  test('parks: submission table matches main table schema', async () => {
    const mainSchema = await getTableSchema('parks');
    const submissionSchema = await getTableSchema('park_submissions');

    const mismatches: string[] = [];
    const missingFields: string[] = [];

    // Check each field in main table exists in submission table
    for (const [fieldName, fieldDef] of Object.entries(mainSchema)) {
      if (EXCLUDED_FIELDS.includes(fieldName)) continue;

      if (!submissionSchema[fieldName]) {
        missingFields.push(fieldName);
      } else {
        // Check data type matches
        const mainType = fieldDef.data_type;
        const submissionType = submissionSchema[fieldName].data_type;
        
        if (mainType !== submissionType) {
          mismatches.push(
            `${fieldName}: main=${mainType}, submission=${submissionType}`
          );
        }
      }
    }

    expect(missingFields, 
      `park_submissions is missing fields: ${missingFields.join(', ')}`
    ).toHaveLength(0);
    
    expect(mismatches,
      `park_submissions has type mismatches: ${mismatches.join('; ')}`
    ).toHaveLength(0);
  });

  test('rides: submission table matches main table schema', async () => {
    const mainSchema = await getTableSchema('rides');
    const submissionSchema = await getTableSchema('ride_submissions');

    const mismatches: string[] = [];
    const missingFields: string[] = [];

    for (const [fieldName, fieldDef] of Object.entries(mainSchema)) {
      if (EXCLUDED_FIELDS.includes(fieldName)) continue;

      if (!submissionSchema[fieldName]) {
        missingFields.push(fieldName);
      } else {
        const mainType = fieldDef.data_type;
        const submissionType = submissionSchema[fieldName].data_type;
        
        if (mainType !== submissionType) {
          mismatches.push(
            `${fieldName}: main=${mainType}, submission=${submissionType}`
          );
        }
      }
    }

    expect(missingFields, 
      `ride_submissions is missing fields: ${missingFields.join(', ')}`
    ).toHaveLength(0);
    
    expect(mismatches,
      `ride_submissions has type mismatches: ${mismatches.join('; ')}`
    ).toHaveLength(0);
  });

  test('companies: submission table matches main table schema', async () => {
    const mainSchema = await getTableSchema('companies');
    const submissionSchema = await getTableSchema('company_submissions');

    const mismatches: string[] = [];
    const missingFields: string[] = [];

    for (const [fieldName, fieldDef] of Object.entries(mainSchema)) {
      if (EXCLUDED_FIELDS.includes(fieldName)) continue;

      if (!submissionSchema[fieldName]) {
        missingFields.push(fieldName);
      } else {
        const mainType = fieldDef.data_type;
        const submissionType = submissionSchema[fieldName].data_type;
        
        if (mainType !== submissionType) {
          mismatches.push(
            `${fieldName}: main=${mainType}, submission=${submissionType}`
          );
        }
      }
    }

    expect(missingFields, 
      `company_submissions is missing fields: ${missingFields.join(', ')}`
    ).toHaveLength(0);
    
    expect(mismatches,
      `company_submissions has type mismatches: ${mismatches.join('; ')}`
    ).toHaveLength(0);
  });

  test('ride_models: submission table matches main table schema', async () => {
    const mainSchema = await getTableSchema('ride_models');
    const submissionSchema = await getTableSchema('ride_model_submissions');

    const mismatches: string[] = [];
    const missingFields: string[] = [];

    for (const [fieldName, fieldDef] of Object.entries(mainSchema)) {
      if (EXCLUDED_FIELDS.includes(fieldName)) continue;

      if (!submissionSchema[fieldName]) {
        missingFields.push(fieldName);
      } else {
        const mainType = fieldDef.data_type;
        const submissionType = submissionSchema[fieldName].data_type;
        
        if (mainType !== submissionType) {
          mismatches.push(
            `${fieldName}: main=${mainType}, submission=${submissionType}`
          );
        }
      }
    }

    expect(missingFields, 
      `ride_model_submissions is missing fields: ${missingFields.join(', ')}`
    ).toHaveLength(0);
    
    expect(mismatches,
      `ride_model_submissions has type mismatches: ${mismatches.join('; ')}`
    ).toHaveLength(0);
  });
});

test.describe('Schema Validation - Version Tables', () => {
  test('park_versions: has all main table fields plus version metadata', async () => {
    const mainSchema = await getTableSchema('parks');
    const versionSchema = await getTableSchema('park_versions');

    const missingFields: string[] = [];

    // Check all main table fields exist in version table
    for (const [fieldName] of Object.entries(mainSchema)) {
      if (EXCLUDED_FIELDS.includes(fieldName)) continue;
      
      const normalizedName = normalizeColumnName(fieldName);
      if (!versionSchema[fieldName] && !versionSchema[normalizedName]) {
        missingFields.push(fieldName);
      }
    }

    // Check all version metadata fields exist
    const missingMetadata: string[] = [];
    for (const metaField of VERSION_METADATA_FIELDS) {
      if (!versionSchema[metaField]) {
        missingMetadata.push(metaField);
      }
    }

    expect(missingFields, 
      `park_versions is missing main table fields: ${missingFields.join(', ')}`
    ).toHaveLength(0);
    
    expect(missingMetadata,
      `park_versions is missing version metadata: ${missingMetadata.join(', ')}`
    ).toHaveLength(0);
  });

  test('ride_versions: has all main table fields plus version metadata', async () => {
    const mainSchema = await getTableSchema('rides');
    const versionSchema = await getTableSchema('ride_versions');

    const missingFields: string[] = [];

    for (const [fieldName] of Object.entries(mainSchema)) {
      if (EXCLUDED_FIELDS.includes(fieldName)) continue;
      
      const normalizedName = normalizeColumnName(fieldName);
      if (!versionSchema[fieldName] && !versionSchema[normalizedName]) {
        missingFields.push(fieldName);
      }
    }

    const missingMetadata: string[] = [];
    for (const metaField of VERSION_METADATA_FIELDS) {
      if (!versionSchema[metaField]) {
        missingMetadata.push(metaField);
      }
    }

    expect(missingFields, 
      `ride_versions is missing main table fields: ${missingFields.join(', ')}`
    ).toHaveLength(0);
    
    expect(missingMetadata,
      `ride_versions is missing version metadata: ${missingMetadata.join(', ')}`
    ).toHaveLength(0);
  });

  test('company_versions: has all main table fields plus version metadata', async () => {
    const mainSchema = await getTableSchema('companies');
    const versionSchema = await getTableSchema('company_versions');

    const missingFields: string[] = [];

    for (const [fieldName] of Object.entries(mainSchema)) {
      if (EXCLUDED_FIELDS.includes(fieldName)) continue;
      
      const normalizedName = normalizeColumnName(fieldName);
      if (!versionSchema[fieldName] && !versionSchema[normalizedName]) {
        missingFields.push(fieldName);
      }
    }

    const missingMetadata: string[] = [];
    for (const metaField of VERSION_METADATA_FIELDS) {
      if (!versionSchema[metaField]) {
        missingMetadata.push(metaField);
      }
    }

    expect(missingFields, 
      `company_versions is missing main table fields: ${missingFields.join(', ')}`
    ).toHaveLength(0);
    
    expect(missingMetadata,
      `company_versions is missing version metadata: ${missingMetadata.join(', ')}`
    ).toHaveLength(0);
  });

  test('ride_model_versions: has all main table fields plus version metadata', async () => {
    const mainSchema = await getTableSchema('ride_models');
    const versionSchema = await getTableSchema('ride_model_versions');

    const missingFields: string[] = [];

    for (const [fieldName] of Object.entries(mainSchema)) {
      if (EXCLUDED_FIELDS.includes(fieldName)) continue;
      
      const normalizedName = normalizeColumnName(fieldName);
      if (!versionSchema[fieldName] && !versionSchema[normalizedName]) {
        missingFields.push(fieldName);
      }
    }

    const missingMetadata: string[] = [];
    for (const metaField of VERSION_METADATA_FIELDS) {
      if (!versionSchema[metaField]) {
        missingMetadata.push(metaField);
      }
    }

    expect(missingFields, 
      `ride_model_versions is missing main table fields: ${missingFields.join(', ')}`
    ).toHaveLength(0);
    
    expect(missingMetadata,
      `ride_model_versions is missing version metadata: ${missingMetadata.join(', ')}`
    ).toHaveLength(0);
  });
});

test.describe('Schema Validation - Critical Fields', () => {
  test('rides table does NOT have ride_type column', async () => {
    const ridesSchema = await getTableSchema('rides');
    
    expect(ridesSchema['ride_type']).toBeUndefined();
  });

  test('rides table DOES have category column (NOT NULL)', async () => {
    const ridesSchema = await getTableSchema('rides');
    
    expect(ridesSchema['category']).toBeDefined();
    expect(ridesSchema['category'].is_nullable).toBe('NO');
  });

  test('ride_models table DOES have both category and ride_type columns', async () => {
    const rideModelsSchema = await getTableSchema('ride_models');
    
    expect(rideModelsSchema['category']).toBeDefined();
    expect(rideModelsSchema['category'].is_nullable).toBe('NO');
    expect(rideModelsSchema['ride_type']).toBeDefined();
  });

  test('all entity tables have required base fields', async () => {
    const requiredFields = ['id', 'name', 'slug', 'created_at', 'updated_at'];
    const tables = ['parks', 'rides', 'companies', 'ride_models'];

    for (const table of tables) {
      const schema = await getTableSchema(table);
      
      for (const field of requiredFields) {
        expect(schema[field], 
          `${table} is missing required field: ${field}`
        ).toBeDefined();
      }
    }
  });

  test('all submission tables have submission_id foreign key', async () => {
    const submissionTables = [
      'park_submissions',
      'ride_submissions', 
      'company_submissions',
      'ride_model_submissions',
      'photo_submissions',
    ];

    for (const table of submissionTables) {
      const schema = await getTableSchema(table);
      
      expect(schema['submission_id'], 
        `${table} is missing submission_id foreign key`
      ).toBeDefined();
      expect(schema['submission_id'].is_nullable).toBe('NO');
    }
  });

  test('all version tables have version metadata fields', async () => {
    const versionTables = [
      'park_versions',
      'ride_versions',
      'company_versions',
      'ride_model_versions',
    ];

    const requiredVersionFields = [
      'version_id',
      'version_number',
      'change_type',
      'is_current',
    ];

    for (const table of versionTables) {
      const schema = await getTableSchema(table);
      
      for (const field of requiredVersionFields) {
        expect(schema[field], 
          `${table} is missing required version field: ${field}`
        ).toBeDefined();
      }
    }
  });
});

test.describe('Schema Validation - Function Parameter Alignment', () => {
  test('verify create_entity_from_submission function exists', async () => {
    const { data, error } = await supabase
      .rpc('pg_get_functiondef', { 
        funcid: 'create_entity_from_submission'::any 
      } as any)
      .single();

    // Function should exist (will error if not)
    expect(error).toBeNull();
  });

  test('verify update_entity_from_submission function exists', async () => {
    const { data, error } = await supabase
      .rpc('pg_get_functiondef', { 
        funcid: 'update_entity_from_submission'::any 
      } as any)
      .single();

    // Function should exist (will error if not)
    expect(error).toBeNull();
  });

  test('verify process_approval_transaction function exists', async () => {
    const { data, error } = await supabase.rpc('pg_catalog.pg_function_is_visible', {
      funcid: 'process_approval_transaction'::any
    } as any);

    // Function should be visible
    expect(data).toBeTruthy();
  });
});

test.describe('Schema Validation - Known Field Name Variations', () => {
  test('ride_versions uses height_requirement_cm instead of height_requirement', async () => {
    const versionSchema = await getTableSchema('ride_versions');
    
    expect(versionSchema['height_requirement_cm']).toBeDefined();
    expect(versionSchema['height_requirement']).toBeUndefined();
  });

  test('ride_versions uses gforce_max instead of max_g_force', async () => {
    const versionSchema = await getTableSchema('ride_versions');
    
    expect(versionSchema['gforce_max']).toBeDefined();
    expect(versionSchema['max_g_force']).toBeUndefined();
  });

  test('ride_versions uses inversions_count instead of inversions', async () => {
    const versionSchema = await getTableSchema('ride_versions');
    
    expect(versionSchema['inversions_count']).toBeDefined();
    expect(versionSchema['inversions']).toBeUndefined();
  });

  test('ride_versions uses height_meters instead of max_height_meters', async () => {
    const versionSchema = await getTableSchema('ride_versions');
    
    expect(versionSchema['height_meters']).toBeDefined();
    expect(versionSchema['max_height_meters']).toBeUndefined();
  });

  test('ride_versions uses drop_meters instead of drop_height_meters', async () => {
    const versionSchema = await getTableSchema('ride_versions');
    
    expect(versionSchema['drop_meters']).toBeDefined();
    expect(versionSchema['drop_height_meters']).toBeUndefined();
  });
});

test.describe('Schema Validation - Submission Items', () => {
  test('submission_items has all required foreign key columns', async () => {
    const schema = await getTableSchema('submission_items');

    const requiredFKs = [
      'submission_id',
      'park_submission_id',
      'ride_submission_id',
      'company_submission_id',
      'ride_model_submission_id',
      'photo_submission_id',
      'timeline_event_submission_id',
      'depends_on', // For dependency chain
    ];

    for (const fk of requiredFKs) {
      expect(schema[fk], 
        `submission_items is missing FK: ${fk}`
      ).toBeDefined();
    }
  });

  test('submission_items has required metadata fields', async () => {
    const schema = await getTableSchema('submission_items');

    const requiredFields = [
      'item_type',
      'action_type',
      'status',
      'order_index',
    ];

    for (const field of requiredFields) {
      expect(schema[field], 
        `submission_items is missing field: ${field}`
      ).toBeDefined();
    }
  });
});