-- Iran location reference data (country → provinces → provincial capitals)
-- Requires migration 015_cities.sql

INSERT INTO cities (parent_id, level, name_fa, name_en, landline_code, slug, sort_order) VALUES
  (NULL, 'country', 'ایران', 'Iran', '98', 'iran', 1);

INSERT INTO cities (parent_id, level, name_fa, name_en, landline_code, slug, sort_order)
SELECT c.id, 'province', v.name_fa, v.name_en, v.landline_code, v.slug, v.sort_order
FROM cities c
CROSS JOIN (
  VALUES
    ('آذربایجان شرقی',   'East Azerbaijan',           '041', 'east-azerbaijan',           1),
    ('آذربایجان غربی',   'West Azerbaijan',           '044', 'west-azerbaijan',           2),
    ('اردبیل',           'Ardabil',                   '045', 'ardabil',                   3),
    ('اصفهان',           'Isfahan',                   '031', 'isfahan',                   4),
    ('البرز',            'Alborz',                    '026', 'alborz',                    5),
    ('ایلام',            'Ilam',                      '084', 'ilam',                      6),
    ('بوشهر',            'Bushehr',                   '077', 'bushehr',                   7),
    ('تهران',            'Tehran',                    '021', 'tehran-province',           8),
    ('چهارمحال و بختیاری', 'Chaharmahal and Bakhtiari', '038', 'chaharmahal-bakhtiari',   9),
    ('خراسان جنوبی',     'South Khorasan',            '056', 'south-khorasan',           10),
    ('خراسان رضوی',      'Razavi Khorasan',           '051', 'razavi-khorasan',          11),
    ('خراسان شمالی',     'North Khorasan',            '058', 'north-khorasan',           12),
    ('خوزستان',          'Khuzestan',                 '061', 'khuzestan',                13),
    ('زنجان',            'Zanjan',                    '024', 'zanjan',                   14),
    ('سمنان',            'Semnan',                    '023', 'semnan',                   15),
    ('سیستان و بلوچستان', 'Sistan and Baluchestan',   '054', 'sistan-baluchestan',       16),
    ('فارس',             'Fars',                      '071', 'fars',                     17),
    ('قزوین',            'Qazvin',                    '028', 'qazvin',                   18),
    ('قم',               'Qom',                       '025', 'qom',                      19),
    ('کردستان',         'Kurdistan',                 '087', 'kurdistan',                20),
    ('کرمان',            'Kerman',                    '034', 'kerman',                   21),
    ('کرمانشاه',         'Kermanshah',                '083', 'kermanshah',               22),
    ('کهگیلویه و بویراحمد', 'Kohgiluyeh and Boyer-Ahmad', '074', 'kohgiluyeh-boyer-ahmad', 23),
    ('گلستان',           'Golestan',                  '017', 'golestan',                 24),
    ('گیلان',            'Gilan',                     '013', 'gilan',                    25),
    ('لرستان',           'Lorestan',                  '066', 'lorestan',                 26),
    ('مازندران',         'Mazandaran',                '011', 'mazandaran',               27),
    ('مرکزی',            'Markazi',                   '086', 'markazi',                  28),
    ('هرمزگان',         'Hormozgan',                 '076', 'hormozgan',                29),
    ('همدان',            'Hamadan',                   '081', 'hamadan',                  30),
    ('یزد',              'Yazd',                      '035', 'yazd',                     31)
) AS v(name_fa, name_en, landline_code, slug, sort_order)
WHERE c.slug = 'iran';

INSERT INTO cities (parent_id, level, name_fa, name_en, landline_code, slug, sort_order)
SELECT p.id, 'city', v.name_fa, v.name_en, v.landline_code, v.slug, v.sort_order
FROM cities p
JOIN (
  VALUES
    ('east-azerbaijan',         'تبریز',              'Tabriz',              '041', 'tabriz',              1),
    ('west-azerbaijan',         'ارومیه',             'Urmia',               '044', 'urmia',               1),
    ('ardabil',                 'اردبیل',             'Ardabil',             '045', 'ardabil-city',        1),
    ('isfahan',                 'اصفهان',             'Isfahan',             '031', 'isfahan-city',        1),
    ('alborz',                  'کرج',                'Karaj',               '026', 'karaj',               1),
    ('ilam',                    'ایلام',              'Ilam',                '084', 'ilam-city',           1),
    ('bushehr',                 'بوشهر',              'Bushehr',             '077', 'bushehr-city',        1),
    ('tehran-province',         'تهران',              'Tehran',              '021', 'tehran',              1),
    ('chaharmahal-bakhtiari',   'شهرکرد',             'Shahrekord',          '038', 'shahrekord',          1),
    ('south-khorasan',          'بیرجند',             'Birjand',             '056', 'birjand',             1),
    ('razavi-khorasan',         'مشهد',               'Mashhad',             '051', 'mashhad',             1),
    ('north-khorasan',          'بجنورد',             'Bojnord',             '058', 'bojnord',             1),
    ('khuzestan',               'اهواز',              'Ahvaz',               '061', 'ahvaz',               1),
    ('zanjan',                  'زنجان',              'Zanjan',              '024', 'zanjan-city',         1),
    ('semnan',                  'سمنان',              'Semnan',              '023', 'semnan-city',         1),
    ('sistan-baluchestan',      'زاهدان',             'Zahedan',             '054', 'zahedan',             1),
    ('fars',                    'شیراز',              'Shiraz',              '071', 'shiraz',              1),
    ('qazvin',                  'قزوین',              'Qazvin',              '028', 'qazvin-city',         1),
    ('qom',                     'قم',                 'Qom',                 '025', 'qom-city',            1),
    ('kurdistan',               'سنندج',              'Sanandaj',            '087', 'sanandaj',            1),
    ('kerman',                  'کرمان',              'Kerman',              '034', 'kerman-city',         1),
    ('kermanshah',              'کرمانشاه',           'Kermanshah',          '083', 'kermanshah-city',     1),
    ('kohgiluyeh-boyer-ahmad',  'یاسوج',              'Yasuj',               '074', 'yasuj',               1),
    ('golestan',                'گرگان',              'Gorgan',              '017', 'gorgan',              1),
    ('gilan',                   'رشت',                'Rasht',               '013', 'rasht',               1),
    ('lorestan',                'خرم‌آباد',           'Khorramabad',         '066', 'khorramabad',         1),
    ('mazandaran',              'ساری',               'Sari',                '011', 'sari',                1),
    ('markazi',                 'اراک',               'Arak',                '086', 'arak',                1),
    ('hormozgan',               'بندرعباس',           'Bandar Abbas',        '076', 'bandar-abbas',        1),
    ('hamadan',                 'همدان',              'Hamadan',             '081', 'hamadan-city',        1),
    ('yazd',                    'یزد',                'Yazd',                '035', 'yazd-city',           1)
) AS v(province_slug, name_fa, name_en, landline_code, slug, sort_order)
  ON p.slug = v.province_slug
WHERE p.level = 'province';

-- Additional major cities
INSERT INTO cities (parent_id, level, name_fa, name_en, landline_code, slug, sort_order)
SELECT p.id, 'city', v.name_fa, v.name_en, v.landline_code, v.slug, v.sort_order
FROM cities p
JOIN (
  VALUES
    ('tehran-province', 'ری',           'Rey',           '021', 'rey',           2),
    ('tehran-province', 'شهریار',       'Shahriar',      '021', 'shahriar',      3),
    ('tehran-province', 'ورامین',       'Varamin',       '021', 'varamin',       4),
    ('isfahan',         'کاشان',        'Kashan',        '031', 'kashan',        2),
    ('isfahan',         'نجف‌آباد',     'Najafabad',     '031', 'najafabad',     3),
    ('fars',            'مرودشت',       'Marvdasht',     '071', 'marvdasht',     2),
    ('fars',            'جهرم',         'Jahrom',        '071', 'jahrom',        3),
    ('khuzestan',       'آبادان',       'Abadan',        '061', 'abadan',        2),
    ('khuzestan',       'دزفول',        'Dezful',        '061', 'dezful',        3),
    ('razavi-khorasan', 'نیشابور',      'Neyshabur',     '051', 'neyshabur',     2),
    ('razavi-khorasan', 'سبزوار',       'Sabzevar',      '051', 'sabzevar',      3),
    ('mazandaran',      'آمل',          'Amol',          '011', 'amol',          2),
    ('mazandaran',      'بابل',         'Babol',         '011', 'babol',         3),
    ('gilan',           'انزلی',        'Bandar Anzali', '013', 'bandar-anzali', 2),
    ('east-azerbaijan', 'مراغه',        'Maragheh',      '041', 'maragheh',      2),
    ('kerman',          'رفسنجان',      'Rafsanjan',     '034', 'rafsanjan',     2),
    ('alborz',          'فردیس',        'Fardis',        '026', 'fardis',        2)
) AS v(province_slug, name_fa, name_en, landline_code, slug, sort_order)
  ON p.slug = v.province_slug
WHERE p.level = 'province';

SELECT setval(pg_get_serial_sequence('cities', 'id'), COALESCE((SELECT MAX(id) FROM cities), 1));