-- ============================================
-- SCRIPT SQL : Exemples de Frais de Livraison
-- ============================================
-- Exécutez ce script après avoir migré la base de données

-- Nettoyage (optionnel, pour recommencer à zéro)
-- TRUNCATE TABLE shipping_fees;

-- ============================================
-- CÔTE D'IVOIRE
-- ============================================

-- Frais généraux Côte d'Ivoire (si ville non listée)
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES ('Côte d\'Ivoire', NULL, NULL, 5000, 100000, 5, 1, NOW(), NOW());

-- ABIDJAN - Frais généraux ville
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES ('Côte d\'Ivoire', 'Abidjan', NULL, 2000, 100000, 2, 1, NOW(), NOW());

-- ABIDJAN - Quartiers/Communes
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES 
('Côte d\'Ivoire', 'Abidjan', 'Cocody', 1500, 100000, 1, 1, NOW(), NOW()),
('Côte d\'Ivoire', 'Abidjan', 'Plateau', 1000, 100000, 1, 1, NOW(), NOW()),
('Côte d\'Ivoire', 'Abidjan', 'Yopougon', 3000, 100000, 3, 1, NOW(), NOW()),
('Côte d\'Ivoire', 'Abidjan', 'Marcory', 2000, 100000, 2, 1, NOW(), NOW()),
('Côte d\'Ivoire', 'Abidjan', 'Treichville', 1800, 100000, 2, 1, NOW(), NOW()),
('Côte d\'Ivoire', 'Abidjan', 'Koumassi', 2500, 100000, 2, 1, NOW(), NOW()),
('Côte d\'Ivoire', 'Abidjan', 'Adjamé', 2200, 100000, 2, 1, NOW(), NOW()),
('Côte d\'Ivoire', 'Abidjan', 'Abobo', 3500, 100000, 3, 1, NOW(), NOW());

-- Autres villes Côte d'Ivoire
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES 
('Côte d\'Ivoire', 'Bouaké', NULL, 8000, 150000, 7, 1, NOW(), NOW()),
('Côte d\'Ivoire', 'Daloa', NULL, 10000, 150000, 10, 1, NOW(), NOW()),
('Côte d\'Ivoire', 'Yamoussoukro', NULL, 7000, 150000, 5, 1, NOW(), NOW());

-- ============================================
-- RÉPUBLIQUE DÉMOCRATIQUE DU CONGO
-- ============================================

-- Frais généraux RDC
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES ('République Démocratique du Congo', NULL, NULL, 10000, 200000, 7, 1, NOW(), NOW());

-- KINSHASA - Frais généraux
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES ('République Démocratique du Congo', 'Kinshasa', NULL, 5000, 150000, 3, 1, NOW(), NOW());

-- KINSHASA - Communes
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES 
('République Démocratique du Congo', 'Kinshasa', 'Gombe', 3000, 150000, 1, 1, NOW(), NOW()),
('République Démocratique du Congo', 'Kinshasa', 'Limete', 4000, 150000, 2, 1, NOW(), NOW()),
('République Démocratique du Congo', 'Kinshasa', 'Masina', 6000, 150000, 3, 1, NOW(), NOW()),
('République Démocratique du Congo', 'Kinshasa', 'Kalamu', 4500, 150000, 2, 1, NOW(), NOW()),
('République Démocratique du Congo', 'Kinshasa', 'Ngaliema', 5000, 150000, 3, 1, NOW(), NOW()),
('République Démocratique du Congo', 'Kinshasa', 'Lemba', 5500, 150000, 3, 1, NOW(), NOW());

-- Autres villes RDC
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES 
('République Démocratique du Congo', 'Lubumbashi', NULL, 15000, 200000, 10, 1, NOW(), NOW()),
('République Démocratique du Congo', 'Goma', NULL, 18000, 250000, 12, 1, NOW(), NOW());

-- ============================================
-- SÉNÉGAL
-- ============================================

-- Frais généraux Sénégal
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES ('Sénégal', NULL, NULL, 8000, 120000, 7, 1, NOW(), NOW());

-- DAKAR - Frais généraux
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES ('Sénégal', 'Dakar', NULL, 5000, 120000, 2, 1, NOW(), NOW());

-- DAKAR - Quartiers
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES 
('Sénégal', 'Dakar', 'Plateau', 2000, 120000, 1, 1, NOW(), NOW()),
('Sénégal', 'Dakar', 'Almadies', 3000, 120000, 2, 1, NOW(), NOW()),
('Sénégal', 'Dakar', 'Parcelles Assainies', 7000, 120000, 3, 1, NOW(), NOW()),
('Sénégal', 'Dakar', 'Medina', 4000, 120000, 2, 1, NOW(), NOW()),
('Sénégal', 'Dakar', 'Grand Dakar', 5000, 120000, 2, 1, NOW(), NOW());

-- ============================================
-- BÉNIN
-- ============================================

-- Frais généraux Bénin
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES ('Bénin', NULL, NULL, 6000, 100000, 5, 1, NOW(), NOW());

-- Villes Bénin
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES 
('Bénin', 'Cotonou', NULL, 2500, 100000, 2, 1, NOW(), NOW()),
('Bénin', 'Porto-Novo', NULL, 3500, 100000, 3, 1, NOW(), NOW()),
('Bénin', 'Parakou', NULL, 8000, 120000, 7, 1, NOW(), NOW());

-- ============================================
-- TOGO
-- ============================================

-- Frais généraux Togo
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES ('Togo', NULL, NULL, 5000, 80000, 5, 1, NOW(), NOW());

-- Villes Togo
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES 
('Togo', 'Lomé', NULL, 2000, 80000, 2, 1, NOW(), NOW()),
('Togo', 'Kara', NULL, 7000, 100000, 7, 1, NOW(), NOW());

-- ============================================
-- CAMEROUN
-- ============================================

-- Frais généraux Cameroun
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES ('Cameroun', NULL, NULL, 9000, 150000, 7, 1, NOW(), NOW());

-- DOUALA
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES ('Cameroun', 'Douala', NULL, 4000, 150000, 2, 1, NOW(), NOW());

-- YAOUNDÉ
INSERT INTO shipping_fees (country, city, district, fee, free_shipping_minimum, estimated_days, is_active, created_at, updated_at)
VALUES ('Cameroun', 'Yaoundé', NULL, 4500, 150000, 3, 1, NOW(), NOW());

-- ============================================
-- VÉRIFICATION
-- ============================================
-- Exécutez cette requête pour voir tous les frais configurés

SELECT 
    country,
    city,
    district,
    fee AS 'Frais (FCFA)',
    free_shipping_minimum AS 'Livraison gratuite si >',
    estimated_days AS 'Délai (jours)',
    is_active AS 'Actif'
FROM shipping_fees
ORDER BY country, city, district;

-- ============================================
-- STATISTIQUES
-- ============================================

-- Nombre de zones configurées par pays
SELECT 
    country,
    COUNT(*) as zones_count,
    AVG(fee) as avg_fee
FROM shipping_fees
WHERE is_active = 1
GROUP BY country
ORDER BY country;
