PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;

-- Nettoyage des tables existantes
DELETE FROM "event";
DELETE FROM "event_type";
DELETE FROM "church";
DELETE FROM "church_type";
DELETE FROM "diocese";
DELETE FROM "province";
DELETE FROM "pastor";
DELETE FROM "prayer";
DELETE FROM "prayer_type";

-- 1. Table Pastor (On insère tous les responsables d'abord)
INSERT INTO "pastor" ("id", "church_id", "job", "full_name", "first_name", "last_name", "description", "image") VALUES
(1, NULL, 'Archevêque', 'Mgr Jean-Luc Bouilleret', 'Jean-Luc', 'Bouilleret', '<div><strong>Jean-Luc Bouilleret</strong>, né le 28 octobre 1953 à Arbois...</div>', NULL),
(2, NULL, 'Archevêque', 'Mgr Jean-Paul James', 'Jean-Paul', 'James', NULL, NULL),
(3, NULL, 'Archevêque', 'Mgr François Kalist', 'François', 'Kalist', NULL, NULL),
(4, NULL, 'Archevêque', 'Mgr Antoine Hérouard', 'Antoine', 'Hérouard', NULL, NULL),
(5, NULL, 'Archevêque', 'Mgr Laurent Le Boulc’h', 'Laurent', 'Le Boulc’h', NULL, NULL),
(6, NULL, 'Archevêque', 'Mgr Olivier de Germay', 'Olivier', 'De Germay', NULL, NULL),
(7, NULL, 'Cardinal', 'Son éminence le cardinal Jean-Marc Aveline', 'Jean-Marc', 'Aveline', NULL, NULL),
(8, NULL, 'Archevêque', 'Mgr Norbert Turini', 'Norbert', 'Turini', NULL, NULL),
(9, NULL, 'Archevêque', 'Mgr Laurent Ulrich', 'Laurent', 'Ulrich', NULL, NULL),
(10, NULL, 'Archevêque', 'Mgr Éric de Moulins-Beaufort', 'Eric', 'De Moulins-Beaufort', NULL, NULL),
(11, NULL, 'Archevêque', 'Mgr Pierre d’ Ornellas', 'Pierre', 'D’Ornellas', NULL, NULL),
(12, NULL, 'Archevêque', 'Mgr Dominique Lebrun', 'Dominique', 'Lebrun', NULL, NULL),
(13, NULL, 'Archevêque', 'Mgr Guy de Kerimel', 'Guy', 'De Kerimel', NULL, NULL),
(14, NULL, 'Archevêque', 'Mgr Vincent Jordy', 'Vincent', 'Jordy', NULL, NULL);

-- 2. Table Province (Dépend des Pastors)
INSERT INTO "province" ("id", "name", "responsable_id") VALUES
(1, 'Province de Besançon', 1), (2, 'Province de Bordeaux', 2), (3, 'Province de Clermont', 3),
(4, 'Province de Dijon', 4), (5, 'Province de Lille', 5), (6, 'Province de Lyon', 6),
(7, 'Province de Marseille', 7), (8, 'Province de Montpellier', 8), (9, 'Province de Paris', 9),
(10, 'Province de Poitiers', NULL), (11, 'Province de Reims', 10), (12, 'Province de Rennes', 11),
(13, 'Province de Rouen', 12), (14, 'Province de Toulouse', 13), (15, 'Province de Tours', 14);

-- 3. Tables de types (Sans dépendances)
INSERT INTO "church_type" ("id", "name", "image", "comments") VALUES
(1, 'Chapelle', 'chapelle.png', 'Une chapelle est...'),
(2, 'Paroisse', 'eglise.png', 'Une paroisse est...'),
(3, 'Basilique', 'basilique.png', 'Une basilique est...'),
(4, 'Cathédrale', 'cathedrale.png', 'Une cathédrale est...');

INSERT INTO "event_type" ("id", "name", "image", "comments") VALUES
(1, 'Messe', 'messe.jpg', 'Célébration...'),
(2, 'Confession', 'confession.jpg', 'La confession est...'),
(3, 'Accueil paroissial', 'accueil.jpg', 'Accueil à votre service...');

INSERT INTO "prayer_type" ("id", "name", "comments", "created_at", "image") VALUES
(1, 'Chapelet', 'Dévotion au rosaire', NULL, 'chapelet.jpg'),
(2, 'Neuvaine', 'Prière de neuf jours', NULL, 'neuvaine.png'),
(3, 'Litanie', 'Suite de prières liturgiques', NULL, 'litanie.webp');

-- 4. Table Diocese (Dépend des Provinces)
INSERT INTO "diocese" ("id", "province_id", "name", "address", "latitude", "longitude", "postal_code", "city", "telephone", "fax", "mail", "website", "image") VALUES
(1, 2, 'Diocèse d''Agen', '5, rue Roger Johan', 44.20372, 0.62549, '47000', 'Agen', '05 53 66 10 23', NULL, NULL, 'https://diocese47.fr', 'diocese-agen.png'),
(2, 2, 'Diocèse d''Aire et Dax', '100, avenue Francis Planté', 43.69801, 1.04087, '40100', 'Dax', '05 58 56 92 69', NULL, NULL, 'http://www.diocese40.fr/', 'diocese-aire-dax-logo.png');

-- 5. Table Church (Dépend des Dioceses)
INSERT INTO "church" ("id", "diocese_id", "image", "name", "address", "postal_code", "city", "latitude", "longitude", "courriel", "website", "telephone", "type_id", "profile_photo", "image_url") VALUES
(1, 1, 'saint-martin-de-tours.jpg', 'Paroisse Saint Martin du Dropt', '37, boulevard Jean Brisseau', '47120', 'Duras', 44.677, 0.187, NULL, 'http://paroissesaintmartindudropt47.over-blog.com', '05 24 30 50 81', 2, NULL, NULL);

-- 6. Table Event (Dépend des Churches et EventTypes)
INSERT INTO "event" ("id", "type_id", "church_id", "name", "comments", "monday_start", "monday_end", "tuesday_start", "tuesday_end", "wednesday_start", "wednesday_end", "thursday_start", "thursday_end", "friday_start", "friday_end", "saturday_start", "saturday_end", "sunday_start", "monday_comments", "tuesday_comments", "wednesday_comments", "thursday_comments", "friday_comments", "saturday_comments", "sunday_comments", "sunday_end") VALUES
(2, 3, 1, 'Accueil', NULL, NULL, NULL, '09:00:00', '12:00:00', NULL, NULL, NULL, NULL, '09:00:00', '12:00:00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

-- 7. Table Prayer (Dépend des PrayerTypes)
INSERT INTO "prayer" ("id", "prayer_type_id", "saint_id", "backdrop", "title", "subtitle", "image", "source", "body", "created_at") VALUES
(1, 1, NULL, 'sainte-faustine-jesus.jpg', 'Chapelet de la miséricorde divine', '« Les âmes... »', 'chapelet-divine-misericorde.png', NULL, 'La prière...', '2025-01-01 12:00:00');

-- Insertion de l'administrateur
-- Mot de passe : admin123
INSERT INTO "user" (
    "email", 
    "roles", 
    "password", 
    "nom", 
    "prenom", 
    "pseudo", 
    "photo", 
    "created_at", 
    "nb_connections"
) VALUES (
    'admin@cathobook.fr', 
    '["ROLE_ADMIN"]', 
    '$2y$13$h6uVlD8X6I4xG5k3yE.fO.7yDk9Mv6G3A4f0W5h7K9a2b3c4d5e6f', 
    'Cathobook', 
    'Admin', 
    'SuperAdmin', 
    'admin.png', 
    CURRENT_TIMESTAMP, 
    0
);

COMMIT;
PRAGMA foreign_keys = ON;