-- Donnees de demo realistes pour tester Lustra / SuitePro SaaS.
-- Cible par defaut: entreprise du site actuel.
--
-- Comptes crees:
--   seed.admin.<company_id>@lustra-seed.test
--   seed.emma.<company_id>@lustra-seed.test
--   seed.matteo.<company_id>@lustra-seed.test
--   seed.jonas.<company_id>@lustra-seed.test
--   client.sophie.<company_id>@lustra-seed.test
--   client.marc.<company_id>@lustra-seed.test
--   client.nina.<company_id>@lustra-seed.test
--   client.lucas.<company_id>@lustra-seed.test
--   client.amina.<company_id>@lustra-seed.test
--
-- Mot de passe pour tous les comptes de demo: Test1234!
--
-- Le script est relancable: il supprime uniquement les donnees avec le
-- domaine @lustra-seed.test et les numeros SEED-DEV/SEED-FAC.

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

SET @target_slug = 'audit-nettoyage-1781088147002';
SET @seed_domain = '@lustra-seed.test';
SET @password_hash = '$2b$10$rI2akutbxLibXUfOUBNJr.1CfnG8CaAsmVBzHUQ4Vd.qRt/SEUiSS';

INSERT INTO companies
  (slug, name, legal_name, email, phone, address, website_url, primary_color, secondary_color, vat_enabled, vat_rate, vat_label, iban, service_zones_json, public_site_enabled, status, created_at)
VALUES
  (@target_slug, 'Audit Nettoyage', 'Audit Nettoyage Sarl', 'contact@audit-nettoyage.test', '+41 21 555 10 10', 'Rue du Centre 12\n1003 Lausanne', 'https://lustra.digicotterd.com', '#0b6b88', '#f3b23c', 1, 8.10, 'TVA 8.1%', 'CH9300762011623852957', JSON_ARRAY('Lausanne','Morges','Nyon','Vevey','Montreux'), 1, 'active', NOW())
ON DUPLICATE KEY UPDATE
  updated_at = updated_at;

SET @company_id = (SELECT id FROM companies WHERE slug = @target_slug LIMIT 1);
SET @seed_quote_prefix = CONCAT('SEED-DEV-', @company_id, '-');
SET @seed_invoice_prefix = CONCAT('SEED-FAC-', @company_id, '-');
SET @seed_recurring_prefix = CONCAT('SEED-RG-', @company_id, '-');

START TRANSACTION;

DROP TEMPORARY TABLE IF EXISTS tmp_seed_users;
DROP TEMPORARY TABLE IF EXISTS tmp_seed_quotes;
DROP TEMPORARY TABLE IF EXISTS tmp_seed_invoices;
DROP TEMPORARY TABLE IF EXISTS tmp_seed_interventions;

CREATE TEMPORARY TABLE tmp_seed_users AS
SELECT id
FROM users
WHERE company_id = @company_id
  AND email LIKE CONCAT('%', @seed_domain);

CREATE TEMPORARY TABLE tmp_seed_quotes AS
SELECT id
FROM quotes
WHERE company_id = @company_id
  AND quote_number LIKE CONCAT(@seed_quote_prefix, '%');

CREATE TEMPORARY TABLE tmp_seed_invoices AS
SELECT id
FROM invoices
WHERE company_id = @company_id
  AND invoice_number LIKE CONCAT(@seed_invoice_prefix, '%');

CREATE TEMPORARY TABLE tmp_seed_interventions AS
SELECT id
FROM interventions
WHERE company_id = @company_id
  AND (
    quote_id IN (SELECT id FROM tmp_seed_quotes)
    OR invoice_id IN (SELECT id FROM tmp_seed_invoices)
    OR user_id IN (
      SELECT id FROM users
      WHERE company_id = @company_id AND email LIKE CONCAT('%', @seed_domain)
    )
    OR assigned_user_id IN (
      SELECT id FROM users
      WHERE company_id = @company_id AND email LIKE CONCAT('%', @seed_domain)
    )
    OR recurring_group LIKE CONCAT(@seed_recurring_prefix, '%')
  );

DELETE FROM payments
WHERE company_id = @company_id
  AND invoice_id IN (SELECT id FROM tmp_seed_invoices);

DELETE FROM invoice_items
WHERE company_id = @company_id
  AND invoice_id IN (SELECT id FROM tmp_seed_invoices);

DELETE FROM invoices
WHERE company_id = @company_id
  AND id IN (SELECT id FROM tmp_seed_invoices);

DELETE FROM quote_acceptances
WHERE company_id = @company_id
  AND (quote_id IN (SELECT id FROM tmp_seed_quotes) OR user_id IN (SELECT id FROM tmp_seed_users));

DELETE FROM quote_items
WHERE company_id = @company_id
  AND quote_id IN (SELECT id FROM tmp_seed_quotes);

DELETE FROM reviews
WHERE company_id = @company_id
  AND (intervention_id IN (SELECT id FROM tmp_seed_interventions) OR user_id IN (SELECT id FROM tmp_seed_users));

DELETE FROM field_reports
WHERE company_id = @company_id
  AND (intervention_id IN (SELECT id FROM tmp_seed_interventions) OR user_id IN (SELECT id FROM tmp_seed_users));

DELETE FROM field_time_entries
WHERE company_id = @company_id
  AND (intervention_id IN (SELECT id FROM tmp_seed_interventions) OR user_id IN (SELECT id FROM tmp_seed_users));

DELETE FROM intervention_staff
WHERE company_id = @company_id
  AND (intervention_id IN (SELECT id FROM tmp_seed_interventions) OR user_id IN (SELECT id FROM tmp_seed_users));

DELETE FROM interventions
WHERE company_id = @company_id
  AND id IN (SELECT id FROM tmp_seed_interventions);

DELETE FROM quotes
WHERE company_id = @company_id
  AND id IN (SELECT id FROM tmp_seed_quotes);

DELETE FROM quote_requests
WHERE company_id = @company_id
  AND (contact_email LIKE CONCAT('%', @seed_domain) OR user_id IN (SELECT id FROM tmp_seed_users));

DELETE FROM clients_profiles
WHERE company_id = @company_id
  AND user_id IN (SELECT id FROM tmp_seed_users);

DELETE FROM users
WHERE company_id = @company_id
  AND id IN (SELECT id FROM tmp_seed_users);

DELETE FROM bank_accounts
WHERE company_id = @company_id
  AND label = 'Seed Banque Test';

SET @admin_email = CONCAT('seed.admin.', @company_id, @seed_domain);
SET @emp1_email = CONCAT('seed.emma.', @company_id, @seed_domain);
SET @emp2_email = CONCAT('seed.matteo.', @company_id, @seed_domain);
SET @emp3_email = CONCAT('seed.jonas.', @company_id, @seed_domain);
SET @client1_email = CONCAT('client.sophie.', @company_id, @seed_domain);
SET @client2_email = CONCAT('client.marc.', @company_id, @seed_domain);
SET @client3_email = CONCAT('client.nina.', @company_id, @seed_domain);
SET @client4_email = CONCAT('client.lucas.', @company_id, @seed_domain);
SET @client5_email = CONCAT('client.amina.', @company_id, @seed_domain);

INSERT INTO users
  (company_id, role, email, password_hash, first_name, last_name, phone, status, internal_hourly_rate, must_change_password, consent_privacy, created_at)
VALUES
  (@company_id, 'admin', @admin_email, @password_hash, 'Camille', 'Admin', '+41 79 100 00 01', 'active', 65.00, 0, 1, DATE_SUB(NOW(), INTERVAL 120 DAY)),
  (@company_id, 'employee', @emp1_email, @password_hash, 'Emma', 'Rossi', '+41 79 100 00 11', 'active', 32.00, 0, 1, DATE_SUB(NOW(), INTERVAL 100 DAY)),
  (@company_id, 'employee', @emp2_email, @password_hash, 'Matteo', 'Keller', '+41 79 100 00 12', 'active', 34.00, 0, 1, DATE_SUB(NOW(), INTERVAL 92 DAY)),
  (@company_id, 'employee', @emp3_email, @password_hash, 'Jonas', 'Muller', '+41 79 100 00 13', 'active', 30.00, 0, 1, DATE_SUB(NOW(), INTERVAL 75 DAY)),
  (@company_id, 'client', @client1_email, @password_hash, 'Sophie', 'Durand', '+41 78 210 10 10', 'active', NULL, 0, 1, DATE_SUB(NOW(), INTERVAL 80 DAY)),
  (@company_id, 'client', @client2_email, @password_hash, 'Marc', 'Weber', '+41 78 220 20 20', 'active', NULL, 0, 1, DATE_SUB(NOW(), INTERVAL 64 DAY)),
  (@company_id, 'client', @client3_email, @password_hash, 'Nina', 'Morel', '+41 78 230 30 30', 'active', NULL, 0, 1, DATE_SUB(NOW(), INTERVAL 41 DAY)),
  (@company_id, 'client', @client4_email, @password_hash, 'Lucas', 'Martin', '+41 78 240 40 40', 'active', NULL, 0, 1, DATE_SUB(NOW(), INTERVAL 29 DAY)),
  (@company_id, 'client', @client5_email, @password_hash, 'Amina', 'Benali', '+41 78 250 50 50', 'active', NULL, 0, 1, DATE_SUB(NOW(), INTERVAL 14 DAY));

SET @admin_id = (SELECT id FROM users WHERE email = @admin_email LIMIT 1);
SET @emp1_id = (SELECT id FROM users WHERE email = @emp1_email LIMIT 1);
SET @emp2_id = (SELECT id FROM users WHERE email = @emp2_email LIMIT 1);
SET @emp3_id = (SELECT id FROM users WHERE email = @emp3_email LIMIT 1);
SET @client1_id = (SELECT id FROM users WHERE email = @client1_email LIMIT 1);
SET @client2_id = (SELECT id FROM users WHERE email = @client2_email LIMIT 1);
SET @client3_id = (SELECT id FROM users WHERE email = @client3_email LIMIT 1);
SET @client4_id = (SELECT id FROM users WHERE email = @client4_email LIMIT 1);
SET @client5_id = (SELECT id FROM users WHERE email = @client5_email LIMIT 1);

INSERT INTO clients_profiles
  (user_id, company_id, client_type, company_name, address, postal_code, city, internal_note, created_at)
VALUES
  (@client1_id, @company_id, 'entreprise', 'PPE Residence Le Floreal', 'Avenue de la Gare 18', '1003', 'Lausanne', 'Client recurrent, concierge disponible le matin.', DATE_SUB(NOW(), INTERVAL 80 DAY)),
  (@client2_id, @company_id, 'entreprise', 'Cabinet Dentaire Leman', 'Route de Morges 42', '1023', 'Crissier', 'Intervenir hors horaires patients.', DATE_SUB(NOW(), INTERVAL 64 DAY)),
  (@client3_id, @company_id, 'entreprise', 'Restaurant Quai 7', 'Quai Perdonnet 7', '1800', 'Vevey', 'Cuisine a nettoyer avant service du midi.', DATE_SUB(NOW(), INTERVAL 41 DAY)),
  (@client4_id, @company_id, 'particulier', NULL, 'Chemin des Cedres 4', '1009', 'Pully', 'Appartement 4.5 pieces, chat sur place.', DATE_SUB(NOW(), INTERVAL 29 DAY)),
  (@client5_id, @company_id, 'entreprise', 'Ecole Atlas', 'Rue de Bourg 21', '1003', 'Lausanne', 'Demande devis vitres et entretien hebdomadaire.', DATE_SUB(NOW(), INTERVAL 14 DAY))
ON DUPLICATE KEY UPDATE
  client_type = VALUES(client_type),
  company_name = VALUES(company_name),
  address = VALUES(address),
  postal_code = VALUES(postal_code),
  city = VALUES(city),
  internal_note = VALUES(internal_note);

INSERT INTO settings (company_id, name, value, updated_at)
VALUES
  (@company_id, 'responsible_employee_ids', CONCAT(@emp1_id, ',', @emp2_id, ',', @emp3_id), NOW()),
  (@company_id, 'quote_price_regular_hourly', '45', NOW()),
  (@company_id, 'quote_price_end_room', '65', NOW()),
  (@company_id, 'quote_price_window_inside_outside', '18', NOW())
ON DUPLICATE KEY UPDATE
  value = VALUES(value),
  updated_at = NOW();

INSERT INTO bank_accounts
  (company_id, label, holder_name, holder_street, holder_building_no, holder_postal_code, holder_city, holder_country, iban, bank_name, is_default, is_active, created_at)
VALUES
  (@company_id, 'Seed Banque Test', 'Audit Nettoyage Sarl', 'Rue du Centre', '12', '1003', 'Lausanne', 'CH', 'CH9300762011623852957', 'Banque Cantonale Vaudoise', 1, 1, NOW());

SET @bank_id = LAST_INSERT_ID();

INSERT INTO quote_requests
  (company_id, user_id, contact_first_name, contact_last_name, contact_email, contact_phone, wants_account, service_type, status, address, desired_date, data_json, comment, internal_note, created_at)
VALUES
  (@company_id, @client1_id, 'Sophie', 'Durand', @client1_email, '+41 78 210 10 10', 1, 'Fin de bail', 'devis_accepte', 'Avenue de la Gare 18, 1003 Lausanne', DATE_SUB(CURDATE(), INTERVAL 18 DAY), JSON_OBJECT('surface_m2', 145, 'rooms', 5.5, 'windows', 12, 'balcony', true, 'parking', true), 'Nettoyage complet avant etat des lieux.', 'Client demande une equipe de 2 personnes.', DATE_SUB(NOW(), INTERVAL 31 DAY));
SET @qr1_id = LAST_INSERT_ID();

INSERT INTO quote_requests
  (company_id, user_id, contact_first_name, contact_last_name, contact_email, contact_phone, wants_account, service_type, status, address, desired_date, data_json, comment, internal_note, created_at)
VALUES
  (@company_id, @client2_id, 'Marc', 'Weber', @client2_email, '+41 78 220 20 20', 1, 'Nettoyage medical', 'devis_envoye', 'Route de Morges 42, 1023 Crissier', DATE_ADD(CURDATE(), INTERVAL 6 DAY), JSON_OBJECT('surface_m2', 210, 'frequency', '2x semaine', 'sensitive_area', true, 'after_hours', true), 'Cabinet dentaire: sols, sanitaires, salle attente.', 'Relancer si pas de retour sous 48h.', DATE_SUB(NOW(), INTERVAL 7 DAY));
SET @qr2_id = LAST_INSERT_ID();

INSERT INTO quote_requests
  (company_id, user_id, contact_first_name, contact_last_name, contact_email, contact_phone, wants_account, service_type, status, address, desired_date, data_json, comment, internal_note, created_at)
VALUES
  (@company_id, @client3_id, 'Nina', 'Morel', @client3_email, '+41 78 230 30 30', 1, 'Nettoyage restaurant', 'devis_refuse', 'Quai Perdonnet 7, 1800 Vevey', DATE_ADD(CURDATE(), INTERVAL 5 DAY), JSON_OBJECT('surface_m2', 95, 'kitchen', true, 'terrace', true, 'monthly_contract', false), 'Besoin ponctuel avant controle hygiene.', 'Refus pour budget, garder contact.', DATE_SUB(NOW(), INTERVAL 12 DAY));
SET @qr3_id = LAST_INSERT_ID();

INSERT INTO quote_requests
  (company_id, user_id, contact_first_name, contact_last_name, contact_email, contact_phone, wants_account, service_type, status, address, desired_date, data_json, comment, internal_note, created_at)
VALUES
  (@company_id, @client4_id, 'Lucas', 'Martin', @client4_email, '+41 78 240 40 40', 1, 'Menage regulier', 'en_analyse', 'Chemin des Cedres 4, 1009 Pully', DATE_ADD(CURDATE(), INTERVAL 9 DAY), JSON_OBJECT('surface_m2', 110, 'frequency', 'hebdomadaire', 'pets', true, 'preferred_day', 'vendredi'), 'Menage 3h par semaine, appartement occupe.', 'Prevoir devis avec abonnement mensuel.', DATE_SUB(NOW(), INTERVAL 1 DAY));
SET @qr4_id = LAST_INSERT_ID();

INSERT INTO quote_requests
  (company_id, user_id, contact_first_name, contact_last_name, contact_email, contact_phone, wants_account, service_type, status, address, desired_date, data_json, comment, internal_note, created_at)
VALUES
  (@company_id, @client5_id, 'Amina', 'Benali', @client5_email, '+41 78 250 50 50', 1, 'Vitres', 'nouvelle', 'Rue de Bourg 21, 1003 Lausanne', DATE_ADD(CURDATE(), INTERVAL 14 DAY), JSON_OBJECT('windows', 42, 'inside_outside', true, 'difficult_access', false, 'school_holidays', true), 'Nettoyage vitres pendant les vacances scolaires.', 'Nouvelle demande a traiter.', NOW());
SET @qr5_id = LAST_INSERT_ID();

INSERT INTO quotes
  (company_id, user_id, quote_request_id, quote_number, service_type, intervention_address, proposed_date, description, subtotal, vat_enabled, vat_rate, vat_label, vat_amount, total, deposit_amount, balance_amount, conditions, notes, status, valid_until, sent_at, created_at)
VALUES
  (@company_id, @client1_id, @qr1_id, CONCAT(@seed_quote_prefix, '0001'), 'Fin de bail', 'Avenue de la Gare 18, 1003 Lausanne', DATE_SUB(CURDATE(), INTERVAL 18 DAY), 'Nettoyage complet fin de bail: cuisine, sanitaires, vitres, balcon, cave et garage.', 1260.00, 1, 8.10, 'TVA 8.1%', 102.06, 1362.06, 400.00, 962.06, 'Paiement a 10 jours. Materiel inclus.', 'Devis accepte par signature client.', 'accepte', DATE_SUB(CURDATE(), INTERVAL 2 DAY), DATE_SUB(NOW(), INTERVAL 28 DAY), DATE_SUB(NOW(), INTERVAL 29 DAY));
SET @q1_id = LAST_INSERT_ID();

INSERT INTO quotes
  (company_id, user_id, quote_request_id, quote_number, service_type, intervention_address, proposed_date, description, subtotal, vat_enabled, vat_rate, vat_label, vat_amount, total, deposit_amount, balance_amount, conditions, notes, status, valid_until, sent_at, created_at)
VALUES
  (@company_id, @client2_id, @qr2_id, CONCAT(@seed_quote_prefix, '0002'), 'Nettoyage medical', 'Route de Morges 42, 1023 Crissier', DATE_ADD(CURDATE(), INTERVAL 6 DAY), 'Entretien medical 2x par semaine: sols, salle attente, sanitaires, surfaces de contact.', 1850.00, 1, 8.10, 'TVA 8.1%', 149.85, 1999.85, 0.00, 1999.85, 'Contrat mensuel renouvelable, produits desinfectants inclus.', 'Devis envoye, attente validation.', 'envoye', DATE_ADD(CURDATE(), INTERVAL 14 DAY), DATE_SUB(NOW(), INTERVAL 2 DAY), DATE_SUB(NOW(), INTERVAL 3 DAY));
SET @q2_id = LAST_INSERT_ID();

INSERT INTO quotes
  (company_id, user_id, quote_request_id, quote_number, service_type, intervention_address, proposed_date, description, subtotal, vat_enabled, vat_rate, vat_label, vat_amount, total, deposit_amount, balance_amount, conditions, notes, status, valid_until, sent_at, created_at)
VALUES
  (@company_id, @client3_id, @qr3_id, CONCAT(@seed_quote_prefix, '0003'), 'Nettoyage restaurant', 'Quai Perdonnet 7, 1800 Vevey', DATE_ADD(CURDATE(), INTERVAL 5 DAY), 'Nettoyage approfondi cuisine, salle, terrasse et sanitaires.', 980.00, 1, 8.10, 'TVA 8.1%', 79.38, 1059.38, 0.00, 1059.38, 'Intervention hors horaires de service.', 'Client a refuse le devis pour budget.', 'refuse', DATE_ADD(CURDATE(), INTERVAL 5 DAY), DATE_SUB(NOW(), INTERVAL 10 DAY), DATE_SUB(NOW(), INTERVAL 11 DAY));
SET @q3_id = LAST_INSERT_ID();

INSERT INTO quotes
  (company_id, user_id, quote_request_id, quote_number, service_type, intervention_address, proposed_date, description, subtotal, vat_enabled, vat_rate, vat_label, vat_amount, total, deposit_amount, balance_amount, conditions, notes, status, valid_until, sent_at, created_at)
VALUES
  (@company_id, @client4_id, @qr4_id, CONCAT(@seed_quote_prefix, '0004'), 'Menage regulier', 'Chemin des Cedres 4, 1009 Pully', DATE_ADD(CURDATE(), INTERVAL 9 DAY), 'Forfait hebdomadaire 3h: cuisine, salle de bain, sols, poussieres.', 540.00, 1, 8.10, 'TVA 8.1%', 43.74, 583.74, 0.00, 583.74, 'Facturation mensuelle en fin de mois.', 'Brouillon a completer apres visite.', 'brouillon', DATE_ADD(CURDATE(), INTERVAL 21 DAY), NULL, NOW());
SET @q4_id = LAST_INSERT_ID();

INSERT INTO quotes
  (company_id, user_id, quote_request_id, quote_number, service_type, intervention_address, proposed_date, description, subtotal, vat_enabled, vat_rate, vat_label, vat_amount, total, deposit_amount, balance_amount, conditions, notes, status, valid_until, sent_at, created_at)
VALUES
  (@company_id, @client1_id, NULL, CONCAT(@seed_quote_prefix, '0005'), 'Nettoyage regulier PPE', 'Avenue de la Gare 18, 1003 Lausanne', DATE_SUB(CURDATE(), INTERVAL 7 DAY), 'Entretien recurrent PPE: halls, escaliers, ascenseur, local poubelles.', 650.00, 1, 8.10, 'TVA 8.1%', 52.65, 702.65, 0.00, 702.65, 'Passage hebdomadaire, facture mensuelle.', 'Contrat recurrent accepte.', 'accepte', DATE_ADD(CURDATE(), INTERVAL 30 DAY), DATE_SUB(NOW(), INTERVAL 20 DAY), DATE_SUB(NOW(), INTERVAL 22 DAY));
SET @q5_id = LAST_INSERT_ID();

INSERT INTO quote_items
  (company_id, quote_id, description, quantity, unit, unit_price, total, position)
VALUES
  (@company_id, @q1_id, 'Nettoyage cuisine complete', 1, 'forfait', 380.00, 380.00, 1),
  (@company_id, @q1_id, 'Sanitaires et detartrage', 2, 'piece', 120.00, 240.00, 2),
  (@company_id, @q1_id, 'Vitres, cadres et stores', 12, 'piece', 28.00, 336.00, 3),
  (@company_id, @q1_id, 'Balcon, cave et garage', 1, 'forfait', 304.00, 304.00, 4),
  (@company_id, @q2_id, 'Entretien medical mensuel', 1, 'mois', 1680.00, 1680.00, 1),
  (@company_id, @q2_id, 'Produits desinfectants certifies', 1, 'forfait', 170.00, 170.00, 2),
  (@company_id, @q3_id, 'Cuisine professionnelle', 1, 'forfait', 520.00, 520.00, 1),
  (@company_id, @q3_id, 'Salle, terrasse et sanitaires', 1, 'forfait', 460.00, 460.00, 2),
  (@company_id, @q4_id, 'Menage hebdomadaire 3h', 12, 'heure', 45.00, 540.00, 1),
  (@company_id, @q5_id, 'Entretien PPE hebdomadaire', 1, 'mois', 650.00, 650.00, 1);

INSERT INTO quote_acceptances
  (company_id, quote_id, user_id, accepted_at, ip_address, user_agent)
VALUES
  (@company_id, @q1_id, @client1_id, DATE_SUB(NOW(), INTERVAL 27 DAY), '127.0.0.1', 'Seed SQL'),
  (@company_id, @q5_id, @client1_id, DATE_SUB(NOW(), INTERVAL 18 DAY), '127.0.0.1', 'Seed SQL');

INSERT INTO interventions
  (company_id, user_id, assigned_user_id, quote_id, service_type, address, starts_at, ends_at, estimated_duration_minutes, hourly_rate, actual_duration_minutes, completed_at, expected_price, status, planning_status, admin_comment, client_comment, work_note, recurring_group, created_at)
VALUES
  (@company_id, @client1_id, @emp1_id, @q1_id, 'Fin de bail', 'Avenue de la Gare 18, 1003 Lausanne', CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 08:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 14:30:00'), 390, 58.00, 360, CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 14:35:00'), 1362.06, 'terminee', 'completed', 'Etat des lieux prevu le lendemain.', 'Merci de verifier les stores.', 'Cuisine et sanitaires termines, photos avant/apres ok.', NULL, DATE_SUB(NOW(), INTERVAL 25 DAY)),
  (@company_id, @client1_id, @emp3_id, @q5_id, 'Nettoyage regulier PPE', 'Avenue de la Gare 18, 1003 Lausanne', CONCAT(DATE_SUB(CURDATE(), INTERVAL 7 DAY), ' 08:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 7 DAY), ' 11:30:00'), 210, 45.00, 210, CONCAT(DATE_SUB(CURDATE(), INTERVAL 7 DAY), ' 11:40:00'), 702.65, 'terminee', 'completed', 'Passage hebdomadaire PPE.', 'Local poubelles a controler.', 'Halls, escaliers et ascenseur termines.', CONCAT(@seed_recurring_prefix, 'PPE-FLOREAL'), DATE_SUB(NOW(), INTERVAL 10 DAY)),
  (@company_id, @client2_id, @emp1_id, @q2_id, 'Nettoyage medical', 'Route de Morges 42, 1023 Crissier', CONCAT(CURDATE(), ' 08:00:00'), CONCAT(CURDATE(), ' 12:00:00'), 240, 62.00, 0, NULL, 1999.85, 'en_cours', 'in_progress', 'Premiere intervention test en cours.', 'Reception par assistante.', NULL, NULL, DATE_SUB(NOW(), INTERVAL 2 DAY)),
  (@company_id, @client5_id, @emp2_id, NULL, 'Vitres', 'Rue de Bourg 21, 1003 Lausanne', CONCAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), ' 09:00:00'), CONCAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), ' 13:00:00'), 240, 52.00, 0, NULL, 680.00, 'confirmee', 'planned', 'Prevoir perche telescopique.', 'Acces par concierge.', NULL, NULL, NOW()),
  (@company_id, @client3_id, @emp2_id, NULL, 'Nettoyage restaurant', 'Quai Perdonnet 7, 1800 Vevey', CONCAT(DATE_ADD(CURDATE(), INTERVAL 5 DAY), ' 06:00:00'), CONCAT(DATE_ADD(CURDATE(), INTERVAL 5 DAY), ' 10:00:00'), 240, 60.00, 0, NULL, 780.00, 'planifiee', 'planned', 'A confirmer apres acompte.', 'Intervention avant service.', NULL, NULL, NOW()),
  (@company_id, @client4_id, @emp3_id, @q4_id, 'Menage regulier', 'Chemin des Cedres 4, 1009 Pully', CONCAT(DATE_ADD(CURDATE(), INTERVAL 8 DAY), ' 14:00:00'), CONCAT(DATE_ADD(CURDATE(), INTERVAL 8 DAY), ' 17:00:00'), 180, 45.00, 0, NULL, 583.74, 'reportee', 'rescheduled', 'Client a demande report.', 'Nouvelle date a confirmer.', NULL, CONCAT(@seed_recurring_prefix, 'MARTIN'), DATE_SUB(NOW(), INTERVAL 1 DAY)),
  (@company_id, @client3_id, @emp2_id, @q3_id, 'Nettoyage restaurant', 'Quai Perdonnet 7, 1800 Vevey', CONCAT(DATE_SUB(CURDATE(), INTERVAL 3 DAY), ' 06:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 3 DAY), ' 10:00:00'), 240, 60.00, 0, NULL, 1059.38, 'annulee', 'cancelled', 'Annule suite refus devis.', 'Client reporte a plus tard.', NULL, NULL, DATE_SUB(NOW(), INTERVAL 9 DAY));

SET @i1_id = LAST_INSERT_ID();
SET @i2_id = @i1_id + 1;
SET @i3_id = @i1_id + 2;
SET @i4_id = @i1_id + 3;
SET @i5_id = @i1_id + 4;
SET @i6_id = @i1_id + 5;
SET @i7_id = @i1_id + 6;

INSERT IGNORE INTO intervention_staff
  (company_id, intervention_id, user_id, created_at)
VALUES
  (@company_id, @i1_id, @emp1_id, NOW()),
  (@company_id, @i1_id, @emp2_id, NOW()),
  (@company_id, @i2_id, @emp3_id, NOW()),
  (@company_id, @i3_id, @emp1_id, NOW()),
  (@company_id, @i3_id, @emp2_id, NOW()),
  (@company_id, @i4_id, @emp2_id, NOW()),
  (@company_id, @i5_id, @emp2_id, NOW()),
  (@company_id, @i6_id, @emp3_id, NOW()),
  (@company_id, @i7_id, @emp2_id, NOW());

INSERT INTO invoices
  (company_id, user_id, quote_id, intervention_id, bank_account_id, invoice_number, service_type, description, subtotal, vat_enabled, vat_rate, vat_label, vat_amount, total, paid_amount, due_date, conditions, status, sent_at, created_at)
VALUES
  (@company_id, @client1_id, @q1_id, @i1_id, @bank_id, CONCAT(@seed_invoice_prefix, '0001'), 'Fin de bail', 'Facture suite nettoyage complet fin de bail.', 1260.00, 1, 8.10, 'TVA 8.1%', 102.06, 1362.06, 1362.06, DATE_SUB(CURDATE(), INTERVAL 8 DAY), 'Paiement recu par virement bancaire.', 'payee', DATE_SUB(NOW(), INTERVAL 17 DAY), DATE_SUB(NOW(), INTERVAL 17 DAY)),
  (@company_id, @client1_id, @q5_id, @i2_id, @bank_id, CONCAT(@seed_invoice_prefix, '0002'), 'Nettoyage regulier PPE', 'Facture mensuelle PPE - entretien parties communes.', 650.00, 1, 8.10, 'TVA 8.1%', 52.65, 702.65, 300.00, DATE_ADD(CURDATE(), INTERVAL 7 DAY), 'Solde a regler sous 7 jours.', 'partiellement_payee', DATE_SUB(NOW(), INTERVAL 6 DAY), DATE_SUB(NOW(), INTERVAL 6 DAY)),
  (@company_id, @client2_id, @q2_id, NULL, @bank_id, CONCAT(@seed_invoice_prefix, '0003'), 'Nettoyage medical', 'Acompte de demarrage contrat medical.', 600.00, 1, 8.10, 'TVA 8.1%', 48.60, 648.60, 0.00, DATE_SUB(CURDATE(), INTERVAL 12 DAY), 'Acompte avant premiere intervention.', 'en_retard', DATE_SUB(NOW(), INTERVAL 21 DAY), DATE_SUB(NOW(), INTERVAL 21 DAY)),
  (@company_id, @client5_id, NULL, @i4_id, @bank_id, CONCAT(@seed_invoice_prefix, '0004'), 'Vitres', 'Intervention vitres ecole - acompte planification.', 300.00, 1, 8.10, 'TVA 8.1%', 24.30, 324.30, 0.00, DATE_ADD(CURDATE(), INTERVAL 20 DAY), 'Paiement a 20 jours.', 'envoyee', NOW(), NOW());

SET @inv1_id = LAST_INSERT_ID();
SET @inv2_id = @inv1_id + 1;
SET @inv3_id = @inv1_id + 2;
SET @inv4_id = @inv1_id + 3;

UPDATE interventions
SET invoice_id = @inv1_id
WHERE company_id = @company_id AND id = @i1_id;

UPDATE interventions
SET invoice_id = @inv2_id
WHERE company_id = @company_id AND id = @i2_id;

UPDATE interventions
SET invoice_id = @inv4_id
WHERE company_id = @company_id AND id = @i4_id;

INSERT INTO invoice_items
  (company_id, invoice_id, description, quantity, unit, unit_price, total, position)
VALUES
  (@company_id, @inv1_id, 'Nettoyage complet fin de bail', 1, 'forfait', 1260.00, 1260.00, 1),
  (@company_id, @inv2_id, 'Entretien PPE mensuel', 1, 'mois', 650.00, 650.00, 1),
  (@company_id, @inv3_id, 'Acompte contrat nettoyage medical', 1, 'forfait', 600.00, 600.00, 1),
  (@company_id, @inv4_id, 'Acompte intervention vitres', 1, 'forfait', 300.00, 300.00, 1);

INSERT INTO payments
  (company_id, invoice_id, amount, paid_at, method, note, created_at)
VALUES
  (@company_id, @inv1_id, 1362.06, DATE_SUB(CURDATE(), INTERVAL 9 DAY), 'virement', 'Paiement integral recu.', DATE_SUB(NOW(), INTERVAL 9 DAY)),
  (@company_id, @inv2_id, 300.00, DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'virement', 'Acompte client, solde ouvert.', DATE_SUB(NOW(), INTERVAL 2 DAY));

INSERT INTO field_time_entries
  (company_id, user_id, intervention_id, work_date, morning_starts_at, morning_ends_at, afternoon_starts_at, afternoon_ends_at, starts_at, ends_at, break_minutes, morning_minutes, afternoon_minutes, duration_minutes, latitude, longitude, expense_amount, expense_note, work_note, source_type, status, submitted_by, approved_by, approved_at, created_at, updated_at)
VALUES
  (@company_id, @emp1_id, @i1_id, DATE_SUB(CURDATE(), INTERVAL 18 DAY), CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 08:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 12:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 12:30:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 14:30:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 08:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 14:30:00'), 30, 240, 120, 360, 46.5196530, 6.6322730, 12.40, 'Parking client', 'Fin de bail realisee avec Matteo.', 'mobile', 'approved', @emp1_id, @admin_id, DATE_SUB(NOW(), INTERVAL 17 DAY), DATE_SUB(NOW(), INTERVAL 18 DAY), DATE_SUB(NOW(), INTERVAL 17 DAY)),
  (@company_id, @emp2_id, @i1_id, DATE_SUB(CURDATE(), INTERVAL 18 DAY), CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 08:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 12:00:00'), NULL, NULL, CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 08:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 18 DAY), ' 12:00:00'), 0, 240, NULL, 240, 46.5196530, 6.6322730, 0.00, NULL, 'Appui cuisine et vitres.', 'mobile', 'approved', @emp2_id, @admin_id, DATE_SUB(NOW(), INTERVAL 17 DAY), DATE_SUB(NOW(), INTERVAL 18 DAY), DATE_SUB(NOW(), INTERVAL 17 DAY)),
  (@company_id, @emp3_id, @i2_id, DATE_SUB(CURDATE(), INTERVAL 7 DAY), CONCAT(DATE_SUB(CURDATE(), INTERVAL 7 DAY), ' 08:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 7 DAY), ' 11:30:00'), NULL, NULL, CONCAT(DATE_SUB(CURDATE(), INTERVAL 7 DAY), ' 08:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 7 DAY), ' 11:30:00'), 0, 210, NULL, 210, 46.5196530, 6.6322730, 0.00, NULL, 'Passage PPE hebdomadaire.', 'manual', 'approved', @emp3_id, @admin_id, DATE_SUB(NOW(), INTERVAL 6 DAY), DATE_SUB(NOW(), INTERVAL 7 DAY), DATE_SUB(NOW(), INTERVAL 6 DAY)),
  (@company_id, @emp1_id, @i3_id, CURDATE(), CONCAT(CURDATE(), ' 08:00:00'), CONCAT(CURDATE(), ' 12:00:00'), NULL, NULL, CONCAT(CURDATE(), ' 08:00:00'), CONCAT(CURDATE(), ' 12:00:00'), 0, 240, NULL, 240, 46.5450000, 6.5750000, 0.00, NULL, 'Premiere matinee cabinet medical.', 'mobile', 'submitted', @emp1_id, NULL, NULL, NOW(), NOW()),
  (@company_id, @emp2_id, NULL, DATE_SUB(CURDATE(), INTERVAL 1 DAY), CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' 09:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' 11:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' 14:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' 16:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' 09:00:00'), CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' 16:00:00'), 60, 120, 120, 240, NULL, NULL, 18.90, 'Produits microfibre', 'Preparation materiel et stock.', 'manual', 'submitted', @emp2_id, NULL, NULL, DATE_SUB(NOW(), INTERVAL 1 DAY), NOW());

INSERT INTO field_reports
  (company_id, intervention_id, user_id, arrival_status, checklist_json, before_photo_path, after_photo_path, before_note, after_note, materials_used, client_signature_name, client_signature_path, status, customer_visible, created_at)
VALUES
  (@company_id, @i1_id, @emp1_id, 'on_time', JSON_ARRAY(
    JSON_OBJECT('label', 'Cuisine complete nettoyee', 'done', true, 'note', 'Four et hotte degraisses'),
    JSON_OBJECT('label', 'Sanitaires detartres', 'done', true, 'note', 'Robinetterie controlee'),
    JSON_OBJECT('label', 'Vitres et stores controles', 'done', true, 'note', '12 vitres terminees')
  ), NULL, NULL, 'Appartement vide, traces de calcaire importantes.', 'Controle final effectue avec la cliente.', 'Degraissant cuisine, anti-calcaire, chiffons microfibre.', 'Sophie Durand', NULL, 'signed', 1, DATE_SUB(NOW(), INTERVAL 18 DAY)),
  (@company_id, @i2_id, @emp3_id, 'on_time', JSON_ARRAY(
    JSON_OBJECT('label', 'Halls et escaliers', 'done', true, 'note', ''),
    JSON_OBJECT('label', 'Ascenseur', 'done', true, 'note', ''),
    JSON_OBJECT('label', 'Local poubelles', 'done', true, 'note', 'Sacs remplaces')
  ), NULL, NULL, 'Passage regulier PPE.', 'Rien a signaler.', 'Produit sols neutre.', 'Concierge PPE Floreal', NULL, 'submitted', 1, DATE_SUB(NOW(), INTERVAL 7 DAY));

INSERT INTO reviews
  (company_id, user_id, intervention_id, rating, private_comment, created_at)
VALUES
  (@company_id, @client1_id, @i1_id, 5, 'Equipe ponctuelle et etat des lieux valide sans remarque.', DATE_SUB(NOW(), INTERVAL 16 DAY)),
  (@company_id, @client1_id, @i2_id, 4, 'Bon passage, local poubelles mieux range.', DATE_SUB(NOW(), INTERVAL 6 DAY));

UPDATE quote_requests
SET status = 'planifiee', updated_at = NOW()
WHERE company_id = @company_id AND id = @qr1_id;

UPDATE quote_requests
SET status = 'devis_envoye', updated_at = NOW()
WHERE company_id = @company_id AND id = @qr2_id;

UPDATE quote_requests
SET status = 'devis_refuse', updated_at = NOW()
WHERE company_id = @company_id AND id = @qr3_id;

COMMIT;

SELECT
  @company_id AS company_id,
  @target_slug AS company_slug,
  'Seed termine' AS status,
  (SELECT COUNT(*) FROM users WHERE company_id = @company_id AND email LIKE CONCAT('%', @seed_domain)) AS seed_users,
  (SELECT COUNT(*) FROM quote_requests WHERE company_id = @company_id AND contact_email LIKE CONCAT('%', @seed_domain)) AS quote_requests,
  (SELECT COUNT(*) FROM quotes WHERE company_id = @company_id AND quote_number LIKE CONCAT(@seed_quote_prefix, '%')) AS quotes,
  (SELECT COUNT(*) FROM interventions WHERE company_id = @company_id AND (recurring_group LIKE CONCAT(@seed_recurring_prefix, '%') OR user_id IN (SELECT id FROM users WHERE company_id = @company_id AND email LIKE CONCAT('%', @seed_domain)))) AS interventions,
  (SELECT COUNT(*) FROM invoices WHERE company_id = @company_id AND invoice_number LIKE CONCAT(@seed_invoice_prefix, '%')) AS invoices,
  (SELECT COUNT(*) FROM field_time_entries WHERE company_id = @company_id AND user_id IN (SELECT id FROM users WHERE company_id = @company_id AND email LIKE CONCAT('%', @seed_domain))) AS time_entries;