-- Migration: Enhance addresses table for user profiles
-- Date: 2025-11-04
-- Purpose: Allow users to save multiple addresses (billing, shipping, residence)

-- Add user_id to link addresses to users
ALTER TABLE addresses 
  ADD COLUMN IF NOT EXISTS user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;

-- Add address type (billing, shipping, residence)
DO $$ BEGIN
  CREATE TYPE address_type AS ENUM ('billing', 'shipping', 'residence');
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

ALTER TABLE addresses
  ADD COLUMN IF NOT EXISTS type address_type DEFAULT 'shipping';

-- Add flag for default address (per type)
ALTER TABLE addresses
  ADD COLUMN IF NOT EXISTS is_default BOOLEAN DEFAULT false;

-- Add optional label/nickname for address
ALTER TABLE addresses
  ALTER COLUMN label DROP NOT NULL;

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_addresses_user_id ON addresses(user_id);
CREATE INDEX IF NOT EXISTS idx_addresses_user_type ON addresses(user_id, type);
