-- ============================================================
-- DONNÉES DE DÉMONSTRATION - ARKENDIA (company_id = 4)
-- Exercice courant : 2026 (fiscal_year_id = 2)
-- Date de référence : 26 février 2026
-- ============================================================

SET @CID = 4;
SET @FY = 2;

-- ============================================================
-- 1. JOURNAUX COMPTABLES SUPPLÉMENTAIRES
-- ============================================================
INSERT IGNORE INTO vendoros_journals (company_id, code, name, journal_type, default_account, is_active, sequence_prefix, last_sequence) VALUES
(@CID, 'AC', 'Journal des achats', 'purchase', '401', 1, 'AC', 0),
(@CID, 'CA', 'Journal de caisse', 'cash', '530', 1, 'CA', 0),
(@CID, 'OD', 'Opérations diverses', 'misc', NULL, 1, 'OD', 0);

-- ============================================================
-- 2. PLAN COMPTABLE (comptes supplémentaires)
-- ============================================================
INSERT IGNORE INTO vendoros_chart_of_accounts (company_id, account_number, account_name, account_class, account_type, supply_type, is_active, allow_entries) VALUES
-- Classe 1 - Capitaux
(@CID, '101', 'Capital social', 1, 'equity', 'none', 1, 1),
(@CID, '108', 'Compte de l''exploitant', 1, 'equity', 'none', 1, 1),
(@CID, '120', 'Résultat de l''exercice (bénéfice)', 1, 'equity', 'none', 1, 1),
(@CID, '164', 'Emprunts bancaires', 1, 'liability', 'none', 1, 1),
-- Classe 2 - Immobilisations
(@CID, '205', 'Concessions, brevets, logiciels', 2, 'asset', 'none', 1, 1),
(@CID, '2183', 'Matériel de bureau et informatique', 2, 'asset', 'none', 1, 1),
(@CID, '2184', 'Mobilier', 2, 'asset', 'none', 1, 1),
(@CID, '28183', 'Amort. mat. bureau et informatique', 2, 'asset', 'none', 1, 1),
-- Classe 4 - Tiers
(@CID, '401', 'Fournisseurs', 4, 'liability', 'none', 1, 1),
(@CID, '411', 'Clients', 4, 'asset', 'none', 1, 1),
(@CID, '421', 'Personnel - Rémunérations dues', 4, 'liability', 'none', 1, 1),
(@CID, '431', 'Sécurité sociale', 4, 'liability', 'none', 1, 1),
(@CID, '4456', 'TVA déductible', 4, 'asset', 'none', 1, 1),
(@CID, '4457', 'TVA collectée', 4, 'liability', 'none', 1, 1),
(@CID, '4458', 'TVA à régulariser', 4, 'liability', 'none', 1, 1),
-- Classe 5 - Finances
(@CID, '512', 'Banque', 5, 'asset', 'none', 1, 1),
(@CID, '530', 'Caisse', 5, 'asset', 'none', 1, 1),
-- Classe 6 - Charges
(@CID, '604', 'Achats sous-traitance', 6, 'expense', 'services', 1, 1),
(@CID, '606', 'Achats fournitures non stockées', 6, 'expense', 'goods', 1, 1),
(@CID, '6061', 'Fournitures non stockables (eau, énergie)', 6, 'expense', 'goods', 1, 1),
(@CID, '6063', 'Fournitures d''entretien', 6, 'expense', 'goods', 1, 1),
(@CID, '6064', 'Fournitures administratives', 6, 'expense', 'goods', 1, 1),
(@CID, '613', 'Locations', 6, 'expense', 'services', 1, 1),
(@CID, '6155', 'Entretien et réparations', 6, 'expense', 'services', 1, 1),
(@CID, '616', 'Primes d''assurance', 6, 'expense', 'services', 1, 1),
(@CID, '622', 'Honoraires', 6, 'expense', 'services', 1, 1),
(@CID, '623', 'Publicité, publications', 6, 'expense', 'services', 1, 1),
(@CID, '625', 'Déplacements, missions, réceptions', 6, 'expense', 'services', 1, 1),
(@CID, '626', 'Frais postaux et télécommunications', 6, 'expense', 'services', 1, 1),
(@CID, '627', 'Services bancaires', 6, 'expense', 'services', 1, 1),
(@CID, '6281', 'Cotisations', 6, 'expense', 'services', 1, 1),
(@CID, '641', 'Rémunérations du personnel', 6, 'expense', 'none', 1, 1),
(@CID, '645', 'Charges de sécurité sociale', 6, 'expense', 'none', 1, 1),
(@CID, '6811', 'Dotations aux amortissements', 6, 'expense', 'none', 1, 1),
-- Classe 7 - Produits
(@CID, '706', 'Prestations de services', 7, 'income', 'services', 1, 1),
(@CID, '707', 'Ventes de marchandises', 7, 'income', 'goods', 1, 1),
(@CID, '708', 'Produits des activités annexes', 7, 'income', 'services', 1, 1),
(@CID, '7085', 'Ports et frais accessoires facturés', 7, 'income', 'services', 1, 1),
(@CID, '764', 'Revenus des valeurs mobilières', 7, 'income', 'none', 1, 1),
(@CID, '791', 'Transferts de charges d''exploitation', 7, 'income', 'none', 1, 1);

-- ============================================================
-- 3. CONTACTS SUPPLÉMENTAIRES (clients et fournisseurs)
-- ============================================================
INSERT INTO vendoros_contacts (company_id, type, is_company, contact_name, first_name, last_name, company_name, email, phone, address, postal_code, city, siren_siret, vat_number, notes, tags) VALUES
(@CID, 'client', 1, 'TechVision SAS', NULL, NULL, 'TechVision SAS', 'contact@techvision.fr', '01 42 18 33 00', '15 rue de la Paix', '75002', 'Paris', '98765432100015', 'FR87987654321', 'Client premium - contrat annuel', 'premium,tech'),
(@CID, 'client', 1, 'GreenLeaf Bio', NULL, NULL, 'GreenLeaf Bio SARL', 'direction@greenleaf-bio.fr', '04 78 55 12 90', '8 allée des Tilleuls', '69003', 'Lyon', '55512345600012', 'FR32555123456', 'Bio-cosmétiques, paie trimestriellement', 'cosmétique,bio'),
(@CID, 'client', 0, 'Marie Dupont', 'Marie', 'Dupont', NULL, 'marie.dupont@gmail.com', '06 12 34 56 78', '22 avenue Victor Hugo', '13006', 'Marseille', NULL, NULL, 'Consultante indépendante', 'freelance'),
(@CID, 'client', 1, 'Studio Pixel Perfect', NULL, NULL, 'Studio Pixel Perfect', 'hello@pixelperfect.design', '01 55 91 23 45', '100 boulevard Haussmann', '75008', 'Paris', '81234567800011', 'FR45812345678', 'Agence design partenaire', 'design,partenaire'),
(@CID, 'client', 1, 'Mairie de Valence', NULL, NULL, 'Mairie de Valence', 'marches-publics@mairie-valence.fr', '04 75 79 20 00', '1 place de la Liberté', '26000', 'Valence', '21260313100011', NULL, 'Marché public maintenance site web', 'collectivité,marché-public'),
(@CID, 'fournisseur', 1, 'CloudStack Pro', NULL, NULL, 'CloudStack Pro SAS', 'billing@cloudstack.pro', '01 80 96 11 22', '41 rue de Rivoli', '75001', 'Paris', '91123456700019', 'FR28911234567', 'Hébergement cloud + CDN', 'infra,cloud'),
(@CID, 'fournisseur', 1, 'Café des Devs', NULL, NULL, 'Café des Devs SARL', 'compta@cafedesdevs.fr', '04 26 83 70 00', '12 rue Thomassin', '69002', 'Lyon', '84567890100013', NULL, 'Coworking + café', 'coworking'),
(@CID, 'fournisseur', 1, 'Ergonomik', NULL, NULL, 'Ergonomik Design', 'pro@ergonomik.fr', '01 44 52 33 00', '5 rue du Faubourg Saint-Antoine', '75011', 'Paris', '79012345600018', 'FR61790123456', 'Mobilier de bureau ergonomique', 'mobilier'),
(@CID, 'partenaire', 1, 'CyberShield France', NULL, NULL, 'CyberShield France SAS', 'partners@cybershield.fr', '01 73 60 22 11', '25 quai Panhard', '75013', 'Paris', '88234567100014', 'FR09882345671', 'Partenaire cybersécurité - audits', 'sécurité,partenaire');

-- ============================================================
-- 4. PARTENAIRES COMPTABLES SUPPLÉMENTAIRES
-- ============================================================
INSERT INTO vendoros_partners (company_id, partner_type, code, name, short_name, account_number, account_prefix, siret, vat_number, email, phone, address, postal_code, city, default_expense_account, default_income_account, default_vat_rate, payment_terms, is_active, notes) VALUES
(@CID, 'customer', 'TECHVI', 'TechVision SAS', 'TechVision', '411TECHVI', '411', '98765432100015', 'FR87987654321', 'contact@techvision.fr', '01 42 18 33 00', '15 rue de la Paix', '75002', 'Paris', NULL, '706', 20.00, 30, 1, 'Client premium - contrat annuel'),
(@CID, 'customer', 'GREENL', 'GreenLeaf Bio SARL', 'GreenLeaf', '411GREENL', '411', '55512345600012', 'FR32555123456', 'direction@greenleaf-bio.fr', '04 78 55 12 90', '8 allée des Tilleuls', '69003', 'Lyon', NULL, '706', 20.00, 45, 1, 'Paie trimestriellement'),
(@CID, 'customer', 'DUPONT', 'Marie Dupont', 'Dupont', '411DUPONT', '411', NULL, NULL, 'marie.dupont@gmail.com', '06 12 34 56 78', '22 av Victor Hugo', '13006', 'Marseille', NULL, '706', 20.00, 30, 1, 'Freelance'),
(@CID, 'customer', 'PIXEL', 'Studio Pixel Perfect', 'Pixel', '411PIXEL', '411', '81234567800011', 'FR45812345678', 'hello@pixelperfect.design', '01 55 91 23 45', '100 bd Haussmann', '75008', 'Paris', NULL, '706', 20.00, 30, 1, 'Agence design partenaire'),
(@CID, 'customer', 'VALENC', 'Mairie de Valence', 'Valence', '411VALENC', '411', '21260313100011', NULL, 'marches-publics@mairie-valence.fr', '04 75 79 20 00', '1 pl de la Liberté', '26000', 'Valence', NULL, '706', 20.00, 60, 1, 'Marché public'),
(@CID, 'supplier', 'CLOUDS', 'CloudStack Pro SAS', 'CloudStack', '401CLOUDS', '401', '91123456700019', 'FR28911234567', 'billing@cloudstack.pro', '01 80 96 11 22', '41 rue de Rivoli', '75001', 'Paris', '613', NULL, 20.00, 30, 1, 'Hébergement cloud'),
(@CID, 'supplier', 'CAFEDE', 'Café des Devs', 'CaféDevs', '401CAFEDE', '401', '84567890100013', NULL, 'compta@cafedesdevs.fr', '04 26 83 70 00', '12 rue Thomassin', '69002', 'Lyon', '613', NULL, 20.00, 15, 1, 'Coworking'),
(@CID, 'supplier', 'ERGONO', 'Ergonomik Design', 'Ergonomik', '401ERGONO', '401', '79012345600018', 'FR61790123456', 'pro@ergonomik.fr', '01 44 52 33 00', '5 rue du Fbg St-Antoine', '75011', 'Paris', '2184', NULL, 20.00, 30, 1, 'Mobilier bureau');

-- ============================================================
-- 8. SERVICES (pour la marketplace)
-- ============================================================
INSERT INTO vendoros_services (company_id, service_name, description, pricing_type, base_price, available, min_notice_hours, duration_minutes, equipment_provided, featured) VALUES
(@CID, 'Développement Web Sur Mesure', 'Conception et développement d''applications web modernes (React, PHP, Node.js). Architecture scalable, code maintenable, tests automatisés.', 'quote', NULL, 1, 72, NULL, 0, 1),
(@CID, 'Audit de Sécurité Applicatif', 'Audit complet de sécurité : tests d''intrusion, analyse de code, rapport OWASP, plan de remédiation détaillé.', 'fixed', 2500.00, 1, 168, NULL, 0, 1),
(@CID, 'Maintenance & Support Technique', 'Contrat de maintenance mensuel : monitoring, mises à jour de sécurité, support technique prioritaire, temps de réponse garanti.', 'package', 890.00, 1, 24, NULL, 0, 1),
(@CID, 'Migration Cloud & DevOps', 'Migration de votre infrastructure vers le cloud (AWS, OVH, Scaleway). CI/CD, Docker, monitoring.', 'quote', NULL, 1, 168, NULL, 0, 0),
(@CID, 'Formation Développement', 'Sessions de formation sur mesure : PHP/Laravel, React, API REST, bonnes pratiques. De 1 à 5 jours.', 'hourly', 150.00, 1, 336, 480, 1, 0),
(@CID, 'Design UX/UI', 'Maquettage Figma complet, design system, prototypage interactif, tests utilisateurs.', 'quote', NULL, 1, 48, NULL, 0, 1),
(@CID, 'Intégration API & Connecteurs', 'Développement de connecteurs et intégrations API tierces (Bridge, Stripe, SendGrid, etc.).', 'hourly', 120.00, 1, 48, NULL, 0, 0),
(@CID, 'Consulting CTO as a Service', 'Accompagnement stratégique technique : choix technologiques, architecture, recrutement tech, roadmap produit.', 'hourly', 200.00, 1, 48, NULL, 0, 1);

-- ============================================================
-- 9. DEVIS
-- ============================================================
INSERT INTO vendoros_quotes (company_id, client_id, number, title, status, issue_date, valid_until, notes, header_note, footer_note) VALUES
-- Devis TechVision - Accepté
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='TechVision SAS'), 'DEV-2026-001', 'Refonte plateforme e-commerce TechVision', 'accepted', '2026-01-10', '2026-02-10', 'Projet démarré en janvier', 'Nous vous remercions de votre confiance.', 'Conditions : 30% à la commande, 40% à mi-projet, 30% à la livraison.'),
-- Devis GreenLeaf - Envoyé
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='GreenLeaf Bio'), 'DEV-2026-002', 'Application mobile Click & Collect GreenLeaf', 'sent', '2026-02-05', '2026-03-07', NULL, 'Suite à notre échange du 3 février.', 'Validité 30 jours. TVA 20%.'),
-- Devis Mairie Valence - Envoyé (marché public)
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='Mairie de Valence'), 'DEV-2026-003', 'Maintenance annuelle site web + intranet Mairie de Valence', 'sent', '2026-02-15', '2026-03-15', 'Réponse au marché public réf. MP-2026-IT-003', 'Offre en réponse au cahier des charges du 20/01/2026.', 'Engagement de résultat. SLA 99.5%.'),
-- Devis Pixel Perfect - Brouillon
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='Studio Pixel Perfect'), 'DEV-2026-004', 'Développement plugin WordPress sur mesure', 'draft', '2026-02-24', '2026-03-24', NULL, NULL, NULL),
-- Devis Marie Dupont - Accepté
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='Marie Dupont'), 'DEV-2026-005', 'Audit sécurité + optimisation performances site vitrine', 'accepted', '2025-12-15', '2026-01-15', 'Projet livré le 20/01', NULL, 'Paiement sous 30 jours.'),
-- Devis Indian Forest - Refusé
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='INDIAN-FOREST-ARDECHE'), 'DEV-2026-006', 'Système de réservation en ligne - Indian Forest', 'refused', '2025-11-20', '2025-12-20', 'Budget client insuffisant', NULL, NULL);

-- Lignes des devis
-- DEV-2026-001 : Refonte e-commerce TechVision
INSERT INTO vendoros_quote_items (quote_id, position, label, description, quantity, unit_type, unit_price, vat_rate, line_total, is_section, is_optional) VALUES
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-001'), 1, 'Phase d''analyse', NULL, 1, 'section', 0, 0, 0, 1, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-001'), 2, 'Audit technique plateforme existante', 'Analyse de l''architecture, performances, sécurité', 3, 'jour', 900.00, 20.00, 2700.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-001'), 3, 'Rédaction cahier des charges fonctionnel', NULL, 2, 'jour', 900.00, 20.00, 1800.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-001'), 4, 'Phase de développement', NULL, 1, 'section', 0, 0, 0, 1, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-001'), 5, 'Développement front-end React', 'Interface responsive, panier, paiement', 15, 'jour', 850.00, 20.00, 12750.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-001'), 6, 'Développement back-end API', 'API REST, gestion commandes, stocks, paiements Stripe', 12, 'jour', 900.00, 20.00, 10800.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-001'), 7, 'Intégration passerelle de paiement Stripe', NULL, 2, 'jour', 900.00, 20.00, 1800.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-001'), 8, 'Tests & Déploiement', NULL, 1, 'section', 0, 0, 0, 1, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-001'), 9, 'Tests unitaires et d''intégration', NULL, 4, 'jour', 800.00, 20.00, 3200.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-001'), 10, 'Mise en production et recette', NULL, 2, 'jour', 850.00, 20.00, 1700.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-001'), 11, 'Formation équipe client (optionnel)', 'Formation à l''administration du back-office', 1, 'jour', 1200.00, 20.00, 1200.00, 0, 1);

-- DEV-2026-002 : App mobile GreenLeaf
INSERT INTO vendoros_quote_items (quote_id, position, label, description, quantity, unit_type, unit_price, vat_rate, line_total, is_section, is_optional) VALUES
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-002'), 1, 'Design UX/UI application mobile', 'Wireframes, maquettes Figma, prototype interactif', 5, 'jour', 800.00, 20.00, 4000.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-002'), 2, 'Développement React Native', 'Application iOS + Android, click & collect, notifications push', 18, 'jour', 850.00, 20.00, 15300.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-002'), 3, 'API back-end et tableau de bord', 'Gestion produits, commandes, stats, panel admin', 8, 'jour', 900.00, 20.00, 7200.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-002'), 4, 'Publication stores Apple & Google', 'Soumission, validation, configuration stores', 1, 'forfait', 500.00, 20.00, 500.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-002'), 5, 'Maintenance 3 mois post-livraison (optionnel)', 'Support technique, corrections bugs, mises à jour mineures', 3, 'mois', 450.00, 20.00, 1350.00, 0, 1);

-- DEV-2026-003 : Maintenance Mairie Valence
INSERT INTO vendoros_quote_items (quote_id, position, label, description, quantity, unit_type, unit_price, vat_rate, line_total, is_section, is_optional) VALUES
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-003'), 1, 'Maintenance corrective et évolutive', 'Corrections bugs, mises à jour sécurité, évolutions mineures', 12, 'mois', 650.00, 20.00, 7800.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-003'), 2, 'Hébergement et infogérance', 'Serveur dédié, sauvegardes quotidiennes, monitoring 24/7', 12, 'mois', 280.00, 20.00, 3360.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-003'), 3, 'Astreinte heures ouvrées', 'Intervention sous 4h en cas d''incident critique', 12, 'mois', 150.00, 20.00, 1800.00, 0, 0);

-- DEV-2026-005 : Audit Marie Dupont
INSERT INTO vendoros_quote_items (quote_id, position, label, description, quantity, unit_type, unit_price, vat_rate, line_total, is_section, is_optional) VALUES
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-005'), 1, 'Audit de sécurité complet', 'Tests d''intrusion, analyse OWASP Top 10, rapport détaillé', 1, 'forfait', 2500.00, 20.00, 2500.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-005'), 2, 'Optimisation performances', 'Audit Lighthouse, optimisation assets, cache, CDN', 2, 'jour', 850.00, 20.00, 1700.00, 0, 0),
((SELECT id FROM vendoros_quotes WHERE company_id=@CID AND number='DEV-2026-005'), 3, 'Rapport et plan d''action', 'Document de synthèse et recommandations priorisées', 0.5, 'jour', 900.00, 20.00, 450.00, 0, 0);

-- ============================================================
-- 10. FACTURES
-- ============================================================
INSERT INTO vendoros_invoices (company_id, client_id, number, title, status, invoice_type, issue_date, due_date, notes, header_note, footer_note) VALUES
-- Facture TechVision - Acompte 30% (payée)
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='TechVision SAS'), 'FA-2026-001', 'Acompte 30% - Refonte e-commerce TechVision', 'paid', 'deposit', '2026-01-15', '2026-02-14', 'Acompte sur devis DEV-2026-001', 'Facture d''acompte - 30% du montant total.', 'Paiement par virement bancaire sous 30 jours.'),
-- Facture Marie Dupont - Audit (payée)
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='Marie Dupont'), 'FA-2026-002', 'Audit sécurité et optimisation - Marie Dupont', 'paid', 'standard', '2026-01-22', '2026-02-21', 'Prestation réalisée du 06 au 20 janvier', NULL, 'Merci pour votre confiance.'),
-- Facture Kastor - Maintenance (payée)
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='Romain Lebled'), 'FA-2026-003', 'Maintenance site web Kastor - Janvier 2026', 'paid', 'standard', '2026-01-31', '2026-02-28', NULL, NULL, NULL),
-- Facture TechVision - 2ème acompte 40% (émise, en attente)
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='TechVision SAS'), 'FA-2026-004', 'Acompte 40% - Refonte e-commerce TechVision', 'issued', 'deposit', '2026-02-15', '2026-03-17', 'Phase de développement en cours', 'Deuxième acompte - 40% du montant total HT.', 'Paiement par virement bancaire sous 30 jours.'),
-- Facture Kastor - Maintenance février (émise)
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='Romain Lebled'), 'FA-2026-005', 'Maintenance site web Kastor - Février 2026', 'issued', 'standard', '2026-02-25', '2026-03-27', NULL, NULL, NULL),
-- Facture Indian Forest - site web (en retard)
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='INDIAN-FOREST-ARDECHE'), 'FA-2025-012', 'Développement site vitrine Indian Forest', 'overdue', 'standard', '2025-11-15', '2025-12-15', 'Relance envoyée le 05/01/2026', NULL, 'Pénalités de retard : 3x taux légal.'),
-- Facture INOVAX - brouillon
(@CID, (SELECT id FROM vendoros_contacts WHERE company_id=@CID AND contact_name='INOVAX'), 'FA-2026-006', 'Consulting architecture technique - Inovax', 'draft', 'standard', '2026-02-26', '2026-03-28', NULL, NULL, NULL);

-- Lignes de factures
-- FA-2026-001 : Acompte TechVision (30%)
INSERT INTO vendoros_invoice_items (invoice_id, position, label, description, quantity, unit_type, unit_price, vat_rate, line_total) VALUES
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-001'), 1, 'Acompte 30% - Refonte plateforme e-commerce', 'Selon devis DEV-2026-001 accepté le 12/01/2026', 1, 'forfait', 10425.00, 20.00, 10425.00);

-- FA-2026-002 : Audit Dupont
INSERT INTO vendoros_invoice_items (invoice_id, position, label, description, quantity, unit_type, unit_price, vat_rate, line_total) VALUES
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-002'), 1, 'Audit de sécurité complet', 'Tests d''intrusion, analyse OWASP Top 10, rapport détaillé', 1, 'forfait', 2500.00, 20.00, 2500.00),
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-002'), 2, 'Optimisation performances', 'Audit Lighthouse, optimisation assets, cache', 2, 'jour', 850.00, 20.00, 1700.00),
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-002'), 3, 'Rapport et plan d''action', 'Synthèse et recommandations priorisées', 0.5, 'jour', 900.00, 20.00, 450.00);

-- FA-2026-003 : Kastor janvier
INSERT INTO vendoros_invoice_items (invoice_id, position, label, description, quantity, unit_type, unit_price, vat_rate, line_total) VALUES
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-003'), 1, 'Maintenance corrective site web', 'Corrections bugs signalés, mises à jour de sécurité', 1, 'mois', 450.00, 20.00, 450.00),
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-003'), 2, 'Évolution : ajout module candidatures', 'Page carrières + formulaire candidatures spontanées', 3, 'heure', 120.00, 20.00, 360.00);

-- FA-2026-004 : Acompte 40% TechVision
INSERT INTO vendoros_invoice_items (invoice_id, position, label, description, quantity, unit_type, unit_price, vat_rate, line_total) VALUES
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-004'), 1, 'Acompte 40% - Refonte plateforme e-commerce', 'Selon devis DEV-2026-001 - Phase développement', 1, 'forfait', 13900.00, 20.00, 13900.00);

-- FA-2026-005 : Kastor février
INSERT INTO vendoros_invoice_items (invoice_id, position, label, description, quantity, unit_type, unit_price, vat_rate, line_total) VALUES
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-005'), 1, 'Maintenance corrective site web', 'Corrections bugs, mises à jour de sécurité', 1, 'mois', 450.00, 20.00, 450.00),
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-005'), 2, 'Support technique prioritaire', 'Tickets support traités en moins de 4h', 1, 'mois', 200.00, 20.00, 200.00);

-- FA-2025-012 : Indian Forest (en retard)
INSERT INTO vendoros_invoice_items (invoice_id, position, label, description, quantity, unit_type, unit_price, vat_rate, line_total) VALUES
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2025-012'), 1, 'Développement site vitrine', 'Site responsive PHP/JS, formulaire contact, galerie', 8, 'jour', 800.00, 20.00, 6400.00),
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2025-012'), 2, 'Hébergement 1ère année', 'OVH mutualisé + nom de domaine', 1, 'an', 120.00, 20.00, 120.00);

-- FA-2026-006 : Inovax (brouillon)
INSERT INTO vendoros_invoice_items (invoice_id, position, label, description, quantity, unit_type, unit_price, vat_rate, line_total) VALUES
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-006'), 1, 'Consulting architecture technique', 'Audit infrastructure, recommandations, roadmap', 5, 'jour', 1200.00, 20.00, 6000.00),
((SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-006'), 2, 'Atelier de cadrage équipe dev', 'Animation atelier, bonnes pratiques, CI/CD', 1, 'jour', 1500.00, 20.00, 1500.00);

-- ============================================================
-- 11. PAIEMENTS
-- ============================================================
INSERT INTO vendoros_payments (company_id, invoice_id, amount, payment_date, payment_method, reference, notes) VALUES
-- Paiement FA-2026-001 (TechVision acompte 30%)
(@CID, (SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-001'), 12510.00, '2026-02-03', 'virement', 'VIR-TECHVISION-030226', 'Acompte 30% TTC reçu'),
-- Paiement FA-2026-002 (audit Dupont)
(@CID, (SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-002'), 5580.00, '2026-02-18', 'virement', 'VIR-DUPONT-180226', 'Paiement total audit'),
-- Paiement FA-2026-003 (Kastor janvier)
(@CID, (SELECT id FROM vendoros_invoices WHERE company_id=@CID AND number='FA-2026-003'), 972.00, '2026-02-25', 'virement', 'VIR-KASTOR-250226', 'Maintenance janvier');

-- ============================================================
-- 12. ÉCRITURES COMPTABLES (ventes et achats manuels)
-- ============================================================

-- On récupère les IDs des journaux
SET @JV = (SELECT id FROM vendoros_journals WHERE company_id=@CID AND code='VE');
SET @JA = (SELECT id FROM vendoros_journals WHERE company_id=@CID AND code='AC');
SET @JB = (SELECT id FROM vendoros_journals WHERE company_id=@CID AND code='BQ');

-- Écriture 1 : Facture vente TechVision acompte (FA-2026-001)
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, @JV, 'VE-2026-001', '2026-01-15', 'Facture FA-2026-001 - Acompte TechVision', 'FA-2026-001', 'invoice', 12510.00, 12510.00, 'validated');
SET @E1 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E1, 1, '411', 'Clients', 'TechVision SAS - Acompte 30%', 12510.00, 0),
(@CID, @E1, 2, '706', 'Prestations de services', 'Prestation dev e-commerce', 0, 10425.00),
(@CID, @E1, 3, '4457', 'TVA collectée', 'TVA 20%', 0, 2085.00);

-- Écriture 2 : Facture vente Dupont audit (FA-2026-002)
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, @JV, 'VE-2026-002', '2026-01-22', 'Facture FA-2026-002 - Audit Dupont', 'FA-2026-002', 'invoice', 5580.00, 5580.00, 'validated');
SET @E2 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E2, 1, '411', 'Clients', 'Marie Dupont - Audit sécurité', 5580.00, 0),
(@CID, @E2, 2, '706', 'Prestations de services', 'Audit sécurité + optimisation', 0, 4650.00),
(@CID, @E2, 3, '4457', 'TVA collectée', 'TVA 20%', 0, 930.00);

-- Écriture 3 : Facture vente Kastor janvier (FA-2026-003)
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, @JV, 'VE-2026-003', '2026-01-31', 'Facture FA-2026-003 - Kastor maintenance jan.', 'FA-2026-003', 'invoice', 972.00, 972.00, 'validated');
SET @E3 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E3, 1, '411', 'Clients', 'KASTOR - Maintenance janvier', 972.00, 0),
(@CID, @E3, 2, '706', 'Prestations de services', 'Maintenance + module candidatures', 0, 810.00),
(@CID, @E3, 3, '4457', 'TVA collectée', 'TVA 20%', 0, 162.00);

-- Écriture 4 : Achat hébergement OVH (charge mensuelle)
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, @JA, 'AC-2026-001', '2026-01-05', 'Hébergement OVH Cloud - Janvier', 'OVH-JAN26', 'bill', 71.88, 71.88, 'validated');
SET @E4 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E4, 1, '613', 'Locations', 'Hébergement cloud OVH', 59.90, 0),
(@CID, @E4, 2, '4456', 'TVA déductible', 'TVA 20%', 11.98, 0),
(@CID, @E4, 3, '401', 'Fournisseurs', 'OVH - Facture janvier', 0, 71.88);

-- Écriture 5 : Abonnement GitHub
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, @JA, 'AC-2026-002', '2026-01-01', 'GitHub Team - Janvier 2026', 'GH-JAN26', 'bill', 22.80, 22.80, 'validated');
SET @E5 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E5, 1, '6281', 'Cotisations', 'Abonnement GitHub Team', 19.00, 0),
(@CID, @E5, 2, '4456', 'TVA déductible', 'TVA 20%', 3.80, 0),
(@CID, @E5, 3, '401', 'Fournisseurs', 'GITHUB', 0, 22.80);

-- Écriture 6 : Abonnement OpenAI
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, @JA, 'AC-2026-003', '2026-01-15', 'OpenAI API - Janvier 2026', 'OPENAI-JAN26', 'bill', 120.00, 120.00, 'validated');
SET @E6 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E6, 1, '604', 'Achats sous-traitance', 'API OpenAI usage', 100.00, 0),
(@CID, @E6, 2, '4456', 'TVA déductible', 'TVA 20%', 20.00, 0),
(@CID, @E6, 3, '401', 'Fournisseurs', 'OpenAI', 0, 120.00);

-- Écriture 7 : Salaire Antoine (janvier)
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, (SELECT id FROM vendoros_journals WHERE company_id=@CID AND code='OD'), 'OD-2026-001', '2026-01-31', 'Salaire brut - Antoine Descotes - Janvier', 'SAL-JAN26-001', 'payroll', 6250.00, 6250.00, 'validated');
SET @E7 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E7, 1, '641', 'Rémunérations du personnel', 'Salaire brut Antoine Descotes', 4200.00, 0),
(@CID, @E7, 2, '645', 'Charges de sécurité sociale', 'Charges patronales', 2050.00, 0),
(@CID, @E7, 3, '421', 'Personnel - Rémunérations dues', 'Net à payer', 0, 3150.00),
(@CID, @E7, 4, '431', 'Sécurité sociale', 'Charges sociales', 0, 3100.00);

-- Écriture 8 : Encaissement TechVision (paiement FA-2026-001)
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, @JB, 'BQ-2026-001', '2026-02-03', 'Encaissement TechVision - Acompte', 'VIR-TECHVISION-030226', 'payment', 12510.00, 12510.00, 'validated');
SET @E8 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E8, 1, '512', 'Banque', 'Encaissement virement TechVision', 12510.00, 0),
(@CID, @E8, 2, '411', 'Clients', 'TechVision SAS', 0, 12510.00);

-- Écriture 9 : Facture TechVision acompte 40% (FA-2026-004)
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, @JV, 'VE-2026-004', '2026-02-15', 'Facture FA-2026-004 - Acompte 40% TechVision', 'FA-2026-004', 'invoice', 16680.00, 16680.00, 'validated');
SET @E9 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E9, 1, '411', 'Clients', 'TechVision SAS - Acompte 40%', 16680.00, 0),
(@CID, @E9, 2, '706', 'Prestations de services', 'Développement e-commerce', 0, 13900.00),
(@CID, @E9, 3, '4457', 'TVA collectée', 'TVA 20%', 0, 2780.00);

-- Écriture 10 : Encaissement Dupont
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, @JB, 'BQ-2026-002', '2026-02-18', 'Encaissement Marie Dupont - Audit', 'VIR-DUPONT-180226', 'payment', 5580.00, 5580.00, 'validated');
SET @E10 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E10, 1, '512', 'Banque', 'Encaissement virement Dupont', 5580.00, 0),
(@CID, @E10, 2, '411', 'Clients', 'Marie Dupont', 0, 5580.00);

-- Écriture 11 : Loyer coworking février
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, @JA, 'AC-2026-004', '2026-02-01', 'Coworking Café des Devs - Février', 'COWORK-FEV26', 'bill', 540.00, 540.00, 'validated');
SET @E11 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E11, 1, '613', 'Locations', 'Location poste coworking x3', 450.00, 0),
(@CID, @E11, 2, '4456', 'TVA déductible', 'TVA 20%', 90.00, 0),
(@CID, @E11, 3, '401', 'Fournisseurs', 'Café des Devs', 0, 540.00);

-- Écriture 12 : Assurance RC Pro
INSERT INTO vendoros_accounting_entries (company_id, fiscal_year_id, journal_id, entry_number, entry_date, description, reference, source_type, total_debit, total_credit, status) VALUES
(@CID, @FY, @JA, 'AC-2026-005', '2026-01-10', 'Assurance RC Pro - Trimestrielle Q1 2026', 'ASSUR-Q1-26', 'bill', 345.00, 345.00, 'validated');
SET @E12 = LAST_INSERT_ID();
INSERT INTO vendoros_accounting_lines (company_id, entry_id, line_number, account_number, account_name, label, debit, credit) VALUES
(@CID, @E12, 1, '616', 'Primes d''assurance', 'RC Pro + Cyber - Q1 2026', 345.00, 0),
(@CID, @E12, 2, '401', 'Fournisseurs', 'AXA Assurances', 0, 345.00);

-- ============================================================
-- 13. RÈGLES D'AFFECTATION BANCAIRE
-- ============================================================
INSERT INTO vendoros_bank_allocation_rules (company_id, name, match_description, match_direction, apply_partner_id, apply_counterpart_account, apply_vat_rate, priority, is_active) VALUES
(@CID, 'OVH - Hébergement', 'OVH', 'debit', (SELECT id FROM vendoros_partners WHERE company_id=@CID AND code='OVH'), '613', 20.00, 10, 1),
(@CID, 'GitHub - Abonnement', 'GITHUB', 'debit', (SELECT id FROM vendoros_partners WHERE company_id=@CID AND code='GITHUB'), '6281', 20.00, 10, 1),
(@CID, 'OpenAI - API', 'OPENAI', 'debit', (SELECT id FROM vendoros_partners WHERE company_id=@CID AND code='OPENAI'), '604', 20.00, 10, 1),
(@CID, 'Amazon - Achats', 'AMAZON', 'debit', (SELECT id FROM vendoros_partners WHERE company_id=@CID AND code='AMAZON'), '6064', 20.00, 20, 1),
(@CID, 'SeDomicilier', 'SEDOMICILIER', 'debit', (SELECT id FROM vendoros_partners WHERE company_id=@CID AND code='SEDOMI'), '613', 20.00, 10, 1),
(@CID, 'RATP - Transport', 'RATP', 'debit', (SELECT id FROM vendoros_partners WHERE company_id=@CID AND code='RATP'), '625', 10.00, 20, 1),
(@CID, 'Qonto - Frais bancaires', 'QONTO', 'debit', (SELECT id FROM vendoros_partners WHERE company_id=@CID AND code='QONTO'), '627', 20.00, 5, 1);

-- ============================================================
-- 14. TYPES D'OPÉRATIONS BANCAIRES
-- ============================================================
INSERT IGNORE INTO vendoros_bank_operation_types (company_id, code, name, direction, default_account_debit, default_account_credit, requires_vat, is_active) VALUES
(@CID, 'PREST', 'Prestation de services', 'credit', NULL, '706', 1, 1),
(@CID, 'VENTE', 'Vente de marchandises', 'credit', NULL, '707', 1, 1),
(@CID, 'ACHAT_SOUS', 'Sous-traitance', 'debit', '604', NULL, 1, 1),
(@CID, 'FOURN', 'Fournitures', 'debit', '606', NULL, 1, 1),
(@CID, 'LOYER', 'Loyer / Location', 'debit', '613', NULL, 1, 1),
(@CID, 'ASSUR', 'Assurance', 'debit', '616', NULL, 0, 1),
(@CID, 'HONO', 'Honoraires', 'debit', '622', NULL, 1, 1),
(@CID, 'PUB', 'Publicité', 'debit', '623', NULL, 1, 1),
(@CID, 'DEPL', 'Déplacements', 'debit', '625', NULL, 1, 1),
(@CID, 'TELECOM', 'Télécommunications', 'debit', '626', NULL, 1, 1),
(@CID, 'BANQUE', 'Frais bancaires', 'debit', '627', NULL, 0, 1),
(@CID, 'COTIS', 'Cotisations / Abonnements', 'debit', '6281', NULL, 1, 1),
(@CID, 'SAL', 'Salaires', 'debit', '641', NULL, 0, 1),
(@CID, 'CHRG', 'Charges sociales', 'debit', '645', NULL, 0, 1);

-- ============================================================
-- FIN DES DONNÉES DE DÉMONSTRATION
-- ============================================================
SELECT 'Demo data inserted successfully!' AS result;
