-- Phase 3A: Create Production Relational Tables and Data Migration

-- 1. Ride Technical Specifications
CREATE TABLE ride_technical_specifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ride_id UUID NOT NULL REFERENCES rides(id) ON DELETE CASCADE,
  spec_name TEXT NOT NULL,
  spec_value TEXT NOT NULL,
  spec_type TEXT NOT NULL CHECK (spec_type IN ('string', 'number', 'boolean', 'date')),
  category TEXT,
  unit TEXT,
  display_order INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  CONSTRAINT ride_technical_specifications_unique_spec UNIQUE(ride_id, spec_name)
);

CREATE INDEX idx_ride_technical_specifications_ride_id ON ride_technical_specifications(ride_id);
CREATE INDEX idx_ride_technical_specifications_category ON ride_technical_specifications(category);

-- 2. Ride Coaster Statistics
CREATE TABLE ride_coaster_statistics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ride_id UUID NOT NULL REFERENCES rides(id) ON DELETE CASCADE,
  stat_name TEXT NOT NULL,
  stat_value NUMERIC NOT NULL,
  unit TEXT,
  category TEXT,
  description TEXT,
  display_order INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  CONSTRAINT ride_coaster_statistics_unique_stat UNIQUE(ride_id, stat_name)
);

CREATE INDEX idx_ride_coaster_statistics_ride_id ON ride_coaster_statistics(ride_id);
CREATE INDEX idx_ride_coaster_statistics_category ON ride_coaster_statistics(category);

-- 3. Ride Name History
CREATE TABLE ride_name_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ride_id UUID NOT NULL REFERENCES rides(id) ON DELETE CASCADE,
  former_name TEXT NOT NULL,
  date_changed DATE,
  reason TEXT,
  from_year INTEGER,
  to_year INTEGER,
  order_index INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ride_name_history_ride_id ON ride_name_history(ride_id);
CREATE INDEX idx_ride_name_history_date_changed ON ride_name_history(date_changed);

-- 4. Ride Model Technical Specifications
CREATE TABLE ride_model_technical_specifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ride_model_id UUID NOT NULL REFERENCES ride_models(id) ON DELETE CASCADE,
  spec_name TEXT NOT NULL,
  spec_value TEXT NOT NULL,
  spec_type TEXT NOT NULL CHECK (spec_type IN ('string', 'number', 'boolean', 'date')),
  category TEXT,
  unit TEXT,
  display_order INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  CONSTRAINT ride_model_technical_specifications_unique_spec UNIQUE(ride_model_id, spec_name)
);

CREATE INDEX idx_ride_model_technical_specifications_ride_model_id ON ride_model_technical_specifications(ride_model_id);
CREATE INDEX idx_ride_model_technical_specifications_category ON ride_model_technical_specifications(category);

-- Enable RLS on all tables
ALTER TABLE ride_technical_specifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE ride_coaster_statistics ENABLE ROW LEVEL SECURITY;
ALTER TABLE ride_name_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE ride_model_technical_specifications ENABLE ROW LEVEL SECURITY;

-- RLS Policies for ride_technical_specifications
CREATE POLICY "Public read access to ride technical specifications"
  ON ride_technical_specifications FOR SELECT
  USING (true);

CREATE POLICY "Moderators can manage ride technical specifications"
  ON ride_technical_specifications FOR ALL
  USING (is_moderator(auth.uid()));

-- RLS Policies for ride_coaster_statistics
CREATE POLICY "Public read access to ride coaster statistics"
  ON ride_coaster_statistics FOR SELECT
  USING (true);

CREATE POLICY "Moderators can manage ride coaster statistics"
  ON ride_coaster_statistics FOR ALL
  USING (is_moderator(auth.uid()));

-- RLS Policies for ride_name_history
CREATE POLICY "Public read access to ride name history"
  ON ride_name_history FOR SELECT
  USING (true);

CREATE POLICY "Moderators can manage ride name history"
  ON ride_name_history FOR ALL
  USING (is_moderator(auth.uid()));

-- RLS Policies for ride_model_technical_specifications
CREATE POLICY "Public read access to ride model technical specifications"
  ON ride_model_technical_specifications FOR SELECT
  USING (true);

CREATE POLICY "Moderators can manage ride model technical specifications"
  ON ride_model_technical_specifications FOR ALL
  USING (is_moderator(auth.uid()));

-- Data Migration Function: Migrate existing JSON data to relational tables
CREATE OR REPLACE FUNCTION migrate_ride_technical_data()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  ride_record RECORD;
  spec_key TEXT;
  spec_value TEXT;
  stat_key TEXT;
  stat_value NUMERIC;
  former_name_item JSONB;
  spec_order INTEGER;
  stat_order INTEGER;
  name_order INTEGER;
BEGIN
  -- Migrate technical_specs from rides
  FOR ride_record IN 
    SELECT id, technical_specs 
    FROM rides 
    WHERE technical_specs IS NOT NULL AND technical_specs != 'null'::jsonb
  LOOP
    spec_order := 0;
    FOR spec_key, spec_value IN 
      SELECT key, value::text 
      FROM jsonb_each_text(ride_record.technical_specs)
    LOOP
      INSERT INTO ride_technical_specifications (
        ride_id, 
        spec_name, 
        spec_value, 
        spec_type,
        display_order
      )
      VALUES (
        ride_record.id,
        spec_key,
        spec_value,
        CASE 
          WHEN spec_value ~ '^[0-9]+\.?[0-9]*$' THEN 'number'
          WHEN spec_value IN ('true', 'false') THEN 'boolean'
          ELSE 'string'
        END,
        spec_order
      )
      ON CONFLICT (ride_id, spec_name) DO NOTHING;
      
      spec_order := spec_order + 1;
    END LOOP;
  END LOOP;

  -- Migrate coaster_stats from rides
  FOR ride_record IN 
    SELECT id, coaster_stats 
    FROM rides 
    WHERE coaster_stats IS NOT NULL AND coaster_stats != 'null'::jsonb
  LOOP
    stat_order := 0;
    FOR stat_key, stat_value IN 
      SELECT key, (value::text)::numeric 
      FROM jsonb_each(ride_record.coaster_stats)
      WHERE value::text ~ '^[0-9]+\.?[0-9]*$'
    LOOP
      INSERT INTO ride_coaster_statistics (
        ride_id,
        stat_name,
        stat_value,
        display_order
      )
      VALUES (
        ride_record.id,
        stat_key,
        stat_value,
        stat_order
      )
      ON CONFLICT (ride_id, stat_name) DO NOTHING;
      
      stat_order := stat_order + 1;
    END LOOP;
  END LOOP;

  -- Migrate former_names from rides
  FOR ride_record IN 
    SELECT id, former_names 
    FROM rides 
    WHERE former_names IS NOT NULL AND former_names != 'null'::jsonb AND jsonb_array_length(former_names) > 0
  LOOP
    name_order := 0;
    FOR former_name_item IN 
      SELECT value 
      FROM jsonb_array_elements(ride_record.former_names)
    LOOP
      INSERT INTO ride_name_history (
        ride_id,
        former_name,
        date_changed,
        reason,
        order_index
      )
      VALUES (
        ride_record.id,
        former_name_item->>'name',
        (former_name_item->>'date')::date,
        former_name_item->>'reason',
        name_order
      );
      
      name_order := name_order + 1;
    END LOOP;
  END LOOP;

  -- Migrate technical_specs from ride_models
  FOR ride_record IN 
    SELECT id, technical_specs 
    FROM ride_models 
    WHERE technical_specs IS NOT NULL AND technical_specs != 'null'::jsonb
  LOOP
    spec_order := 0;
    FOR spec_key, spec_value IN 
      SELECT key, value::text 
      FROM jsonb_each_text(ride_record.technical_specs)
    LOOP
      INSERT INTO ride_model_technical_specifications (
        ride_model_id,
        spec_name,
        spec_value,
        spec_type,
        display_order
      )
      VALUES (
        ride_record.id,
        spec_key,
        spec_value,
        CASE 
          WHEN spec_value ~ '^[0-9]+\.?[0-9]*$' THEN 'number'
          WHEN spec_value IN ('true', 'false') THEN 'boolean'
          ELSE 'string'
        END,
        spec_order
      )
      ON CONFLICT (ride_model_id, spec_name) DO NOTHING;
      
      spec_order := spec_order + 1;
    END LOOP;
  END LOOP;

  RAISE NOTICE 'Data migration completed successfully';
END;
$$;

-- Execute the migration
SELECT migrate_ride_technical_data();