mirror of
https://git.meshkee.com/Meshkee/backend.git
synced 2026-08-11 22:30:59 +04:30
Ali Reza
bb59d5e9ba
NestJS backend with Prisma, Docker Compose for Postgres/Redis, and deploy docs for the production VM.
114 lines
9.4 KiB
SQL
114 lines
9.4 KiB
SQL
-- 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));
|