-- =====================================================================
-- Kapitano Logistic integration - Step 2 (create order): lookup tables
--
-- Two NEW tables - no existing table is altered, no foreign keys:
--
--   logistic_branches        where each shop hands goods to the captain
--                            (shops.delivery_pickup_latitude/longitude are empty
--                            for every shop, and Logistic requires coordinates)
--   logistic_area_centroids  a governorate's centre: the fallback drop-off point
--                            for orders that carry no coordinates (~92 % of them).
--                            Approximate by construction; those orders are marked.
--
-- The 14 governorates are seeded (keys are pre-normalised: no diacritics,
-- ة->ه, ى->ي, أ/إ/آ->ا, because the web data spells one governorate several ways).
--
-- Same statements as migration 2026_10_05_000020_create_logistic_branch_and_area_tables.
-- Re-running the file changes nothing.
-- Requires the Step 1 file (kapitano_logistic_integration_2026-10-05.sql) to have run first.
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `logistic_branches` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `shop_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(120) NULL,
    `address` VARCHAR(255) NULL,
    `lat` DECIMAL(10,7) NOT NULL,
    `lng` DECIMAL(10,7) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `logistic_branches_shop_id_unique` (`shop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `logistic_area_centroids` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name_key` VARCHAR(80) NOT NULL,
    `label` VARCHAR(80) NOT NULL,
    `lat` DECIMAL(10,7) NOT NULL,
    `lng` DECIMAL(10,7) NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `logistic_area_centroids_name_key_unique` (`name_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `logistic_area_centroids` (`name_key`, `label`, `lat`, `lng`, `created_at`, `updated_at`) VALUES
    ('دمشق',      'دمشق',      33.5138000, 36.2765000, NOW(), NOW()),
    ('ريف دمشق',  'ريف دمشق',  33.5714000, 36.4034000, NOW(), NOW()),
    ('حلب',       'حلب',       36.2021000, 37.1343000, NOW(), NOW()),
    ('halab',     'حلب',       36.2021000, 37.1343000, NOW(), NOW()),
    ('حمص',       'حمص',       34.7324000, 36.7137000, NOW(), NOW()),
    ('حماه',      'حماة',      35.1318000, 36.7578000, NOW(), NOW()),
    ('اللاذقيه',  'اللاذقية',  35.5317000, 35.7900000, NOW(), NOW()),
    ('طرطوس',     'طرطوس',     34.8890000, 35.8866000, NOW(), NOW()),
    ('ادلب',      'إدلب',      35.9306000, 36.6339000, NOW(), NOW()),
    ('دير الزور', 'دير الزور', 35.3359000, 40.1408000, NOW(), NOW()),
    ('الرقه',     'الرقة',     35.9528000, 39.0079000, NOW(), NOW()),
    ('الحسكه',    'الحسكة',    36.5024000, 40.7477000, NOW(), NOW()),
    ('درعا',      'درعا',      32.6189000, 36.1021000, NOW(), NOW()),
    ('السويداء',  'السويداء',  32.7090000, 36.5690000, NOW(), NOW()),
    ('القنيطره',  'القنيطرة',  33.1257000, 35.8247000, NOW(), NOW());