-- Migration: Create regions and cities tables
-- Description: Global location data (Regions/States + Cities)

CREATE TABLE IF NOT EXISTS regions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country_code VARCHAR(10) NOT NULL,  -- ISO Country (US, BR, JP, etc.)
    code VARCHAR(10),  -- ISO Region code (e.g., SP, CA)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS cities (
    id SERIAL PRIMARY KEY,
    region_id INT,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Ensure column and constraints exist when table already existed without them
ALTER TABLE cities
    ADD COLUMN IF NOT EXISTS region_id INT;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints tc
        JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
        WHERE tc.table_name = 'cities' AND tc.constraint_type = 'FOREIGN KEY' AND kcu.column_name = 'region_id'
    ) THEN
        ALTER TABLE cities ADD CONSTRAINT fk_cities_region FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE CASCADE;
    END IF;
END$$;

-- Indexes (safe if table/column already existed)
CREATE INDEX IF NOT EXISTS idx_regions_country ON regions(country_code);
CREATE INDEX IF NOT EXISTS idx_cities_region ON cities(region_id);

-- Comments
COMMENT ON TABLE regions IS 'Global Regions (States, Provinces, Prefectures)';
COMMENT ON TABLE cities IS 'Global Cities by Region';
COMMENT ON COLUMN regions.code IS 'ISO Region code (e.g., SP, CA)';