-- Migration: Phone Numbers and Stores Integration
-- Description: Adds price column to phone_numbers, creates FK relation to stores, and populates diverse phone numbers
-- Date: 2025-11-10
-- Related Issue: #65

-- ============================================================================
-- STEP 1: Add price column to phone_numbers table
-- ============================================================================

ALTER TABLE phone_numbers
ADD COLUMN IF NOT EXISTS price NUMERIC(10, 4) DEFAULT 0.0000;

COMMENT ON COLUMN phone_numbers.price IS 'Price for the phone number. 0.0000 = free, > 0 = premium';

-- ============================================================================
-- STEP 2: Add phone_number_id to stores table (FK relation)
-- ============================================================================

ALTER TABLE stores
ADD COLUMN IF NOT EXISTS phone_number_id UUID REFERENCES phone_numbers(id) ON DELETE SET NULL;

COMMENT ON COLUMN stores.phone_number_id IS 'Foreign key to phone_numbers table. Phone number assigned to this store.';

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_stores_phone_number_id ON stores(phone_number_id);

-- ============================================================================
-- STEP 3: Migrate existing stores with phone_number text to phone_number_id
-- ============================================================================

-- This will be handled separately after we assign phone numbers to existing stores

-- ============================================================================
-- STEP 4: Insert diverse fake phone numbers (free and premium, various countries)
-- ============================================================================

-- Germany (DE) - Free numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+49 30 1234 5670', 'DE', 'Germany', 'Berlin', 'local', true, true, 0.0000),
  ('+49 30 1234 5671', 'DE', 'Germany', 'Berlin', 'local', true, true, 0.0000),
  ('+49 89 1234 5672', 'DE', 'Germany', 'Munich', 'local', true, true, 0.0000),
  ('+49 40 1234 5673', 'DE', 'Germany', 'Hamburg', 'local', true, true, 0.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- Germany (DE) - Premium numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+49 800 1234 567', 'DE', 'Germany', 'Berlin', 'toll-free', true, true, 9.9900),
  ('+49 800 1234 568', 'DE', 'Germany', 'Munich', 'toll-free', true, true, 9.9900)
ON CONFLICT (phone_number) DO NOTHING;

-- United Kingdom (GB) - Free numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+44 20 1234 5670', 'GB', 'United Kingdom', 'London', 'local', true, true, 0.0000),
  ('+44 20 1234 5671', 'GB', 'United Kingdom', 'London', 'local', true, true, 0.0000),
  ('+44 161 234 5672', 'GB', 'United Kingdom', 'Manchester', 'local', true, true, 0.0000),
  ('+44 121 234 5673', 'GB', 'United Kingdom', 'Birmingham', 'local', true, true, 0.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- United Kingdom (GB) - Premium numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+44 800 123 4567', 'GB', 'United Kingdom', 'London', 'toll-free', true, true, 12.5000),
  ('+44 800 123 4568', 'GB', 'United Kingdom', 'Manchester', 'toll-free', true, true, 12.5000)
ON CONFLICT (phone_number) DO NOTHING;

-- France (FR) - Free numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+33 1 23 45 67 80', 'FR', 'France', 'Paris', 'local', true, true, 0.0000),
  ('+33 1 23 45 67 81', 'FR', 'France', 'Paris', 'local', true, true, 0.0000),
  ('+33 4 23 45 67 82', 'FR', 'France', 'Lyon', 'local', true, true, 0.0000),
  ('+33 5 23 45 67 83', 'FR', 'France', 'Marseille', 'local', true, true, 0.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- France (FR) - Premium numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+33 800 123 456', 'FR', 'France', 'Paris', 'toll-free', true, true, 11.5000),
  ('+33 800 123 457', 'FR', 'France', 'Lyon', 'toll-free', true, true, 11.5000)
ON CONFLICT (phone_number) DO NOTHING;

-- Italy (IT) - Free numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+39 06 1234 5670', 'IT', 'Italy', 'Rome', 'local', true, true, 0.0000),
  ('+39 06 1234 5671', 'IT', 'Italy', 'Rome', 'local', true, true, 0.0000),
  ('+39 02 1234 5672', 'IT', 'Italy', 'Milan', 'local', true, true, 0.0000),
  ('+39 081 234 5673', 'IT', 'Italy', 'Naples', 'local', true, true, 0.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- Italy (IT) - Premium numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+39 800 123 456', 'IT', 'Italy', 'Rome', 'toll-free', true, true, 10.5000),
  ('+39 800 123 457', 'IT', 'Italy', 'Milan', 'toll-free', true, true, 10.5000)
ON CONFLICT (phone_number) DO NOTHING;

-- Spain (ES) - Free numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+34 91 123 4567', 'ES', 'Spain', 'Madrid', 'local', true, true, 0.0000),
  ('+34 91 123 4568', 'ES', 'Spain', 'Madrid', 'local', true, true, 0.0000),
  ('+34 93 123 4569', 'ES', 'Spain', 'Barcelona', 'local', true, true, 0.0000),
  ('+34 95 123 4570', 'ES', 'Spain', 'Seville', 'local', true, true, 0.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- Spain (ES) - Premium numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+34 800 123 456', 'ES', 'Spain', 'Madrid', 'toll-free', true, true, 9.5000),
  ('+34 800 123 457', 'ES', 'Spain', 'Barcelona', 'toll-free', true, true, 9.5000)
ON CONFLICT (phone_number) DO NOTHING;

-- Austria (AT) - Free numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+43 1 234 5670', 'AT', 'Austria', 'Vienna', 'local', true, true, 0.0000),
  ('+43 1 234 5671', 'AT', 'Austria', 'Vienna', 'local', true, true, 0.0000),
  ('+43 316 234 567', 'AT', 'Austria', 'Graz', 'local', true, true, 0.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- Austria (AT) - Premium numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+43 800 123 456', 'AT', 'Austria', 'Vienna', 'toll-free', true, true, 10.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- Netherlands (NL) - Free numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+31 20 123 4567', 'NL', 'Netherlands', 'Amsterdam', 'local', true, true, 0.0000),
  ('+31 20 123 4568', 'NL', 'Netherlands', 'Amsterdam', 'local', true, true, 0.0000),
  ('+31 10 123 4569', 'NL', 'Netherlands', 'Rotterdam', 'local', true, true, 0.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- Netherlands (NL) - Premium numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+31 800 123 456', 'NL', 'Netherlands', 'Amsterdam', 'toll-free', true, true, 11.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- Belgium (BE) - Free numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+32 2 123 4567', 'BE', 'Belgium', 'Brussels', 'local', true, true, 0.0000),
  ('+32 2 123 4568', 'BE', 'Belgium', 'Brussels', 'local', true, true, 0.0000),
  ('+32 3 123 4569', 'BE', 'Belgium', 'Antwerp', 'local', true, true, 0.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- Belgium (BE) - Premium numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+32 800 123 45', 'BE', 'Belgium', 'Brussels', 'toll-free', true, true, 10.5000)
ON CONFLICT (phone_number) DO NOTHING;

-- Poland (PL) - Free numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+48 22 123 4567', 'PL', 'Poland', 'Warsaw', 'local', true, true, 0.0000),
  ('+48 22 123 4568', 'PL', 'Poland', 'Warsaw', 'local', true, true, 0.0000),
  ('+48 12 123 4569', 'PL', 'Poland', 'Krakow', 'local', true, true, 0.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- Poland (PL) - Premium numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+48 800 123 456', 'PL', 'Poland', 'Warsaw', 'toll-free', true, true, 8.5000)
ON CONFLICT (phone_number) DO NOTHING;

-- United States (US) - Free numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+1 212 123 4567', 'US', 'United States', 'New York', 'local', true, true, 0.0000),
  ('+1 212 123 4568', 'US', 'United States', 'New York', 'local', true, true, 0.0000),
  ('+1 310 123 4569', 'US', 'United States', 'Los Angeles', 'local', true, true, 0.0000),
  ('+1 312 123 4570', 'US', 'United States', 'Chicago', 'local', true, true, 0.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- United States (US) - Premium numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+1 800 123 4567', 'US', 'United States', 'National', 'toll-free', true, true, 15.0000),
  ('+1 888 123 4567', 'US', 'United States', 'National', 'toll-free', true, true, 15.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- Canada (CA) - Free numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+1 416 123 4567', 'CA', 'Canada', 'Toronto', 'local', true, true, 0.0000),
  ('+1 416 123 4568', 'CA', 'Canada', 'Toronto', 'local', true, true, 0.0000),
  ('+1 514 123 4569', 'CA', 'Canada', 'Montreal', 'local', true, true, 0.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- Canada (CA) - Premium numbers
INSERT INTO phone_numbers (phone_number, country_code, country_name, location, phone_type, is_available, is_active, price)
VALUES
  ('+1 800 123 4570', 'CA', 'Canada', 'National', 'toll-free', true, true, 14.0000)
ON CONFLICT (phone_number) DO NOTHING;

-- ============================================================================
-- STEP 5: Assign first available phone number to existing stores
-- ============================================================================

-- For each existing store without a phone_number_id, assign the first available
-- phone number from the user's country
DO $$
DECLARE
  store_record RECORD;
  assigned_phone_id UUID;
BEGIN
  FOR store_record IN
    SELECT s.id, s.user_id, p.country_code
    FROM stores s
    JOIN profiles p ON s.user_id = p.id
    WHERE s.phone_number_id IS NULL
  LOOP
    -- Find first available phone number matching user's country
    SELECT id INTO assigned_phone_id
    FROM phone_numbers
    WHERE country_code = store_record.country_code
      AND is_available = true
      AND is_active = true
      AND assigned_to_store_id IS NULL
    ORDER BY price ASC, created_at ASC
    LIMIT 1;

    -- If a matching phone number is found, assign it
    IF assigned_phone_id IS NOT NULL THEN
      -- Update phone_numbers table
      UPDATE phone_numbers
      SET
        assigned_to_store_id = store_record.id,
        assigned_to_user_id = store_record.user_id,
        is_available = false,
        assigned_at = NOW(),
        updated_at = NOW()
      WHERE id = assigned_phone_id;

      -- Update stores table
      UPDATE stores
      SET
        phone_number_id = assigned_phone_id,
        updated_at = NOW()
      WHERE id = store_record.id;

      RAISE NOTICE 'Assigned phone number % to store %', assigned_phone_id, store_record.id;
    ELSE
      RAISE NOTICE 'No available phone number found for store % with country %', store_record.id, store_record.country_code;
    END IF;
  END LOOP;
END $$;

-- ============================================================================
-- STEP 6: Create trigger to prevent phone number changes after store creation
-- ============================================================================

CREATE OR REPLACE FUNCTION prevent_phone_number_change()
RETURNS TRIGGER AS $$
BEGIN
  -- If the store already has a phone_number_id assigned (not NULL in OLD)
  -- and user is trying to change it to a different value
  IF OLD.phone_number_id IS NOT NULL AND NEW.phone_number_id IS DISTINCT FROM OLD.phone_number_id THEN
    RAISE EXCEPTION 'Cannot change phone number after store creation. Phone number is permanently assigned.';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Drop trigger if exists and create new one
DROP TRIGGER IF EXISTS trigger_prevent_phone_number_change ON stores;

CREATE TRIGGER trigger_prevent_phone_number_change
  BEFORE UPDATE ON stores
  FOR EACH ROW
  EXECUTE FUNCTION prevent_phone_number_change();

COMMENT ON TRIGGER trigger_prevent_phone_number_change ON stores IS 'Prevents changing phone_number_id after initial assignment';

-- ============================================================================
-- STEP 7: Create function to get available phone numbers for a user's country
-- ============================================================================

CREATE OR REPLACE FUNCTION get_available_phone_numbers_by_country(user_country_code TEXT)
RETURNS TABLE (
  id UUID,
  phone_number TEXT,
  country_code TEXT,
  country_name TEXT,
  location TEXT,
  phone_type TEXT,
  price NUMERIC,
  is_available BOOLEAN
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    pn.id,
    pn.phone_number,
    pn.country_code,
    pn.country_name,
    pn.location,
    pn.phone_type,
    pn.price,
    pn.is_available
  FROM phone_numbers pn
  WHERE pn.country_code = user_country_code
    AND pn.is_available = true
    AND pn.is_active = true
    AND pn.assigned_to_store_id IS NULL
  ORDER BY pn.price ASC, pn.created_at ASC;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_available_phone_numbers_by_country IS 'Returns available phone numbers for a specific country code, ordered by price (free first)';

-- ============================================================================
-- Migration Complete
-- ============================================================================

DO $$
BEGIN
  RAISE NOTICE '============================================================';
  RAISE NOTICE 'Phone Numbers and Stores Integration Migration Complete';
  RAISE NOTICE '============================================================';
  RAISE NOTICE '';
  RAISE NOTICE 'Changes applied:';
  RAISE NOTICE '1. Added price column to phone_numbers table';
  RAISE NOTICE '2. Added phone_number_id FK to stores table';
  RAISE NOTICE '3. Created diverse fake phone numbers (10 countries, free + premium)';
  RAISE NOTICE '4. Assigned phone numbers to existing stores';
  RAISE NOTICE '5. Created trigger to prevent phone number changes';
  RAISE NOTICE '6. Created helper function get_available_phone_numbers_by_country()';
  RAISE NOTICE '';
  RAISE NOTICE 'Next steps:';
  RAISE NOTICE '1. Deploy /phone-numbers API endpoint';
  RAISE NOTICE '2. Update frontend to use phone number selection';
  RAISE NOTICE '3. Disable phone number changes in UI after store creation';
  RAISE NOTICE '============================================================';
END $$;
