thrilltrack-explorer/supabase/migrations/20251010185634_375d6c7d-5497-4888-b7c3-267ee1cf31bb.sql

-- Add date precision columns to all tables with date fields

-- Parks
ALTER TABLE parks 
  ADD COLUMN IF NOT EXISTS opening_date_precision TEXT CHECK (opening_date_precision IN ('day', 'month', 'year')),
  ADD COLUMN IF NOT EXISTS closing_date_precision TEXT CHECK (closing_date_precision IN ('day', 'month', 'year'));

ALTER TABLE park_submissions 
  ADD COLUMN IF NOT EXISTS opening_date_precision TEXT CHECK (opening_date_precision IN ('day', 'month', 'year')),
  ADD COLUMN IF NOT EXISTS closing_date_precision TEXT CHECK (closing_date_precision IN ('day', 'month', 'year'));

-- Rides
ALTER TABLE rides 
  ADD COLUMN IF NOT EXISTS opening_date_precision TEXT CHECK (opening_date_precision IN ('day', 'month', 'year')),
  ADD COLUMN IF NOT EXISTS closing_date_precision TEXT CHECK (closing_date_precision IN ('day', 'month', 'year'));

ALTER TABLE ride_submissions 
  ADD COLUMN IF NOT EXISTS opening_date_precision TEXT CHECK (opening_date_precision IN ('day', 'month', 'year')),
  ADD COLUMN IF NOT EXISTS closing_date_precision TEXT CHECK (closing_date_precision IN ('day', 'month', 'year'));

-- Ride name history
ALTER TABLE ride_name_history 
  ADD COLUMN IF NOT EXISTS date_changed_precision TEXT CHECK (date_changed_precision IN ('day', 'month', 'year'));

ALTER TABLE ride_submission_name_history 
  ADD COLUMN IF NOT EXISTS date_changed_precision TEXT CHECK (date_changed_precision IN ('day', 'month', 'year'));

-- Photos
ALTER TABLE photos 
  ADD COLUMN IF NOT EXISTS date_taken_precision TEXT CHECK (date_taken_precision IN ('day', 'month', 'year'));

ALTER TABLE photo_submission_items 
  ADD COLUMN IF NOT EXISTS date_taken_precision TEXT CHECK (date_taken_precision IN ('day', 'month', 'year'));

-- Companies: Add founded_date to replace founded_year
ALTER TABLE companies 
  ADD COLUMN IF NOT EXISTS founded_date DATE,
  ADD COLUMN IF NOT EXISTS founded_date_precision TEXT CHECK (founded_date_precision IN ('day', 'month', 'year'));

-- Migrate existing founded_year data to founded_date
UPDATE companies 
SET 
  founded_date = make_date(founded_year, 1, 1),
  founded_date_precision = 'year'
WHERE founded_year IS NOT NULL 
  AND founded_date IS NULL;

-- Company submissions
ALTER TABLE company_submissions 
  ADD COLUMN IF NOT EXISTS founded_date DATE,
  ADD COLUMN IF NOT EXISTS founded_date_precision TEXT CHECK (founded_date_precision IN ('day', 'month', 'year'));

UPDATE company_submissions 
SET 
  founded_date = make_date(founded_year, 1, 1),
  founded_date_precision = 'year'
WHERE founded_year IS NOT NULL
  AND founded_date IS NULL;

-- Historical entities
ALTER TABLE historical_parks
  ADD COLUMN IF NOT EXISTS operated_from_precision TEXT CHECK (operated_from_precision IN ('day', 'month', 'year')),
  ADD COLUMN IF NOT EXISTS operated_until_precision TEXT CHECK (operated_until_precision IN ('day', 'month', 'year'));

ALTER TABLE historical_rides
  ADD COLUMN IF NOT EXISTS operated_from_precision TEXT CHECK (operated_from_precision IN ('day', 'month', 'year')),
  ADD COLUMN IF NOT EXISTS operated_until_precision TEXT CHECK (operated_until_precision IN ('day', 'month', 'year'));

-- Set default precision for existing dates (assume full date precision)
UPDATE parks SET opening_date_precision = 'day' WHERE opening_date IS NOT NULL AND opening_date_precision IS NULL;
UPDATE parks SET closing_date_precision = 'day' WHERE closing_date IS NOT NULL AND closing_date_precision IS NULL;
UPDATE park_submissions SET opening_date_precision = 'day' WHERE opening_date IS NOT NULL AND opening_date_precision IS NULL;
UPDATE park_submissions SET closing_date_precision = 'day' WHERE closing_date IS NOT NULL AND closing_date_precision IS NULL;
UPDATE rides SET opening_date_precision = 'day' WHERE opening_date IS NOT NULL AND opening_date_precision IS NULL;
UPDATE rides SET closing_date_precision = 'day' WHERE closing_date IS NOT NULL AND closing_date_precision IS NULL;
UPDATE ride_submissions SET opening_date_precision = 'day' WHERE opening_date IS NOT NULL AND opening_date_precision IS NULL;
UPDATE ride_submissions SET closing_date_precision = 'day' WHERE closing_date IS NOT NULL AND closing_date_precision IS NULL;
UPDATE ride_name_history SET date_changed_precision = 'day' WHERE date_changed IS NOT NULL AND date_changed_precision IS NULL;
UPDATE ride_submission_name_history SET date_changed_precision = 'day' WHERE date_changed IS NOT NULL AND date_changed_precision IS NULL;
UPDATE photos SET date_taken_precision = 'day' WHERE date_taken IS NOT NULL AND date_taken_precision IS NULL;
UPDATE photo_submission_items SET date_taken_precision = 'day' WHERE date_taken IS NOT NULL AND date_taken_precision IS NULL;
UPDATE historical_parks SET operated_from_precision = 'day' WHERE operated_from IS NOT NULL AND operated_from_precision IS NULL;
UPDATE historical_parks SET operated_until_precision = 'day' WHERE operated_until IS NOT NULL AND operated_until_precision IS NULL;
UPDATE historical_rides SET operated_from_precision = 'day' WHERE operated_from IS NOT NULL AND operated_from_precision IS NULL;
UPDATE historical_rides SET operated_until_precision = 'day' WHERE operated_until IS NOT NULL AND operated_until_precision IS NULL;