-- Jeu de démonstration local PyramidCom.
-- Toutes les données sont identifiables par TEST et peuvent être réinjectées sans doublon.

DELETE FROM commercial_quotes
WHERE (request_type = 'sign' AND request_id IN (
  SELECT id FROM quote_requests WHERE reference LIKE 'TEST-%'
)) OR (request_type = 'textile' AND request_id IN (
  SELECT id FROM textile_quote_requests WHERE reference LIKE 'TEST-%'
));

DELETE FROM quote_requests WHERE reference LIKE 'TEST-%';
DELETE FROM textile_quote_requests WHERE reference LIKE 'TEST-%';
DELETE FROM neon_quote_requests WHERE reference LIKE 'TEST-%';
DELETE FROM professional_applications WHERE email LIKE '%.test@example.com';

INSERT INTO professional_applications
  (company, contact_name, email, phone, siret, activity, expected_volume, status, access_token, approved_at, created_at)
VALUES
  ('TEST — Enseignes Grand Est', 'Sophie Martin', 'revendeur.actif.test@example.com', '06 10 20 30 40', '90123456700011', 'Agence de communication et pose d’enseignes', '8 à 12 projets par mois', 'approved', 'test-revendeur-actif-2026', CAST(strftime('%s','now','-40 days') AS INTEGER) * 1000, CAST(strftime('%s','now','-45 days') AS INTEGER) * 1000),
  ('TEST — Com Factory Lyon', 'Karim Benali', 'revendeur.attente.test@example.com', '06 22 33 44 55', '90123456700029', 'Impression grand format et signalétique', '3 à 5 projets par mois', 'pending', NULL, NULL, CAST(strftime('%s','now','-2 days') AS INTEGER) * 1000),
  ('TEST — Studio Signal', 'Élodie Laurent', 'revendeur.suspendu.test@example.com', '06 34 45 56 67', '90123456700037', 'Design, covering et enseignes', '15 projets par trimestre', 'suspended', 'test-revendeur-suspendu-2026', CAST(strftime('%s','now','-120 days') AS INTEGER) * 1000, CAST(strftime('%s','now','-130 days') AS INTEGER) * 1000),
  ('TEST — Pub & Réseau', 'Thomas Girard', 'revendeur.refuse.test@example.com', '06 46 57 68 79', '90123456700045', 'Courtier en communication visuelle', 'Volume non défini', 'rejected', NULL, NULL, CAST(strftime('%s','now','-12 days') AS INTEGER) * 1000);

INSERT INTO quote_requests
  (reference, name, email, phone, company, profile_code, sign_text, letter_count, height_cm, width_cm, letter_spacing_cm, depth_mm, mounting, font, sign_color, light_color, position_x, position_y, visual_scale, night, estimated_price_cents, is_professional, status, created_at)
VALUES
  ('TEST-ENS-2601', 'Claire Robert', 'claire.test@example.com', '06 11 12 13 14', 'TEST — Maison Robert', 'P03', 'MAISON ROBERT', 12, 32, 420, 5, 80, 'individual', 'montserrat', '#f5f1e8', '#ffb15a', 50, 42, 92, 1, 286000, 0, 'new', CAST(strftime('%s','now','-3 hours') AS INTEGER) * 1000),
  ('TEST-ENS-2602', 'Sophie Martin', 'revendeur.actif.test@example.com', '06 10 20 30 40', 'TEST — Enseignes Grand Est', 'P20', 'BISTROT DES HALLES', 16, 40, 590, 6, 100, 'dibond', 'montserrat', '#e9e3d7', '#ffc26f', 52, 45, 98, 1, 498000, 1, 'review', CAST(strftime('%s','now','-1 day') AS INTEGER) * 1000),
  ('TEST-ENS-2603', 'Nicolas Bernard', 'nicolas.test@example.com', '06 21 22 23 24', 'TEST — Cabinet Bernard', 'P06', 'BERNARD AVOCATS', 14, 28, 380, 4, 60, 'rails', 'grotesk', '#c9aa73', '#ffffff', 48, 40, 88, 0, 319000, 0, 'quoted', CAST(strftime('%s','now','-3 days') AS INTEGER) * 1000),
  ('TEST-ENS-2604', 'Julie Morel', 'julie.test@example.com', '06 31 32 33 34', 'TEST — Bloom Institut', 'P12', 'BLOOM', 5, 45, 310, 9, 90, 'individual', 'serif', '#f2d9de', '#ff8fb3', 49, 43, 105, 1, 227500, 0, 'won', CAST(strftime('%s','now','-8 days') AS INTEGER) * 1000),
  ('TEST-ENS-2605', 'Marc Petit', 'marc.test@example.com', '06 41 42 43 44', 'TEST — Garage Petit', 'P01', 'GARAGE PETIT', 11, 50, 510, 7, 120, 'rails', 'industrial', '#f2f2f2', '#e9f4ff', 50, 39, 100, 1, 554000, 0, 'lost', CAST(strftime('%s','now','-15 days') AS INTEGER) * 1000),
  ('TEST-ENS-2606', 'Karim Benali', 'revendeur.attente.test@example.com', '06 22 33 44 55', 'TEST — Com Factory Lyon', 'P09', 'LE COMPTOIR', 10, 36, 390, 6, 75, 'dibond', 'montserrat', '#101010', '#f5c06a', 54, 41, 96, 1, 375000, 0, 'new', CAST(strftime('%s','now','-5 hours') AS INTEGER) * 1000);

INSERT INTO textile_quote_requests
  (reference, name, email, phone, company, profile_code, width_cm, height_cm, quantity, finish, installation, estimated_price_cents, is_professional, status, created_at)
VALUES
  ('TEST-TEX-2601', 'Pauline Leroy', 'pauline.test@example.com', '06 51 52 53 54', 'TEST — Hôtel Central', 'CT60', 300, 220, 2, 'SEG premium', 'murale', 184000, 0, 'new', CAST(strftime('%s','now','-4 hours') AS INTEGER) * 1000),
  ('TEST-TEX-2602', 'Sophie Martin', 'revendeur.actif.test@example.com', '06 10 20 30 40', 'TEST — Enseignes Grand Est', 'CT100', 500, 250, 4, 'Rétroéclairé', 'suspendue', 628000, 1, 'quoted', CAST(strftime('%s','now','-4 days') AS INTEGER) * 1000),
  ('TEST-TEX-2603', 'Antoine Dubois', 'antoine.test@example.com', '06 61 62 63 64', 'TEST — Expo Design', 'CT40', 200, 200, 6, 'Acoustique', 'autoportante', 396000, 0, 'review', CAST(strftime('%s','now','-7 days') AS INTEGER) * 1000),
  ('TEST-TEX-2604', 'Camille Roy', 'camille.test@example.com', '06 71 72 73 74', 'TEST — Pharmacie Roy', 'CT60', 240, 160, 1, 'SEG premium', 'murale', 99500, 0, 'won', CAST(strftime('%s','now','-18 days') AS INTEGER) * 1000);

INSERT INTO neon_quote_requests
  (reference, name, email, phone, company, neon_text, font, width_cm, height_cm, tube_mm, color, support, installation, quantity, estimated_price_cents, is_professional, status, created_at)
VALUES
  ('TEST-NEO-2601', 'Emma Simon', 'emma.test@example.com', '06 81 82 83 84', 'TEST — Café Simone', 'Hello Belfort', 'sacramento', 150, 55, 8, '#ff5fa2', 'plexiglas-transparent', 'entretoises', 1, 78500, 0, 'new', CAST(strftime('%s','now','-90 minutes') AS INTEGER) * 1000),
  ('TEST-NEO-2602', 'Sophie Martin', 'revendeur.actif.test@example.com', '06 10 20 30 40', 'TEST — Enseignes Grand Est', 'Good vibes only', 'dancing-script', 220, 75, 10, '#7d5cff', 'plexiglas-decoupe', 'suspendu', 3, 246000, 1, 'review', CAST(strftime('%s','now','-2 days') AS INTEGER) * 1000),
  ('TEST-NEO-2603', 'Lucas Fontaine', 'lucas.test@example.com', '06 91 92 93 94', 'TEST — Barber District', 'Stay sharp', 'satisfy', 180, 60, 8, '#ff8b32', 'dibond-noir', 'murale', 1, 96000, 0, 'quoted', CAST(strftime('%s','now','-6 days') AS INTEGER) * 1000),
  ('TEST-NEO-2604', 'Manon Gauthier', 'manon.test@example.com', '07 01 02 03 04', 'TEST — Studio Manon', 'Create', 'allura', 120, 48, 6, '#4ee7ff', 'plexiglas-transparent', 'entretoises', 2, 112000, 0, 'lost', CAST(strftime('%s','now','-20 days') AS INTEGER) * 1000);

INSERT INTO commercial_quotes
  (request_type, request_id, production_price_cents, delivery_price_cents, installation_price_cents, discount_cents, notes, validity_days, public_token, client_status, client_name, client_note, responded_at, updated_at)
VALUES
  ('sign', (SELECT id FROM quote_requests WHERE reference = 'TEST-ENS-2604'), 227500, 8500, 49000, 0, 'TEST — Fabrication, alimentation et pose comprises.', 30, 'test-devis-enseigne-accepte', 'accepted', 'Julie Morel', 'Bon pour accord, lancement en fabrication.', CAST(strftime('%s','now','-6 days') AS INTEGER) * 1000, CAST(strftime('%s','now','-7 days') AS INTEGER) * 1000),
  ('sign', (SELECT id FROM quote_requests WHERE reference = 'TEST-ENS-2603'), 319000, 12000, 55000, 15000, 'TEST — Validation du RAL avant mise en production.', 30, 'test-devis-enseigne-attente', 'pending', NULL, NULL, NULL, CAST(strftime('%s','now','-2 days') AS INTEGER) * 1000),
  ('sign', (SELECT id FROM quote_requests WHERE reference = 'TEST-ENS-2602'), 498000, 18000, 74000, 45000, 'TEST — Tarif revendeur et livraison multi-sites.', 21, 'test-devis-enseigne-modification', 'changes_requested', 'Sophie Martin', 'Merci de chiffrer une variante avec pose sur rails.', CAST(strftime('%s','now','-12 hours') AS INTEGER) * 1000, CAST(strftime('%s','now','-1 day') AS INTEGER) * 1000),
  ('textile', (SELECT id FROM textile_quote_requests WHERE reference = 'TEST-TEX-2604'), 99500, 6500, 18000, 0, 'TEST — Cadre mural avec visuel imprimé.', 30, 'test-devis-textile-accepte', 'accepted', 'Camille Roy', 'Accord validé.', CAST(strftime('%s','now','-15 days') AS INTEGER) * 1000, CAST(strftime('%s','now','-16 days') AS INTEGER) * 1000),
  ('textile', (SELECT id FROM textile_quote_requests WHERE reference = 'TEST-TEX-2602'), 628000, 25000, 82000, 60000, 'TEST — Série revendeur, BAT individuel par point de vente.', 30, 'test-devis-textile-attente', 'pending', NULL, NULL, NULL, CAST(strftime('%s','now','-3 days') AS INTEGER) * 1000);