thrilltrack-explorer/supabase/migrations/20251107145022_1372b55c-d704-40e0-9660-1dcc72ce6b90.sql

-- Phase 2: DATABASE INTEGRITY ENHANCEMENTS (CORRECTED)
-- Add UNIQUE constraints, trigger-based validation, and date precision validation

-- ============================================================================
-- STEP 2.1: ADD MISSING UNIQUE CONSTRAINTS
-- ============================================================================

-- First, check for and remove duplicates before adding constraints
-- Check parks.slug for duplicates
DO $$
DECLARE
  duplicate_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO duplicate_count
  FROM (
    SELECT slug, COUNT(*) as cnt
    FROM parks
    GROUP BY slug
    HAVING COUNT(*) > 1
  ) duplicates;
  
  IF duplicate_count > 0 THEN
    RAISE WARNING 'Found % duplicate slugs in parks table. These must be resolved manually before adding UNIQUE constraint.', duplicate_count;
  END IF;
END $$;

-- Check rides.slug for duplicates (per park)
DO $$
DECLARE
  duplicate_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO duplicate_count
  FROM (
    SELECT park_id, slug, COUNT(*) as cnt
    FROM rides
    GROUP BY park_id, slug
    HAVING COUNT(*) > 1
  ) duplicates;
  
  IF duplicate_count > 0 THEN
    RAISE WARNING 'Found % duplicate slugs (per park) in rides table. These must be resolved manually before adding UNIQUE constraint.', duplicate_count;
  END IF;
END $$;

-- Add UNIQUE constraint on parks.slug (globally unique)
ALTER TABLE parks 
  DROP CONSTRAINT IF EXISTS parks_slug_unique;

ALTER TABLE parks 
  ADD CONSTRAINT parks_slug_unique UNIQUE (slug);

-- Add UNIQUE constraint on rides.slug (unique per park)
ALTER TABLE rides 
  DROP CONSTRAINT IF EXISTS rides_slug_park_unique;

ALTER TABLE rides 
  ADD CONSTRAINT rides_slug_park_unique UNIQUE (park_id, slug);

-- Add UNIQUE constraint on companies.slug (globally unique)
ALTER TABLE companies
  DROP CONSTRAINT IF EXISTS companies_slug_unique;

ALTER TABLE companies
  ADD CONSTRAINT companies_slug_unique UNIQUE (slug);

-- Add UNIQUE constraint on ride_models.slug (unique per manufacturer)
ALTER TABLE ride_models
  DROP CONSTRAINT IF EXISTS ride_models_slug_manufacturer_unique;

ALTER TABLE ride_models
  ADD CONSTRAINT ride_models_slug_manufacturer_unique UNIQUE (manufacturer_id, slug);

-- ============================================================================
-- STEP 2.2: ADD DATE PRECISION VALIDATION
-- ============================================================================

-- Create CHECK constraints for date_precision columns to ensure valid values
-- Valid values: 'exact', 'month', 'year', 'decade', 'century', 'approximate'

-- Parks table
ALTER TABLE parks
  DROP CONSTRAINT IF EXISTS parks_opening_date_precision_check;

ALTER TABLE parks
  ADD CONSTRAINT parks_opening_date_precision_check
  CHECK (opening_date_precision IS NULL OR opening_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

ALTER TABLE parks
  DROP CONSTRAINT IF EXISTS parks_closing_date_precision_check;

ALTER TABLE parks
  ADD CONSTRAINT parks_closing_date_precision_check
  CHECK (closing_date_precision IS NULL OR closing_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

-- Rides table
ALTER TABLE rides
  DROP CONSTRAINT IF EXISTS rides_opening_date_precision_check;

ALTER TABLE rides
  ADD CONSTRAINT rides_opening_date_precision_check
  CHECK (opening_date_precision IS NULL OR opening_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

ALTER TABLE rides
  DROP CONSTRAINT IF EXISTS rides_closing_date_precision_check;

ALTER TABLE rides
  ADD CONSTRAINT rides_closing_date_precision_check
  CHECK (closing_date_precision IS NULL OR closing_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

-- Companies table
ALTER TABLE companies
  DROP CONSTRAINT IF EXISTS companies_founded_date_precision_check;

ALTER TABLE companies
  ADD CONSTRAINT companies_founded_date_precision_check
  CHECK (founded_date_precision IS NULL OR founded_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

-- Park submissions
ALTER TABLE park_submissions
  DROP CONSTRAINT IF EXISTS park_submissions_opening_date_precision_check;

ALTER TABLE park_submissions
  ADD CONSTRAINT park_submissions_opening_date_precision_check
  CHECK (opening_date_precision IS NULL OR opening_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

ALTER TABLE park_submissions
  DROP CONSTRAINT IF EXISTS park_submissions_closing_date_precision_check;

ALTER TABLE park_submissions
  ADD CONSTRAINT park_submissions_closing_date_precision_check
  CHECK (closing_date_precision IS NULL OR closing_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

-- Ride submissions
ALTER TABLE ride_submissions
  DROP CONSTRAINT IF EXISTS ride_submissions_opening_date_precision_check;

ALTER TABLE ride_submissions
  ADD CONSTRAINT ride_submissions_opening_date_precision_check
  CHECK (opening_date_precision IS NULL OR opening_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

ALTER TABLE ride_submissions
  DROP CONSTRAINT IF EXISTS ride_submissions_closing_date_precision_check;

ALTER TABLE ride_submissions
  ADD CONSTRAINT ride_submissions_closing_date_precision_check
  CHECK (closing_date_precision IS NULL OR closing_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

-- Company submissions
ALTER TABLE company_submissions
  DROP CONSTRAINT IF EXISTS company_submissions_founded_date_precision_check;

ALTER TABLE company_submissions
  ADD CONSTRAINT company_submissions_founded_date_precision_check
  CHECK (founded_date_precision IS NULL OR founded_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

-- Timeline event submissions
ALTER TABLE timeline_event_submissions
  DROP CONSTRAINT IF EXISTS timeline_event_submissions_event_date_precision_check;

ALTER TABLE timeline_event_submissions
  ADD CONSTRAINT timeline_event_submissions_event_date_precision_check
  CHECK (event_date_precision IS NULL OR event_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

-- Entity timeline events
ALTER TABLE entity_timeline_events
  DROP CONSTRAINT IF EXISTS entity_timeline_events_event_date_precision_check;

ALTER TABLE entity_timeline_events
  ADD CONSTRAINT entity_timeline_events_event_date_precision_check
  CHECK (event_date_precision IS NULL OR event_date_precision IN ('exact', 'month', 'year', 'decade', 'century', 'approximate'));

-- ============================================================================
-- STEP 2.3: ADD TRIGGER-BASED FOREIGN KEY VALIDATION
-- ============================================================================

-- Create trigger function to validate submission_items.depends_on
CREATE OR REPLACE FUNCTION validate_submission_item_dependency()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  -- If depends_on is not null, verify it references a valid item in same submission
  IF NEW.depends_on IS NOT NULL THEN
    IF NOT EXISTS (
      SELECT 1 FROM submission_items
      WHERE id = NEW.depends_on
      AND submission_id = NEW.submission_id
    ) THEN
      RAISE EXCEPTION 'Invalid depends_on reference: item % not found in submission %', 
        NEW.depends_on, NEW.submission_id;
    END IF;
    
    -- Also prevent circular dependencies by checking order_index
    IF EXISTS (
      SELECT 1 FROM submission_items
      WHERE id = NEW.depends_on
      AND submission_id = NEW.submission_id
      AND order_index >= NEW.order_index
    ) THEN
      RAISE EXCEPTION 'Circular dependency detected: dependent item must have lower order_index';
    END IF;
  END IF;
  
  RETURN NEW;
END;
$$;

-- Drop trigger if exists and recreate
DROP TRIGGER IF EXISTS validate_submission_item_dependency_trigger ON submission_items;

CREATE TRIGGER validate_submission_item_dependency_trigger
  BEFORE INSERT OR UPDATE ON submission_items
  FOR EACH ROW
  EXECUTE FUNCTION validate_submission_item_dependency();

-- ============================================================================
-- STEP 2.4: ADD DATA INTEGRITY CONSTRAINTS
-- ============================================================================

-- Ensure dates are logically consistent (opening before closing)
ALTER TABLE parks
  DROP CONSTRAINT IF EXISTS parks_dates_logical_check;

ALTER TABLE parks
  ADD CONSTRAINT parks_dates_logical_check
  CHECK (closing_date IS NULL OR opening_date IS NULL OR opening_date <= closing_date);

ALTER TABLE rides
  DROP CONSTRAINT IF EXISTS rides_dates_logical_check;

ALTER TABLE rides
  ADD CONSTRAINT rides_dates_logical_check
  CHECK (closing_date IS NULL OR opening_date IS NULL OR opening_date <= closing_date);

-- Ensure ratings are in valid range (1-5)
ALTER TABLE reviews
  DROP CONSTRAINT IF EXISTS reviews_rating_range_check;

ALTER TABLE reviews
  ADD CONSTRAINT reviews_rating_range_check
  CHECK (rating >= 1 AND rating <= 5);

-- Ensure numeric fields are non-negative where applicable
ALTER TABLE rides
  DROP CONSTRAINT IF EXISTS rides_numeric_positive_check;

ALTER TABLE rides
  ADD CONSTRAINT rides_numeric_positive_check
  CHECK (
    (height_requirement IS NULL OR height_requirement >= 0)
    AND (age_requirement IS NULL OR age_requirement >= 0)
    AND (max_speed_kmh IS NULL OR max_speed_kmh >= 0)
    AND (duration_seconds IS NULL OR duration_seconds >= 0)
    AND (capacity_per_hour IS NULL OR capacity_per_hour >= 0)
    AND (length_meters IS NULL OR length_meters >= 0)
    AND (max_height_meters IS NULL OR max_height_meters >= 0)
    AND (drop_height_meters IS NULL OR drop_height_meters >= 0)
    AND (inversions IS NULL OR inversions >= 0)
  );

-- ============================================================================
-- STEP 2.5: ADD INDEXES FOR PERFORMANCE
-- ============================================================================

-- Add indexes to improve query performance on foreign keys and frequently queried columns
CREATE INDEX IF NOT EXISTS idx_parks_slug ON parks(slug);
CREATE INDEX IF NOT EXISTS idx_rides_slug ON rides(park_id, slug);
CREATE INDEX IF NOT EXISTS idx_companies_slug ON companies(slug);
CREATE INDEX IF NOT EXISTS idx_ride_models_slug ON ride_models(manufacturer_id, slug);

-- Add indexes for submission items dependencies
CREATE INDEX IF NOT EXISTS idx_submission_items_depends_on ON submission_items(depends_on) WHERE depends_on IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_submission_items_submission_id ON submission_items(submission_id);

-- Add indexes for date filtering
CREATE INDEX IF NOT EXISTS idx_parks_status_opening_date ON parks(status, opening_date);
CREATE INDEX IF NOT EXISTS idx_rides_status_opening_date ON rides(status, opening_date);

-- ============================================================================
-- VERIFICATION & LOGGING
-- ============================================================================

-- Log completion
DO $$
BEGIN
  RAISE NOTICE 'Phase 2 DATABASE INTEGRITY ENHANCEMENTS completed successfully';
  RAISE NOTICE '- Added UNIQUE constraints on slugs (parks, rides, companies, ride_models)';
  RAISE NOTICE '- Added CHECK constraints for date_precision validation (10+ tables)';
  RAISE NOTICE '- Added trigger-based validation for submission_items.depends_on';
  RAISE NOTICE '- Added data integrity constraints (date logic, rating ranges, numeric validation)';
  RAISE NOTICE '- Added performance indexes for slug lookups and submission dependencies';
  RAISE NOTICE '- Database integrity now enforced at schema level!';
END $$;