-- ===================================================================== -- Lustra — base de données complète (schéma + migrations + démo) -- Généré le 2026-06-11T17:33:02.926Z -- -- Import phpMyAdmin : sélectionnez votre base puis importez ce fichier. -- En ligne de commande : mysql -u USER -p DB_NAME < database-complete.sql -- -- En cas d'erreur sous phpMyAdmin, utilisez plutôt l'importeur Node -- (tolérant et multi-passes) : node scripts/db-setup.js --seed -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; SET SESSION sql_mode = ''; -- --------------------------------------------------------------------- -- Source : database.sql -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, role ENUM('client','admin','employee') NOT NULL DEFAULT 'client', email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, first_name VARCHAR(120) NOT NULL, last_name VARCHAR(120) NOT NULL, phone VARCHAR(60), status ENUM('active','blocked') NOT NULL DEFAULT 'active', internal_hourly_rate DECIMAL(10,2) NULL, must_change_password TINYINT(1) NOT NULL DEFAULT 0, consent_privacy TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_users_role (role), INDEX idx_users_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS clients_profiles ( user_id INT UNSIGNED PRIMARY KEY, client_type ENUM('particulier','entreprise') NOT NULL DEFAULT 'particulier', company_name VARCHAR(190), address VARCHAR(255), postal_code VARCHAR(20), city VARCHAR(120), internal_note TEXT, deletion_requested_at DATETIME NULL, anonymized_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS settings ( name VARCHAR(120) PRIMARY KEY, value TEXT, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS bank_accounts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, label VARCHAR(120) NOT NULL, holder_name VARCHAR(190) NOT NULL, holder_street VARCHAR(190) NOT NULL, holder_building_no VARCHAR(20), holder_postal_code VARCHAR(20) NOT NULL, holder_city VARCHAR(120) NOT NULL, holder_country CHAR(2) NOT NULL DEFAULT 'CH', iban VARCHAR(34) NOT NULL, bank_name VARCHAR(190), is_default TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_bank_accounts_active (is_active), INDEX idx_bank_accounts_default (is_default) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quote_requests ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NULL, contact_first_name VARCHAR(120), contact_last_name VARCHAR(120), contact_email VARCHAR(190), contact_phone VARCHAR(60), wants_account TINYINT(1) NOT NULL DEFAULT 0, service_type VARCHAR(80) NOT NULL, status ENUM('nouvelle','en_analyse','devis_envoye','devis_accepte','devis_refuse','planifiee','terminee','annulee') NOT NULL DEFAULT 'nouvelle', address VARCHAR(255), desired_date DATE NULL, data_json JSON NOT NULL, comment TEXT, internal_note TEXT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_quote_requests_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_quote_requests_contact_email (contact_email), INDEX idx_quote_requests_status (status), INDEX idx_quote_requests_service (service_type), INDEX idx_quote_requests_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quote_request_files ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quote_request_id INT UNSIGNED NOT NULL, original_name VARCHAR(255) NOT NULL, stored_name VARCHAR(255) NOT NULL, mime_type VARCHAR(120) NOT NULL, size_bytes INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_quote_files_request FOREIGN KEY (quote_request_id) REFERENCES quote_requests(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quotes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, quote_request_id INT UNSIGNED NULL, quote_number VARCHAR(40) NOT NULL UNIQUE, service_type VARCHAR(120) NOT NULL, intervention_address VARCHAR(255), proposed_date DATE NULL, description TEXT, subtotal DECIMAL(10,2) NOT NULL DEFAULT 0, vat_enabled TINYINT(1) NOT NULL DEFAULT 0, vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0, vat_label VARCHAR(190) NOT NULL DEFAULT 'TVA non applicable', vat_amount DECIMAL(10,2) NOT NULL DEFAULT 0, total DECIMAL(10,2) NOT NULL DEFAULT 0, deposit_amount DECIMAL(10,2) NOT NULL DEFAULT 0, balance_amount DECIMAL(10,2) NOT NULL DEFAULT 0, conditions TEXT, notes TEXT, status ENUM('brouillon','envoye','accepte','refuse','expire') NOT NULL DEFAULT 'brouillon', valid_until DATE NULL, pdf_path VARCHAR(255), sent_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_quotes_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_quotes_request FOREIGN KEY (quote_request_id) REFERENCES quote_requests(id) ON DELETE SET NULL, INDEX idx_quotes_status (status), INDEX idx_quotes_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quote_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quote_id INT UNSIGNED NOT NULL, description VARCHAR(255) NOT NULL, quantity DECIMAL(10,2) NOT NULL DEFAULT 1, unit VARCHAR(40) NOT NULL DEFAULT 'forfait', unit_price DECIMAL(10,2) NOT NULL DEFAULT 0, total DECIMAL(10,2) NOT NULL DEFAULT 0, position INT UNSIGNED NOT NULL DEFAULT 1, CONSTRAINT fk_quote_items_quote FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quote_acceptances ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, quote_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, accepted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ip_address VARCHAR(60), user_agent VARCHAR(255), CONSTRAINT fk_quote_accept_quote FOREIGN KEY (quote_id) REFERENCES quotes(id), CONSTRAINT fk_quote_accept_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS interventions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, assigned_user_id INT UNSIGNED NULL, quote_id INT UNSIGNED NULL, invoice_id INT UNSIGNED NULL, service_type VARCHAR(120) NOT NULL, address VARCHAR(255), starts_at DATETIME NULL, ends_at DATETIME NULL, hourly_rate DECIMAL(10,2) NOT NULL DEFAULT 0, actual_duration_minutes INT UNSIGNED NOT NULL DEFAULT 0, completed_at DATETIME NULL, expected_price DECIMAL(10,2) NOT NULL DEFAULT 0, status ENUM('planifiee','confirmee','en_cours','terminee','annulee','reportee') NOT NULL DEFAULT 'planifiee', admin_comment TEXT, client_comment TEXT, work_note TEXT, recurring_group VARCHAR(40), created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_interventions_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_interventions_assigned_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_interventions_quote FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE SET NULL, INDEX idx_interventions_status (status), INDEX idx_interventions_assigned (assigned_user_id), INDEX idx_interventions_starts (starts_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS intervention_staff ( intervention_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (intervention_id, user_id), CONSTRAINT fk_intervention_staff_intervention FOREIGN KEY (intervention_id) REFERENCES interventions(id) ON DELETE CASCADE, CONSTRAINT fk_intervention_staff_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_intervention_staff_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS invoices ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, quote_id INT UNSIGNED NULL, intervention_id INT UNSIGNED NULL, bank_account_id INT UNSIGNED NULL, invoice_number VARCHAR(40) NOT NULL UNIQUE, service_type VARCHAR(120) NOT NULL, description TEXT, subtotal DECIMAL(10,2) NOT NULL DEFAULT 0, vat_enabled TINYINT(1) NOT NULL DEFAULT 0, vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0, vat_label VARCHAR(190) NOT NULL DEFAULT 'TVA non applicable', vat_amount DECIMAL(10,2) NOT NULL DEFAULT 0, total DECIMAL(10,2) NOT NULL DEFAULT 0, paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0, due_date DATE NULL, conditions TEXT, status ENUM('brouillon','envoyee','partiellement_payee','payee','en_retard','annulee') NOT NULL DEFAULT 'brouillon', pdf_path VARCHAR(255), sent_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_invoices_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_invoices_quote FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE SET NULL, CONSTRAINT fk_invoices_intervention FOREIGN KEY (intervention_id) REFERENCES interventions(id) ON DELETE SET NULL, CONSTRAINT fk_invoices_bank_account FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE SET NULL, INDEX idx_invoices_status (status), INDEX idx_invoices_due (due_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS invoice_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id INT UNSIGNED NOT NULL, description VARCHAR(255) NOT NULL, quantity DECIMAL(10,2) NOT NULL DEFAULT 1, unit VARCHAR(40) NOT NULL DEFAULT 'forfait', unit_price DECIMAL(10,2) NOT NULL DEFAULT 0, total DECIMAL(10,2) NOT NULL DEFAULT 0, position INT UNSIGNED NOT NULL DEFAULT 1, CONSTRAINT fk_invoice_items_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payments ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id INT UNSIGNED NOT NULL, amount DECIMAL(10,2) NOT NULL, paid_at DATE NOT NULL, method VARCHAR(80), note TEXT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS messages_threads ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, subject VARCHAR(190) NOT NULL, status ENUM('ouvert','repondu','ferme') NOT NULL DEFAULT 'ouvert', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_threads_user FOREIGN KEY (user_id) REFERENCES users(id), INDEX idx_threads_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS messages ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, thread_id INT UNSIGNED NOT NULL, sender_user_id INT UNSIGNED NOT NULL, body TEXT NOT NULL, file_path VARCHAR(255), read_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_messages_thread FOREIGN KEY (thread_id) REFERENCES messages_threads(id) ON DELETE CASCADE, CONSTRAINT fk_messages_sender FOREIGN KEY (sender_user_id) REFERENCES users(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS reviews ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, intervention_id INT UNSIGNED NOT NULL, rating TINYINT UNSIGNED NOT NULL, private_comment TEXT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_reviews_user FOREIGN KEY (user_id) REFERENCES users(id), CONSTRAINT fk_reviews_intervention FOREIGN KEY (intervention_id) REFERENCES interventions(id), UNIQUE KEY uniq_review_intervention (intervention_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS site_photos ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, stored_name VARCHAR(190) NOT NULL, original_name VARCHAR(190), mime_type VARCHAR(120) NOT NULL, alt_text VARCHAR(255) NOT NULL, caption VARCHAR(255), service_slug VARCHAR(120), city_slug VARCHAR(120), placement ENUM('accueil','service','article','zone','general') NOT NULL DEFAULT 'general', status ENUM('publiee','masquee') NOT NULL DEFAULT 'publiee', created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_site_photos_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_site_photos_status (status), INDEX idx_site_photos_service (service_slug), INDEX idx_site_photos_city (city_slug), INDEX idx_site_photos_placement (placement) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS blog_posts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(160) NOT NULL UNIQUE, title VARCHAR(190) NOT NULL, meta_description VARCHAR(255) NOT NULL, h1 VARCHAR(190) NOT NULL, excerpt TEXT, body TEXT NOT NULL, service_slug VARCHAR(120), status ENUM('brouillon','publie') NOT NULL DEFAULT 'publie', published_at DATETIME NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_blog_posts_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_blog_posts_status (status), INDEX idx_blog_posts_slug (slug), INDEX idx_blog_posts_service (service_slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS password_resets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, token_hash VARCHAR(255) NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_resets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_resets_token (token_hash) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS activity_logs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NULL, action VARCHAR(120) NOT NULL, meta_json JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_logs_action (action), INDEX idx_logs_created (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS sessions ( session_id VARCHAR(128) NOT NULL PRIMARY KEY, expires INT UNSIGNED NOT NULL, data MEDIUMTEXT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT IGNORE INTO settings (name, value) VALUES ('business_name', 'SaaS DEMO'), ('business_email', 'demo@saas-demo.test'), ('business_phone', '079 000 00 00'), ('business_address', 'Rue Démo 1\n1000 Lausanne'), ('quote_validity_days', '30'), ('invoice_due_days', '30'), ('vat_enabled', 'false'), ('vat_rate', '8.1'), ('vat_label', 'TVA non applicable'), ('quote_conditions', 'Devis valable selon la date indiquée. Intervention sur rendez-vous.'), ('invoice_conditions', 'Paiement par virement bancaire, TWINT manuel ou cash selon accord.'), ('quote_price_end_room', '55'), ('quote_price_end_kitchen', '150'), ('quote_price_end_bathroom', '70'), ('quote_price_end_windows', '120'), ('quote_price_end_blinds', '90'), ('quote_price_end_balcony', '60'), ('quote_price_end_cellar_garage', '50'), ('quote_price_window_inside', '10'), ('quote_price_window_inside_outside', '15'), ('quote_price_blind', '8'), ('quote_price_difficult_access', '40'), ('quote_price_regular_hourly', '40'), ('responsible_employee_ids', ''); INSERT IGNORE INTO blog_posts (slug, title, meta_description, h1, excerpt, body, service_slug, status, published_at) VALUES ('preparer-nettoyage-fin-de-bail', 'Comment préparer un nettoyage de fin de bail ?', 'Checklist nettoyage fin de bail: pièces, cuisine, sanitaires, vitres, stores, balcon, cave, accès et photos utiles.', 'Comment préparer un nettoyage de fin de bail ?', 'Une demande complète aide l’entreprise à chiffrer juste et à éviter les oublis avant l’état des lieux.', 'Un nettoyage de fin de bail se prépare avec les informations essentielles: nombre de pièces, surface, cuisine, sanitaires, vitres, stores, balcon, cave, garage, accès, étage et délai avant état des lieux.\n\nDes photos permettent de comprendre l’état du logement et les points sensibles. L’entreprise peut ensuite confirmer le périmètre, les options et le planning.\n\nUn devis clair indique ce qui est inclus, les conditions, la validité et les prochaines étapes.', 'nettoyage-fin-de-bail', 'publie', NOW()), ('devis-clair-nettoyage', 'Devis de nettoyage: les éléments à faire apparaître', 'Les points essentiels d’un devis professionnel de nettoyage: prestation, fréquence, surface, TVA, conditions, validité et options.', 'Devis de nettoyage: les éléments à faire apparaître', 'Un devis lisible rassure le client et limite les malentendus avant l’intervention.', 'Un devis de nettoyage doit présenter le client, le lieu, le type de prestation, la surface, les quantités, les options, la TVA, l’acompte éventuel et les conditions.\n\nLa description doit préciser ce qui est inclus: sols, vitres, sanitaires, cuisine, stores, consommables, accès ou fréquence.\n\nUne fois accepté, le devis peut être converti en passage, contrat récurrent ou facture afin de garder une chaîne commerciale cohérente.', 'nettoyage-regulier', 'publie', NOW()), ('planifier-passages-nettoyage', 'Planifier les passages de nettoyage sans perdre le suivi client', 'Organisation d’un planning de nettoyage: confirmations, employés assignés, commentaires internes, messages et facturation.', 'Planifier les passages de nettoyage sans perdre le suivi client', 'Le planning devient plus fiable quand les informations commerciales et terrain restent au même endroit.', 'La planification d’un passage de nettoyage ne consiste pas seulement à choisir une date. Il faut assigner les bonnes personnes, garder les commentaires internes, confirmer les horaires au client et suivre l’état de l’intervention.\n\nUn espace client simplifie les échanges: le client retrouve ses devis, factures et messages sans chercher dans ses emails.\n\nPour l’entreprise, les interventions terminées peuvent alimenter les avis, les factures et l’historique client.', 'nettoyage-regulier', 'publie', NOW()); -- --------------------------------------------------------------------- -- Source : sql/2026-06-saas-tenancy.sql -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS companies ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(120) NOT NULL, name VARCHAR(190) NOT NULL, legal_name VARCHAR(190), email VARCHAR(190), phone VARCHAR(60), address TEXT, website_url VARCHAR(255), logo_path VARCHAR(255), primary_color VARCHAR(20) NOT NULL DEFAULT '#0b6b88', secondary_color VARCHAR(20) NOT NULL DEFAULT '#f3b23c', vat_enabled TINYINT(1) NOT NULL DEFAULT 0, vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0, vat_label VARCHAR(190) NOT NULL DEFAULT 'TVA non applicable', iban VARCHAR(34), service_zones_json JSON NULL, public_site_enabled TINYINT(1) NOT NULL DEFAULT 1, status ENUM('active','suspended','archived') NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_companies_slug (slug), INDEX idx_companies_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS company_domains ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, hostname VARCHAR(190) NOT NULL, type ENUM('subdomain','custom') NOT NULL DEFAULT 'subdomain', status ENUM('pending','active','disabled') NOT NULL DEFAULT 'pending', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_company_domains_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, UNIQUE KEY uniq_company_domains_hostname (hostname), INDEX idx_company_domains_company (company_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO companies (slug, name, legal_name, email, phone, address, primary_color, secondary_color, vat_enabled, vat_rate, vat_label, status, created_at) VALUES ('saas-demo', 'SaaS DEMO', 'SaaS DEMO Sàrl', 'demo@saas-demo.test', '079 000 00 00', 'Rue Démo 1\n1000 Lausanne', '#064b78', '#f5b84b', 0, 8.1, 'TVA non applicable', 'active', NOW()) ON DUPLICATE KEY UPDATE name=VALUES(name), legal_name=VALUES(legal_name), email=VALUES(email), phone=VALUES(phone), address=VALUES(address), updated_at=NOW(); SET @default_company_id = (SELECT id FROM companies WHERE slug='saas-demo' LIMIT 1); ALTER TABLE users ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE clients_profiles ADD COLUMN company_id INT UNSIGNED NULL AFTER user_id; ALTER TABLE settings ADD COLUMN company_id INT UNSIGNED NULL FIRST; ALTER TABLE bank_accounts ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE quote_requests ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE quote_request_files ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE quotes ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE quote_items ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE quote_acceptances ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE interventions ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE intervention_staff ADD COLUMN company_id INT UNSIGNED NULL AFTER intervention_id; ALTER TABLE invoices ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE invoice_items ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE payments ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE messages_threads ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE messages ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE reviews ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE site_photos ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE blog_posts ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE password_resets ADD COLUMN company_id INT UNSIGNED NULL AFTER id; ALTER TABLE activity_logs ADD COLUMN company_id INT UNSIGNED NULL AFTER id; UPDATE users SET company_id=@default_company_id WHERE company_id IS NULL; UPDATE clients_profiles p JOIN users u ON u.id=p.user_id SET p.company_id=u.company_id WHERE p.company_id IS NULL; UPDATE settings SET company_id=@default_company_id WHERE company_id IS NULL; UPDATE bank_accounts SET company_id=@default_company_id WHERE company_id IS NULL; UPDATE quote_requests SET company_id=@default_company_id WHERE company_id IS NULL; UPDATE quote_request_files f JOIN quote_requests r ON r.id=f.quote_request_id SET f.company_id=r.company_id WHERE f.company_id IS NULL; UPDATE quotes SET company_id=@default_company_id WHERE company_id IS NULL; UPDATE quote_items qi JOIN quotes q ON q.id=qi.quote_id SET qi.company_id=q.company_id WHERE qi.company_id IS NULL; UPDATE quote_acceptances qa JOIN quotes q ON q.id=qa.quote_id SET qa.company_id=q.company_id WHERE qa.company_id IS NULL; UPDATE interventions SET company_id=@default_company_id WHERE company_id IS NULL; UPDATE intervention_staff s JOIN interventions i ON i.id=s.intervention_id SET s.company_id=i.company_id WHERE s.company_id IS NULL; UPDATE invoices SET company_id=@default_company_id WHERE company_id IS NULL; UPDATE invoice_items ii JOIN invoices i ON i.id=ii.invoice_id SET ii.company_id=i.company_id WHERE ii.company_id IS NULL; UPDATE payments p JOIN invoices i ON i.id=p.invoice_id SET p.company_id=i.company_id WHERE p.company_id IS NULL; UPDATE messages_threads SET company_id=@default_company_id WHERE company_id IS NULL; UPDATE messages m JOIN messages_threads t ON t.id=m.thread_id SET m.company_id=t.company_id WHERE m.company_id IS NULL; UPDATE reviews r JOIN interventions i ON i.id=r.intervention_id SET r.company_id=i.company_id WHERE r.company_id IS NULL; UPDATE site_photos SET company_id=@default_company_id WHERE company_id IS NULL; UPDATE blog_posts SET company_id=@default_company_id WHERE company_id IS NULL; UPDATE password_resets pr JOIN users u ON u.id=pr.user_id SET pr.company_id=u.company_id WHERE pr.company_id IS NULL; UPDATE activity_logs l LEFT JOIN users u ON u.id=l.user_id SET l.company_id=COALESCE(u.company_id, @default_company_id) WHERE l.company_id IS NULL; ALTER TABLE settings MODIFY company_id INT UNSIGNED NOT NULL; ALTER TABLE users ADD INDEX idx_users_company (company_id); ALTER TABLE clients_profiles ADD INDEX idx_clients_profiles_company (company_id); ALTER TABLE settings DROP PRIMARY KEY, ADD PRIMARY KEY (company_id, name); ALTER TABLE bank_accounts ADD INDEX idx_bank_accounts_company (company_id); ALTER TABLE quote_requests ADD INDEX idx_quote_requests_company (company_id); ALTER TABLE quote_request_files ADD INDEX idx_quote_request_files_company (company_id); ALTER TABLE quotes ADD INDEX idx_quotes_company (company_id); ALTER TABLE quote_items ADD INDEX idx_quote_items_company (company_id); ALTER TABLE quote_acceptances ADD INDEX idx_quote_acceptances_company (company_id); ALTER TABLE interventions ADD INDEX idx_interventions_company (company_id); ALTER TABLE intervention_staff ADD INDEX idx_intervention_staff_company (company_id); ALTER TABLE invoices ADD INDEX idx_invoices_company (company_id); ALTER TABLE invoice_items ADD INDEX idx_invoice_items_company (company_id); ALTER TABLE payments ADD INDEX idx_payments_company (company_id); ALTER TABLE messages_threads ADD INDEX idx_messages_threads_company (company_id); ALTER TABLE messages ADD INDEX idx_messages_company (company_id); ALTER TABLE reviews ADD INDEX idx_reviews_company (company_id); ALTER TABLE site_photos ADD INDEX idx_site_photos_company (company_id); ALTER TABLE blog_posts ADD INDEX idx_blog_posts_company (company_id); ALTER TABLE password_resets ADD INDEX idx_password_resets_company (company_id); ALTER TABLE activity_logs ADD INDEX idx_activity_logs_company (company_id); ALTER TABLE users ADD CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT; ALTER TABLE settings ADD CONSTRAINT fk_settings_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE; -- --------------------------------------------------------------------- -- Source : sql/2026-05-public-quote-requests.sql -- --------------------------------------------------------------------- ALTER TABLE users ADD COLUMN must_change_password TINYINT(1) NOT NULL DEFAULT 0 AFTER status; ALTER TABLE quote_requests MODIFY user_id INT UNSIGNED NULL, ADD COLUMN contact_first_name VARCHAR(120) NULL AFTER user_id, ADD COLUMN contact_last_name VARCHAR(120) NULL AFTER contact_first_name, ADD COLUMN contact_email VARCHAR(190) NULL AFTER contact_last_name, ADD COLUMN contact_phone VARCHAR(60) NULL AFTER contact_email, ADD COLUMN wants_account TINYINT(1) NOT NULL DEFAULT 0 AFTER contact_phone, ADD INDEX idx_quote_requests_contact_email (contact_email); -- --------------------------------------------------------------------- -- Source : sql/2026-05-quote-notes.sql -- --------------------------------------------------------------------- ALTER TABLE quotes ADD COLUMN notes TEXT NULL AFTER conditions; -- --------------------------------------------------------------------- -- Source : sql/2026-05-staff-interventions.sql -- --------------------------------------------------------------------- ALTER TABLE users MODIFY role ENUM('client','admin','employee') NOT NULL DEFAULT 'client'; ALTER TABLE interventions ADD COLUMN assigned_user_id INT UNSIGNED NULL AFTER user_id, ADD COLUMN hourly_rate DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER ends_at, ADD COLUMN actual_duration_minutes INT UNSIGNED NOT NULL DEFAULT 0 AFTER hourly_rate, ADD COLUMN completed_at DATETIME NULL AFTER actual_duration_minutes, ADD COLUMN work_note TEXT NULL AFTER client_comment, ADD COLUMN recurring_group VARCHAR(40) NULL AFTER work_note, ADD INDEX idx_interventions_assigned (assigned_user_id), ADD CONSTRAINT fk_interventions_assigned_user FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL; -- --------------------------------------------------------------------- -- Source : sql/2026-05-employees-rate.sql -- --------------------------------------------------------------------- ALTER TABLE users ADD COLUMN internal_hourly_rate DECIMAL(10,2) NULL AFTER status; -- --------------------------------------------------------------------- -- Source : sql/2026-05-bank-accounts-qr-invoices.sql -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS bank_accounts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, label VARCHAR(120) NOT NULL, holder_name VARCHAR(190) NOT NULL, holder_street VARCHAR(190) NOT NULL, holder_building_no VARCHAR(20), holder_postal_code VARCHAR(20) NOT NULL, holder_city VARCHAR(120) NOT NULL, holder_country CHAR(2) NOT NULL DEFAULT 'CH', iban VARCHAR(34) NOT NULL, bank_name VARCHAR(190), is_default TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_bank_accounts_active (is_active), INDEX idx_bank_accounts_default (is_default) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; ALTER TABLE invoices ADD COLUMN bank_account_id INT UNSIGNED NULL AFTER intervention_id, ADD CONSTRAINT fk_invoices_bank_account FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE SET NULL; -- --------------------------------------------------------------------- -- Source : sql/2026-06-intervention-team.sql -- --------------------------------------------------------------------- -- Mise a jour SuitePro SaaS - Juin 2026 -- Permet d'assigner une intervention a plusieurs employes/admins. CREATE TABLE IF NOT EXISTS intervention_staff ( intervention_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (intervention_id, user_id), CONSTRAINT fk_intervention_staff_intervention FOREIGN KEY (intervention_id) REFERENCES interventions(id) ON DELETE CASCADE, CONSTRAINT fk_intervention_staff_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_intervention_staff_user (user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Reprend les anciennes assignations simples pour remplir l'equipe. INSERT IGNORE INTO intervention_staff (intervention_id, user_id) SELECT id, assigned_user_id FROM interventions WHERE assigned_user_id IS NOT NULL; -- --------------------------------------------------------------------- -- Source : sql/2026-06-settings-quote-pricing-responsible-employees.sql -- --------------------------------------------------------------------- -- Mise a jour SuitePro SaaS - Juin 2026 -- A importer sur une base existante, sans reimporter database.sql. -- Ajoute les reglages de l'assistant devis et la liste des employes responsables. INSERT INTO settings (name, value) VALUES ('quote_price_end_room', '55'), ('quote_price_end_kitchen', '150'), ('quote_price_end_bathroom', '70'), ('quote_price_end_windows', '120'), ('quote_price_end_blinds', '90'), ('quote_price_end_balcony', '60'), ('quote_price_end_cellar_garage', '50'), ('quote_price_window_inside', '10'), ('quote_price_window_inside_outside', '15'), ('quote_price_blind', '8'), ('quote_price_difficult_access', '40'), ('quote_price_regular_hourly', '40'), ('responsible_employee_ids', '') ON DUPLICATE KEY UPDATE value = VALUES(value), updated_at = CURRENT_TIMESTAMP; -- Optionnel : activer un employe responsable si son compte existe deja. -- Decommentez puis adaptez l'email si necessaire. -- -- UPDATE settings -- SET value = ( -- SELECT GROUP_CONCAT(id ORDER BY id) -- FROM users -- WHERE role = 'employee' -- AND status = 'active' -- AND email IN ('employe@example.test') -- ) -- WHERE name = 'responsible_employee_ids'; -- --------------------------------------------------------------------- -- Source : sql/2026-06-site-builder.sql -- --------------------------------------------------------------------- -- Studio site: stockage JSON des blocs, brouillons et historiques. -- MEDIUMTEXT permet de garder plusieurs versions sans dépasser la limite TEXT. ALTER TABLE settings MODIFY value MEDIUMTEXT NULL; -- --------------------------------------------------------------------- -- Source : sql/2026-06-premium-saas-bricks.sql -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS company_subscriptions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, plan_code VARCHAR(60) NOT NULL DEFAULT 'trial', status VARCHAR(40) NOT NULL DEFAULT 'trialing', seats INT UNSIGNED NOT NULL DEFAULT 3, monthly_price DECIMAL(10,2) NOT NULL DEFAULT 0, currency CHAR(3) NOT NULL DEFAULT 'CHF', trial_ends_at DATETIME NULL, current_period_end DATETIME NULL, stripe_customer_id VARCHAR(190), stripe_subscription_id VARCHAR(190), created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_company_subscriptions_company (company_id), INDEX idx_company_subscriptions_status (status), CONSTRAINT fk_company_subscriptions_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS demo_reset_jobs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 0, interval_hours INT UNSIGNED NOT NULL DEFAULT 24, last_reset_at DATETIME NULL, next_reset_at DATETIME NULL, notes TEXT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_demo_reset_company (company_id), CONSTRAINT fk_demo_reset_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS field_reports ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, intervention_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, arrival_status VARCHAR(80), before_note TEXT, after_note TEXT, materials_used TEXT, client_signature_name VARCHAR(190), created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_field_reports_company (company_id), INDEX idx_field_reports_intervention (intervention_id), CONSTRAINT fk_field_reports_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE, CONSTRAINT fk_field_reports_intervention FOREIGN KEY (intervention_id) REFERENCES interventions(id) ON DELETE CASCADE, CONSTRAINT fk_field_reports_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Source : sql/2026-06-saas-production-hardening.sql -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS account_invitations ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, invited_by INT UNSIGNED NULL, token_hash CHAR(64) NOT NULL, status ENUM('pending','accepted','revoked') NOT NULL DEFAULT 'pending', expires_at DATETIME NOT NULL, accepted_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_account_invitation_hash (token_hash), INDEX idx_account_invitation_user (company_id, user_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Source : sql/2026-06-accounting-camt.sql -- --------------------------------------------------------------------- -- Module comptabilité suisse: plan comptable, écritures, CAMT, banque, achats et TVA. -- Le service Node crée aussi ces tables automatiquement au premier accès. CREATE TABLE IF NOT EXISTS accounting_accounts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, code VARCHAR(20) NOT NULL, name VARCHAR(190) NOT NULL, type ENUM('asset','liability','equity','revenue','expense') NOT NULL, category VARCHAR(120), vat_code VARCHAR(40), is_active TINYINT(1) NOT NULL DEFAULT 1, is_system TINYINT(1) NOT NULL DEFAULT 0, sort_order INT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_accounting_accounts_company_code (company_id, code), INDEX idx_accounting_accounts_company_type (company_id, type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS accounting_settings ( company_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, value TEXT, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (company_id, name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS accounting_periods ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, label VARCHAR(120) NOT NULL, starts_on DATE NOT NULL, ends_on DATE NOT NULL, status ENUM('open','closed','locked') NOT NULL DEFAULT 'open', closed_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_accounting_periods_company_range (company_id, starts_on, ends_on) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS accounting_entries ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, journal_code VARCHAR(40) NOT NULL DEFAULT 'general', entry_date DATE NOT NULL, value_date DATE NULL, document_type VARCHAR(60), document_id INT UNSIGNED NULL, source VARCHAR(80), source_id INT UNSIGNED NULL, label VARCHAR(255) NOT NULL, reference VARCHAR(120), currency CHAR(3) NOT NULL DEFAULT 'CHF', status ENUM('draft','posted','locked') NOT NULL DEFAULT 'posted', total_debit DECIMAL(12,2) NOT NULL DEFAULT 0, total_credit DECIMAL(12,2) NOT NULL DEFAULT 0, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_accounting_entry_source (company_id, source, source_id), INDEX idx_accounting_entries_company_date (company_id, entry_date), INDEX idx_accounting_entries_document (company_id, document_type, document_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS accounting_entry_lines ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, entry_id INT UNSIGNED NOT NULL, account_id INT UNSIGNED NOT NULL, description VARCHAR(255), debit DECIMAL(12,2) NOT NULL DEFAULT 0, credit DECIMAL(12,2) NOT NULL DEFAULT 0, vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0, vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0, position INT UNSIGNED NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_accounting_lines_company_account (company_id, account_id), CONSTRAINT fk_accounting_lines_entry FOREIGN KEY (entry_id) REFERENCES accounting_entries(id) ON DELETE CASCADE, CONSTRAINT fk_accounting_lines_account FOREIGN KEY (account_id) REFERENCES accounting_accounts(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS bank_statement_imports ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, bank_account_id INT UNSIGNED NULL, message_type VARCHAR(40) NOT NULL DEFAULT 'camt', message_id VARCHAR(190), file_name VARCHAR(255) NOT NULL, file_path VARCHAR(255), imported_by INT UNSIGNED NULL, total_transactions INT UNSIGNED NOT NULL DEFAULT 0, matched_transactions INT UNSIGNED NOT NULL DEFAULT 0, status ENUM('imported','partial','matched','error') NOT NULL DEFAULT 'imported', notes TEXT, imported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_bank_imports_company (company_id, imported_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS bank_transactions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, bank_account_id INT UNSIGNED NULL, import_id INT UNSIGNED NULL, message_type VARCHAR(40), statement_id VARCHAR(190), entry_reference VARCHAR(190), transaction_reference VARCHAR(190), amount DECIMAL(12,2) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'CHF', direction ENUM('credit','debit') NOT NULL, booking_date DATE NULL, value_date DATE NULL, counterparty_name VARCHAR(190), counterparty_iban VARCHAR(34), payment_reference VARCHAR(190), remittance_info TEXT, status ENUM('unmatched','matched','ignored','booked') NOT NULL DEFAULT 'unmatched', invoice_id INT UNSIGNED NULL, payment_id INT UNSIGNED NULL, payroll_run_id INT UNSIGNED NULL, payroll_payslip_id INT UNSIGNED NULL, accounting_entry_id INT UNSIGNED NULL, match_score INT UNSIGNED NOT NULL DEFAULT 0, raw_json JSON, hash CHAR(64) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_bank_transaction_hash (company_id, hash), INDEX idx_bank_transactions_company_status (company_id, status), INDEX idx_bank_transactions_company_date (company_id, booking_date), CONSTRAINT fk_bank_transactions_import FOREIGN KEY (import_id) REFERENCES bank_statement_imports(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS accounting_expenses ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, supplier_name VARCHAR(190) NOT NULL, supplier_invoice_number VARCHAR(120), expense_date DATE NOT NULL, due_date DATE NULL, account_id INT UNSIGNED NOT NULL, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0, vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0, vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0, total DECIMAL(12,2) NOT NULL DEFAULT 0, paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0, status ENUM('open','paid','cancelled') NOT NULL DEFAULT 'open', paid_at DATE NULL, attachment_path VARCHAR(255), notes TEXT, accounting_entry_id INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_accounting_expenses_company_status (company_id, status), INDEX idx_accounting_expenses_company_date (company_id, expense_date), CONSTRAINT fk_accounting_expenses_account FOREIGN KEY (account_id) REFERENCES accounting_accounts(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Source : sql/2026-06-payroll-swiss.sql -- --------------------------------------------------------------------- -- Module salaires Suisse: profils RH, paramètres sociaux, cycles de paie et fiches PDF. -- Le service Node crée aussi ces tables automatiquement au premier accès. CREATE TABLE IF NOT EXISTS payroll_settings ( company_id INT UNSIGNED NOT NULL, name VARCHAR(120) NOT NULL, value TEXT, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (company_id, name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payroll_employee_profiles ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, ahv_number VARCHAR(40), date_of_birth DATE NULL, nationality VARCHAR(80), canton VARCHAR(8), municipality VARCHAR(120), permit_type VARCHAR(80), marital_status VARCHAR(80), children_count INT UNSIGNED NOT NULL DEFAULT 0, employment_start DATE NULL, employment_end DATE NULL, salary_type ENUM('monthly','hourly') NOT NULL DEFAULT 'monthly', monthly_salary DECIMAL(12,2) NOT NULL DEFAULT 0, hourly_rate DECIMAL(12,2) NOT NULL DEFAULT 0, monthly_hours DECIMAL(8,2) NOT NULL DEFAULT 0, weekly_hours DECIMAL(8,2) NOT NULL DEFAULT 42, working_percentage DECIMAL(5,2) NOT NULL DEFAULT 100, vacation_pay_rate DECIMAL(5,2) NOT NULL DEFAULT 0, thirteenth_enabled TINYINT(1) NOT NULL DEFAULT 0, lpp_enabled TINYINT(1) NOT NULL DEFAULT 1, lpp_employee_rate DECIMAL(5,2) NULL, lpp_employer_rate DECIMAL(5,2) NULL, laa_nbu_employee_rate DECIMAL(5,2) NULL, laa_bu_employer_rate DECIMAL(5,2) NULL, ijm_employee_rate DECIMAL(5,2) NULL, ijm_employer_rate DECIMAL(5,2) NULL, source_tax_subject TINYINT(1) NOT NULL DEFAULT 0, source_tax_tariff VARCHAR(20), church_tax TINYINT(1) NOT NULL DEFAULT 0, source_tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0, source_tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0, other_monthly_deduction DECIMAL(12,2) NOT NULL DEFAULT 0, bank_iban VARCHAR(40), payroll_note TEXT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_payroll_profile_user (company_id, user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payroll_runs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, period_month CHAR(7) NOT NULL, status ENUM('draft','approved','paid','locked') NOT NULL DEFAULT 'draft', payment_date DATE NULL, notes TEXT, total_gross DECIMAL(12,2) NOT NULL DEFAULT 0, total_net DECIMAL(12,2) NOT NULL DEFAULT 0, total_employee_deductions DECIMAL(12,2) NOT NULL DEFAULT 0, total_employer_costs DECIMAL(12,2) NOT NULL DEFAULT 0, created_by INT UNSIGNED NULL, approved_by INT UNSIGNED NULL, approved_at DATETIME NULL, paid_at DATETIME NULL, accounting_entry_id INT UNSIGNED NULL, pain001_path VARCHAR(255), swissdec_export_path VARCHAR(255), camt_matched_amount DECIMAL(12,2) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_payroll_run_period (company_id, period_month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payroll_absences ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, type ENUM('vacation','sickness','accident','unpaid','other') NOT NULL DEFAULT 'vacation', starts_on DATE NOT NULL, ends_on DATE NOT NULL, hours DECIMAL(8,2) NOT NULL DEFAULT 0, paid_percent DECIMAL(5,2) NOT NULL DEFAULT 100, status ENUM('requested','approved','rejected','cancelled') NOT NULL DEFAULT 'requested', note TEXT, reviewed_by INT UNSIGNED NULL, reviewed_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_payroll_absences_company_user (company_id, user_id, starts_on) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payroll_source_tax_rates ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, tax_year INT UNSIGNED NOT NULL, canton VARCHAR(8) NOT NULL, tariff_code VARCHAR(20) NOT NULL, children_count INT UNSIGNED NOT NULL DEFAULT 0, church_tax TINYINT(1) NOT NULL DEFAULT 0, monthly_income_from DECIMAL(12,2) NOT NULL DEFAULT 0, monthly_income_to DECIMAL(12,2) NOT NULL DEFAULT 999999, rate DECIMAL(7,4) NOT NULL DEFAULT 0, fixed_amount DECIMAL(12,2) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_payroll_source_tax_lookup (company_id, tax_year, canton, tariff_code, children_count, church_tax, monthly_income_from) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS payroll_payslips ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, run_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, period_month CHAR(7) NOT NULL, base_salary DECIMAL(12,2) NOT NULL DEFAULT 0, hours_worked DECIMAL(8,2) NOT NULL DEFAULT 0, vacation_pay DECIMAL(12,2) NOT NULL DEFAULT 0, thirteenth_pay DECIMAL(12,2) NOT NULL DEFAULT 0, bonus DECIMAL(12,2) NOT NULL DEFAULT 0, allowances DECIMAL(12,2) NOT NULL DEFAULT 0, expenses_reimbursement DECIMAL(12,2) NOT NULL DEFAULT 0, gross_salary DECIMAL(12,2) NOT NULL DEFAULT 0, employee_deductions DECIMAL(12,2) NOT NULL DEFAULT 0, employer_contributions DECIMAL(12,2) NOT NULL DEFAULT 0, net_salary DECIMAL(12,2) NOT NULL DEFAULT 0, employer_total_cost DECIMAL(12,2) NOT NULL DEFAULT 0, lines_json JSON, pdf_path VARCHAR(255), status ENUM('draft','approved','paid','locked') NOT NULL DEFAULT 'draft', generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_payroll_payslip_user_run (run_id, user_id), INDEX idx_payroll_payslips_company_period (company_id, period_month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Source : sql/2026-06-hr-absence-planning.sql -- --------------------------------------------------------------------- ALTER TABLE payroll_absences MODIFY type ENUM('vacation','sickness','accident','unpaid','maternity','paternity','military','care','training','remote','other') NOT NULL DEFAULT 'vacation'; ALTER TABLE payroll_absences ADD COLUMN half_day ENUM('full','morning','afternoon') NOT NULL DEFAULT 'full' AFTER hours, ADD COLUMN certificate_required TINYINT(1) NOT NULL DEFAULT 0 AFTER status, ADD COLUMN certificate_received TINYINT(1) NOT NULL DEFAULT 0 AFTER certificate_required, ADD COLUMN replacement_user_id INT UNSIGNED NULL AFTER certificate_received, ADD COLUMN decision_note TEXT NULL AFTER note, ADD COLUMN created_by INT UNSIGNED NULL AFTER decision_note; -- --------------------------------------------------------------------- -- Source : sql/2026-06-hr-entitlements-certificates.sql -- --------------------------------------------------------------------- ALTER TABLE payroll_employee_profiles ADD COLUMN vacation_entitlement_days DECIMAL(5,2) NOT NULL DEFAULT 20 AFTER vacation_pay_rate; ALTER TABLE payroll_absences ADD COLUMN certificate_file_path VARCHAR(255) NULL AFTER certificate_received, ADD COLUMN certificate_original_name VARCHAR(190) NULL AFTER certificate_file_path, ADD COLUMN certificate_mime VARCHAR(120) NULL AFTER certificate_original_name, ADD COLUMN certificate_uploaded_at DATETIME NULL AFTER certificate_mime; -- --------------------------------------------------------------------- -- Source : sql/2026-06-manual-time-entries.sql -- --------------------------------------------------------------------- ALTER TABLE field_time_entries ADD COLUMN IF NOT EXISTS work_date DATE NULL AFTER intervention_id, ADD COLUMN IF NOT EXISTS morning_starts_at DATETIME NULL AFTER work_date, ADD COLUMN IF NOT EXISTS morning_ends_at DATETIME NULL AFTER morning_starts_at, ADD COLUMN IF NOT EXISTS afternoon_starts_at DATETIME NULL AFTER morning_ends_at, ADD COLUMN IF NOT EXISTS afternoon_ends_at DATETIME NULL AFTER afternoon_starts_at, ADD COLUMN IF NOT EXISTS morning_minutes INT UNSIGNED NULL AFTER break_minutes, ADD COLUMN IF NOT EXISTS afternoon_minutes INT UNSIGNED NULL AFTER morning_minutes, ADD COLUMN IF NOT EXISTS duration_minutes INT UNSIGNED NULL AFTER afternoon_minutes, ADD COLUMN IF NOT EXISTS work_note TEXT NULL AFTER expense_note, ADD COLUMN IF NOT EXISTS source_type VARCHAR(30) NOT NULL DEFAULT 'manual' AFTER work_note, ADD COLUMN IF NOT EXISTS submitted_by INT UNSIGNED NULL AFTER status, ADD COLUMN IF NOT EXISTS approved_by INT UNSIGNED NULL AFTER submitted_by, ADD COLUMN IF NOT EXISTS approved_at DATETIME NULL AFTER approved_by, ADD COLUMN IF NOT EXISTS updated_at DATETIME NULL AFTER created_at; UPDATE field_time_entries SET work_date = DATE(starts_at) WHERE work_date IS NULL AND starts_at IS NOT NULL; -- --------------------------------------------------------------------- -- Source : sql/2026-06-operations-suite.sql -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS fiduciary_exports ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, export_type VARCHAR(40) NOT NULL, period_from DATE NULL, period_to DATE NULL, status VARCHAR(30) NOT NULL DEFAULT 'prepared', notes TEXT NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_fiduciary_exports_company (company_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS inventory_assets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, asset_type VARCHAR(30) NOT NULL, name VARCHAR(190) NOT NULL, sku VARCHAR(80) NULL, status VARCHAR(30) NOT NULL DEFAULT 'active', quantity DECIMAL(12,2) NOT NULL DEFAULT 0, unit VARCHAR(30) NOT NULL DEFAULT 'pce', location VARCHAR(190) NULL, purchase_price DECIMAL(12,2) NOT NULL DEFAULT 0, replacement_value DECIMAL(12,2) NOT NULL DEFAULT 0, service_interval_days INT UNSIGNED NULL, last_service_on DATE NULL, next_service_on DATE NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, INDEX idx_inventory_assets_company (company_id, asset_type, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS supplier_orders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, supplier_name VARCHAR(190) NOT NULL, order_number VARCHAR(90) NULL, status VARCHAR(30) NOT NULL DEFAULT 'ordered', order_date DATE NULL, delivery_date DATE NULL, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0, currency CHAR(3) NOT NULL DEFAULT 'CHF', delivery_note_number VARCHAR(90) NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_supplier_orders_company (company_id, status, order_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS field_time_entries ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NULL, intervention_id INT UNSIGNED NULL, work_date DATE NULL, morning_starts_at DATETIME NULL, morning_ends_at DATETIME NULL, afternoon_starts_at DATETIME NULL, afternoon_ends_at DATETIME NULL, starts_at DATETIME NULL, ends_at DATETIME NULL, break_minutes INT UNSIGNED NOT NULL DEFAULT 0, morning_minutes INT UNSIGNED NULL, afternoon_minutes INT UNSIGNED NULL, duration_minutes INT UNSIGNED NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, expense_amount DECIMAL(12,2) NOT NULL DEFAULT 0, expense_note VARCHAR(255) NULL, work_note TEXT NULL, source_type VARCHAR(30) NOT NULL DEFAULT 'manual', status VARCHAR(30) NOT NULL DEFAULT 'submitted', submitted_by INT UNSIGNED NULL, approved_by INT UNSIGNED NULL, approved_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, INDEX idx_field_time_entries_company (company_id, starts_at, status), INDEX idx_field_time_entries_user_period (company_id, user_id, work_date, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS recurring_contracts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, client_id INT UNSIGNED NULL, title VARCHAR(190) NOT NULL, service_type VARCHAR(140) NULL, recurrence_rule VARCHAR(140) NOT NULL DEFAULT 'Mensuel', starts_on DATE NULL, renews_on DATE NULL, notice_days INT UNSIGNED NOT NULL DEFAULT 60, monthly_amount DECIMAL(12,2) NOT NULL DEFAULT 0, status VARCHAR(30) NOT NULL DEFAULT 'active', next_invoice_on DATE NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_recurring_contracts_company (company_id, status, renews_on) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS quality_reports ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, intervention_id INT UNSIGNED NULL, user_id INT UNSIGNED NULL, checklist_json LONGTEXT NULL, before_photo_path VARCHAR(255) NULL, after_photo_path VARCHAR(255) NULL, client_signature VARCHAR(190) NULL, score DECIMAL(5,2) NOT NULL DEFAULT 0, status VARCHAR(30) NOT NULL DEFAULT 'submitted', signed_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_quality_reports_company (company_id, status, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS planning_routes ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, route_date DATE NULL, zone VARCHAR(140) NULL, assigned_user_id INT UNSIGNED NULL, status VARCHAR(30) NOT NULL DEFAULT 'optimized', total_travel_minutes INT UNSIGNED NOT NULL DEFAULT 0, stops_json LONGTEXT NULL, optimized_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_planning_routes_company (company_id, route_date, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS hr_documents ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NULL, doc_type VARCHAR(30) NOT NULL, title VARCHAR(190) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'draft', due_on DATE NULL, completed_on DATE NULL, file_path VARCHAR(255) NULL, signature_required TINYINT(1) NOT NULL DEFAULT 0, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_hr_documents_company (company_id, doc_type, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS support_tickets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, requester_user_id INT UNSIGNED NULL, channel VARCHAR(40) NOT NULL DEFAULT 'portal', priority VARCHAR(20) NOT NULL DEFAULT 'normal', sla_due_at DATETIME NULL, subject VARCHAR(190) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'open', category VARCHAR(90) NULL, assigned_to INT UNSIGNED NULL, resolution_notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_support_tickets_company (company_id, status, sla_due_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS knowledge_articles ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, title VARCHAR(190) NOT NULL, category VARCHAR(90) NULL, body TEXT NULL, audience VARCHAR(40) NOT NULL DEFAULT 'client', status VARCHAR(30) NOT NULL DEFAULT 'published', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL, INDEX idx_knowledge_articles_company (company_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS marketing_campaigns ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, campaign_type VARCHAR(30) NOT NULL, name VARCHAR(190) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'draft', segment VARCHAR(140) NULL, scheduled_at DATETIME NULL, sent_count INT UNSIGNED NOT NULL DEFAULT 0, conversion_count INT UNSIGNED NOT NULL DEFAULT 0, budget DECIMAL(12,2) NOT NULL DEFAULT 0, message TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_marketing_campaigns_company (company_id, campaign_type, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS ocr_documents ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, source_type VARCHAR(60) NOT NULL DEFAULT 'supplier_invoice', file_path VARCHAR(255) NULL, supplier_name VARCHAR(190) NULL, invoice_number VARCHAR(90) NULL, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0, vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0, confidence DECIMAL(5,2) NOT NULL DEFAULT 0, classification VARCHAR(90) NULL, status VARCHAR(30) NOT NULL DEFAULT 'uploaded', raw_text LONGTEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_ocr_documents_company (company_id, status, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS offline_queue_events ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NULL, device_id VARCHAR(90) NULL, event_type VARCHAR(80) NOT NULL, payload_json LONGTEXT NULL, sync_status VARCHAR(30) NOT NULL DEFAULT 'queued', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, synced_at DATETIME NULL, INDEX idx_offline_queue_company (company_id, sync_status, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS profitability_snapshots ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, period_month CHAR(7) NOT NULL, client_id INT UNSIGNED NULL, service_type VARCHAR(140) NULL, user_id INT UNSIGNED NULL, zone VARCHAR(140) NULL, revenue DECIMAL(12,2) NOT NULL DEFAULT 0, costs DECIMAL(12,2) NOT NULL DEFAULT 0, margin DECIMAL(12,2) NOT NULL DEFAULT 0, margin_rate DECIMAL(6,2) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_profitability_company (company_id, period_month, margin_rate) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Source : sql/2026-06-gallery-blog.sql -- --------------------------------------------------------------------- -- Mise a jour SuitePro SaaS - Juin 2026 -- Mediatheque photos publiques et articles utiles administrables. CREATE TABLE IF NOT EXISTS site_photos ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NULL, stored_name VARCHAR(190) NOT NULL, original_name VARCHAR(190), mime_type VARCHAR(120) NOT NULL, alt_text VARCHAR(255) NOT NULL, caption VARCHAR(255), service_slug VARCHAR(120), city_slug VARCHAR(120), placement ENUM('accueil','service','article','zone','general') NOT NULL DEFAULT 'general', status ENUM('publiee','masquee') NOT NULL DEFAULT 'publiee', created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_site_photos_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_site_photos_status (status), INDEX idx_site_photos_company (company_id), INDEX idx_site_photos_service (service_slug), INDEX idx_site_photos_city (city_slug), INDEX idx_site_photos_placement (placement) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS blog_posts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NULL, slug VARCHAR(160) NOT NULL, title VARCHAR(190) NOT NULL, meta_description VARCHAR(255) NOT NULL, h1 VARCHAR(190) NOT NULL, excerpt TEXT, body TEXT NOT NULL, service_slug VARCHAR(120), status ENUM('brouillon','publie') NOT NULL DEFAULT 'publie', published_at DATETIME NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_blog_posts_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, INDEX idx_blog_posts_status (status), INDEX idx_blog_posts_company (company_id), INDEX idx_blog_posts_slug (slug), INDEX idx_blog_posts_service (service_slug), UNIQUE KEY uniq_blog_posts_company_slug (company_id, slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET @demo_company_id = (SELECT id FROM companies WHERE slug='saas-demo' LIMIT 1); DELETE FROM blog_posts WHERE company_id=@demo_company_id AND slug IN ('preparer-intervention-artisan', 'devis-clair-travaux-services', 'planifier-chantier-client'); INSERT INTO blog_posts (company_id, slug, title, meta_description, h1, excerpt, body, service_slug, status, published_at) VALUES (@demo_company_id, 'preparer-nettoyage-fin-de-bail', 'Comment préparer un nettoyage de fin de bail ?', 'Checklist nettoyage fin de bail: pièces, cuisine, sanitaires, vitres, stores, balcon, cave, accès et photos utiles.', 'Comment préparer un nettoyage de fin de bail ?', 'Une demande complète aide l’entreprise à chiffrer juste et à éviter les oublis avant l’état des lieux.', 'Un nettoyage de fin de bail se prépare avec les informations essentielles: nombre de pièces, surface, cuisine, sanitaires, vitres, stores, balcon, cave, garage, accès, étage et délai avant état des lieux.\n\nDes photos permettent de comprendre l’état du logement et les points sensibles. L’entreprise peut ensuite confirmer le périmètre, les options et le planning.\n\nUn devis clair indique ce qui est inclus, les conditions, la validité et les prochaines étapes.', 'nettoyage-fin-de-bail', 'publie', NOW()), (@demo_company_id, 'devis-clair-nettoyage', 'Devis de nettoyage: les éléments à faire apparaître', 'Les points essentiels d’un devis professionnel de nettoyage: prestation, fréquence, surface, TVA, conditions, validité et options.', 'Devis de nettoyage: les éléments à faire apparaître', 'Un devis lisible rassure le client et limite les malentendus avant l’intervention.', 'Un devis de nettoyage doit présenter le client, le lieu, le type de prestation, la surface, les quantités, les options, la TVA, l’acompte éventuel et les conditions.\n\nLa description doit préciser ce qui est inclus: sols, vitres, sanitaires, cuisine, stores, consommables, accès ou fréquence.\n\nUne fois accepté, le devis peut être converti en passage, contrat récurrent ou facture afin de garder une chaîne commerciale cohérente.', 'nettoyage-regulier', 'publie', NOW()), (@demo_company_id, 'planifier-passages-nettoyage', 'Planifier les passages de nettoyage sans perdre le suivi client', 'Organisation d’un planning de nettoyage: confirmations, employés assignés, commentaires internes, messages et facturation.', 'Planifier les passages de nettoyage sans perdre le suivi client', 'Le planning devient plus fiable quand les informations commerciales et terrain restent au même endroit.', 'La planification d’un passage de nettoyage ne consiste pas seulement à choisir une date. Il faut assigner les bonnes personnes, garder les commentaires internes, confirmer les horaires au client et suivre l’état de l’intervention.\n\nUn espace client simplifie les échanges: le client retrouve ses devis, factures et messages sans chercher dans ses emails.\n\nPour l’entreprise, les interventions terminées peuvent alimenter les avis, les factures et l’historique client.', 'nettoyage-regulier', 'publie', NOW()) ON DUPLICATE KEY UPDATE company_id = VALUES(company_id), title = VALUES(title), meta_description = VALUES(meta_description), h1 = VALUES(h1), excerpt = VALUES(excerpt), body = VALUES(body), service_slug = VALUES(service_slug), status = VALUES(status), updated_at = CURRENT_TIMESTAMP; -- --------------------------------------------------------------------- -- Source : sql/2026-06-saas-hardening.sql -- --------------------------------------------------------------------- -- Durcissement V2 SaaS - Juin 2026 -- A importer apres sql/2026-06-saas-tenancy.sql sur une base existante. SET @demo_company_id = (SELECT id FROM companies WHERE slug='saas-demo' LIMIT 1); UPDATE companies SET name='SaaS DEMO', legal_name='SaaS DEMO Sàrl', email='demo@saas-demo.test', phone='079 000 00 00', address='Rue Démo 1\n1000 Lausanne', updated_at=NOW() WHERE slug='saas-demo'; UPDATE settings SET value = CASE name WHEN 'business_name' THEN 'SaaS DEMO' WHEN 'business_email' THEN 'demo@saas-demo.test' WHEN 'business_phone' THEN '079 000 00 00' WHEN 'business_address' THEN 'Rue Démo 1\n1000 Lausanne' ELSE value END WHERE company_id=@demo_company_id AND name IN ('business_name', 'business_email', 'business_phone', 'business_address'); DELETE FROM blog_posts WHERE slug IN ( 'nettoyage-regulier-taches-entreprise', 'erreurs-nettoyage-fin-de-bail-etat-des-lieux', 'vitres-stores-apres-pollen', 'menage-regulier-seniors-payerne-broye', 'frequence-nettoyage-bureaux', 'preparer-intervention-artisan', 'devis-clair-travaux-services', 'planifier-chantier-client' ); DELETE b FROM bank_accounts b JOIN companies c ON c.id=b.company_id WHERE c.slug='saas-demo' AND ( b.label LIKE '%Anais%' OR b.holder_name LIKE '%Anais%' OR b.holder_name LIKE '%Geisseler%' ); INSERT INTO blog_posts (company_id, slug, title, meta_description, h1, excerpt, body, service_slug, status, published_at) VALUES (@demo_company_id, 'preparer-nettoyage-fin-de-bail', 'Comment préparer un nettoyage de fin de bail ?', 'Checklist nettoyage fin de bail: pièces, cuisine, sanitaires, vitres, stores, balcon, cave, accès et photos utiles.', 'Comment préparer un nettoyage de fin de bail ?', 'Une demande complète aide l’entreprise à chiffrer juste et à éviter les oublis avant l’état des lieux.', 'Un nettoyage de fin de bail se prépare avec les informations essentielles: nombre de pièces, surface, cuisine, sanitaires, vitres, stores, balcon, cave, garage, accès, étage et délai avant état des lieux.\n\nDes photos permettent de comprendre l’état du logement et les points sensibles. L’entreprise peut ensuite confirmer le périmètre, les options et le planning.\n\nUn devis clair indique ce qui est inclus, les conditions, la validité et les prochaines étapes.', 'nettoyage-fin-de-bail', 'publie', NOW()), (@demo_company_id, 'devis-clair-nettoyage', 'Devis de nettoyage: les éléments à faire apparaître', 'Les points essentiels d’un devis professionnel de nettoyage: prestation, fréquence, surface, TVA, conditions, validité et options.', 'Devis de nettoyage: les éléments à faire apparaître', 'Un devis lisible rassure le client et limite les malentendus avant l’intervention.', 'Un devis de nettoyage doit présenter le client, le lieu, le type de prestation, la surface, les quantités, les options, la TVA, l’acompte éventuel et les conditions.\n\nLa description doit préciser ce qui est inclus: sols, vitres, sanitaires, cuisine, stores, consommables, accès ou fréquence.\n\nUne fois accepté, le devis peut être converti en passage, contrat récurrent ou facture afin de garder une chaîne commerciale cohérente.', 'nettoyage-regulier', 'publie', NOW()), (@demo_company_id, 'planifier-passages-nettoyage', 'Planifier les passages de nettoyage sans perdre le suivi client', 'Organisation d’un planning de nettoyage: confirmations, employés assignés, commentaires internes, messages et facturation.', 'Planifier les passages de nettoyage sans perdre le suivi client', 'Le planning devient plus fiable quand les informations commerciales et terrain restent au même endroit.', 'La planification d’un passage de nettoyage ne consiste pas seulement à choisir une date. Il faut assigner les bonnes personnes, garder les commentaires internes, confirmer les horaires au client et suivre l’état de l’intervention.\n\nUn espace client simplifie les échanges: le client retrouve ses devis, factures et messages sans chercher dans ses emails.\n\nPour l’entreprise, les interventions terminées peuvent alimenter les avis, les factures et l’historique client.', 'nettoyage-regulier', 'publie', NOW()) ON DUPLICATE KEY UPDATE company_id=VALUES(company_id), title=VALUES(title), meta_description=VALUES(meta_description), h1=VALUES(h1), excerpt=VALUES(excerpt), body=VALUES(body), service_slug=VALUES(service_slug), status=VALUES(status), updated_at=NOW(); SET @blog_slug_unique_exists = ( SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='blog_posts' AND index_name='slug' ); SET @drop_blog_slug_unique = IF(@blog_slug_unique_exists > 0, 'ALTER TABLE blog_posts DROP INDEX slug', 'SELECT 1'); PREPARE drop_blog_slug_unique_stmt FROM @drop_blog_slug_unique; EXECUTE drop_blog_slug_unique_stmt; DEALLOCATE PREPARE drop_blog_slug_unique_stmt; SET @blog_company_slug_unique_exists = ( SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='blog_posts' AND index_name='uniq_blog_posts_company_slug' ); SET @add_blog_company_slug_unique = IF(@blog_company_slug_unique_exists = 0, 'ALTER TABLE blog_posts ADD UNIQUE KEY uniq_blog_posts_company_slug (company_id, slug)', 'SELECT 1'); PREPARE add_blog_company_slug_unique_stmt FROM @add_blog_company_slug_unique; EXECUTE add_blog_company_slug_unique_stmt; DEALLOCATE PREPARE add_blog_company_slug_unique_stmt; -- --------------------------------------------------------------------- -- Source : sql/2026-06-advanced-cleaning-modules.sql -- --------------------------------------------------------------------- ALTER TABLE users MODIFY role ENUM('client','admin','employee','fiduciaire') NOT NULL DEFAULT 'client'; ALTER TABLE interventions ADD COLUMN IF NOT EXISTS estimated_duration_minutes INT UNSIGNED NULL AFTER ends_at; ALTER TABLE interventions ADD COLUMN IF NOT EXISTS planning_status VARCHAR(40) NOT NULL DEFAULT 'planned' AFTER status; CREATE TABLE IF NOT EXISTS employee_availabilities ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, weekday TINYINT UNSIGNED NOT NULL, starts_at TIME NOT NULL DEFAULT '08:00:00', ends_at TIME NOT NULL DEFAULT '17:00:00', zones_json LONGTEXT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_employee_availabilities_company_user (company_id, user_id, weekday, is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS employee_absences ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, absence_type VARCHAR(40) NOT NULL DEFAULT 'vacation', starts_on DATE NOT NULL, ends_on DATE NOT NULL, starts_at TIME NULL, ends_at TIME NULL, status VARCHAR(30) NOT NULL DEFAULT 'approved', certificate_path VARCHAR(255) NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_employee_absences_period (company_id, user_id, starts_on, ends_on, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS employee_skills ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, service_type VARCHAR(60) NOT NULL, skill_level TINYINT UNSIGNED NOT NULL DEFAULT 3, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_employee_skill (company_id, user_id, service_type), INDEX idx_employee_skills_service (company_id, service_type, is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS service_zones ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, name VARCHAR(140) NOT NULL, postal_codes_json LONGTEXT NULL, cities_json LONGTEXT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_service_zones_company (company_id, is_active) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS client_planning_preferences ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, client_id INT UNSIGNED NOT NULL, preferred_user_id INT UNSIGNED NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_client_planning_pref (company_id, client_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS schedule_recommendations ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, intervention_id INT UNSIGNED NOT NULL, recommended_user_id INT UNSIGNED NULL, score INT NOT NULL DEFAULT 0, reasons_json LONGTEXT NULL, alerts_json LONGTEXT NULL, candidates_json LONGTEXT NULL, generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_schedule_recommendations_intervention (company_id, intervention_id, generated_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS recurring_jobs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, client_id INT UNSIGNED NULL, intervention_id INT UNSIGNED NULL, title VARCHAR(190) NOT NULL, service_type VARCHAR(80) NOT NULL, address VARCHAR(255) NULL, recurrence_type VARCHAR(30) NOT NULL DEFAULT 'weekly', custom_days_json LONGTEXT NULL, duration_minutes INT UNSIGNED NOT NULL DEFAULT 120, assigned_user_id INT UNSIGNED NULL, starts_on DATE NULL, ends_on DATE NULL, paused_until DATE NULL, status VARCHAR(30) NOT NULL DEFAULT 'active', next_run_on DATE NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_recurring_jobs_company (company_id, status, next_run_on) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS fiduciary_users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, fiduciary_company VARCHAR(190) NULL, contact_name VARCHAR(190) NULL, period_from DATE NULL, period_to DATE NULL, status VARCHAR(30) NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_fiduciary_user (company_id, user_id), INDEX idx_fiduciary_users_company (company_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS fiduciary_permissions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, fiduciary_user_id INT UNSIGNED NOT NULL, permission_key VARCHAR(60) NOT NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_fiduciary_permission (company_id, fiduciary_user_id, permission_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS fiduciary_download_logs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, fiduciary_user_id INT UNSIGNED NULL, user_id INT UNSIGNED NULL, export_type VARCHAR(80) NOT NULL, period_month CHAR(7) NULL, meta_json LONGTEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_fiduciary_logs_company (company_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS expenses ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, expense_date DATE NOT NULL, supplier VARCHAR(190) NOT NULL, category VARCHAR(60) NOT NULL DEFAULT 'autres', amount_excl_vat DECIMAL(12,2) NOT NULL DEFAULT 0, vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0, vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0, amount_incl_vat DECIMAL(12,2) NOT NULL DEFAULT 0, payment_method VARCHAR(60) NULL, receipt_path VARCHAR(255) NULL, note TEXT NULL, status VARCHAR(40) NOT NULL DEFAULT 'complete', created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_expenses_company_date (company_id, expense_date, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS expense_attachments ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, expense_id INT UNSIGNED NOT NULL, file_path VARCHAR(255) NOT NULL, original_name VARCHAR(190) NULL, mime_type VARCHAR(120) NULL, size_bytes INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_expense_attachments_expense (company_id, expense_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS accounting_documents ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, document_type VARCHAR(60) NOT NULL DEFAULT 'other', title VARCHAR(190) NOT NULL, period_month CHAR(7) NULL, file_path VARCHAR(255) NULL, original_name VARCHAR(190) NULL, visibility VARCHAR(30) NOT NULL DEFAULT 'fiduciary', notes TEXT NULL, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_accounting_documents_company (company_id, document_type, period_month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS property_managers ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, client_id INT UNSIGNED NULL, name VARCHAR(190) NOT NULL, contact_technical VARCHAR(190) NULL, contact_billing VARCHAR(190) NULL, email VARCHAR(190) NULL, phone VARCHAR(80) NULL, address VARCHAR(255) NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_property_managers_company (company_id, name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS buildings ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, client_id INT UNSIGNED NULL, property_manager_id INT UNSIGNED NULL, name VARCHAR(190) NOT NULL, address VARCHAR(255) NULL, postal_code VARCHAR(20) NULL, city VARCHAR(120) NULL, owner_name VARCHAR(190) NULL, main_contact VARCHAR(190) NULL, entries_count INT UNSIGNED NOT NULL DEFAULT 1, floors_count INT UNSIGNED NOT NULL DEFAULT 1, has_elevator TINYINT(1) NOT NULL DEFAULT 0, has_laundry TINYINT(1) NOT NULL DEFAULT 0, has_bin_room TINYINT(1) NOT NULL DEFAULT 0, has_parking TINYINT(1) NOT NULL DEFAULT 0, has_outdoors TINYINT(1) NOT NULL DEFAULT 0, access_notes TEXT NULL, secure_access_notes TEXT NULL, photos_json LONGTEXT NULL, documents_json LONGTEXT NULL, status VARCHAR(30) NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_buildings_company_city (company_id, city, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS concierge_contracts ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, building_id INT UNSIGNED NULL, client_id INT UNSIGNED NULL, property_manager_id INT UNSIGNED NULL, title VARCHAR(190) NOT NULL, included_services TEXT NULL, frequency VARCHAR(120) NULL, monthly_price DECIMAL(12,2) NOT NULL DEFAULT 0, starts_on DATE NULL, ends_on DATE NULL, renewal_type VARCHAR(80) NULL, conditions_text TEXT NULL, document_path VARCHAR(255) NULL, status VARCHAR(30) NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_concierge_contracts_company (company_id, status, starts_on) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS concierge_checklists ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, building_id INT UNSIGNED NULL, name VARCHAR(190) NOT NULL, items_json LONGTEXT NULL, is_template TINYINT(1) NOT NULL DEFAULT 0, status VARCHAR(30) NOT NULL DEFAULT 'active', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_concierge_checklists_company (company_id, building_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS concierge_rounds ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, name VARCHAR(190) NOT NULL, round_date DATE NULL, recurrence_type VARCHAR(30) NOT NULL DEFAULT 'weekly', assigned_user_id INT UNSIGNED NULL, status VARCHAR(30) NOT NULL DEFAULT 'planned', notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_concierge_rounds_company (company_id, round_date, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS concierge_round_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, round_id INT UNSIGNED NOT NULL, building_id INT UNSIGNED NULL, sort_order INT UNSIGNED NOT NULL DEFAULT 1, estimated_minutes INT UNSIGNED NOT NULL DEFAULT 30, status VARCHAR(30) NOT NULL DEFAULT 'planned', photos_json LONGTEXT NULL, notes TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_concierge_round_items_round (company_id, round_id, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS concierge_tickets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, building_id INT UNSIGNED NULL, client_id INT UNSIGNED NULL, category VARCHAR(60) NOT NULL DEFAULT 'other', priority VARCHAR(20) NOT NULL DEFAULT 'normal', description TEXT NOT NULL, photos_json LONGTEXT NULL, status VARCHAR(40) NOT NULL DEFAULT 'open', assigned_user_id INT UNSIGNED NULL, due_on DATE NULL, billable_out_of_contract TINYINT(1) NOT NULL DEFAULT 0, created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, closed_at DATETIME NULL, INDEX idx_concierge_tickets_company (company_id, status, priority, due_on) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS concierge_ticket_events ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, ticket_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NULL, event_type VARCHAR(60) NOT NULL DEFAULT 'note', body TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, INDEX idx_concierge_ticket_events_ticket (company_id, ticket_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS concierge_reports ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_id INT UNSIGNED NOT NULL, building_id INT UNSIGNED NULL, period_from DATE NULL, period_to DATE NULL, summary TEXT NULL, hours_spent DECIMAL(10,2) NOT NULL DEFAULT 0, out_of_contract_amount DECIMAL(12,2) NOT NULL DEFAULT 0, signature_path VARCHAR(255) NULL, pdf_path VARCHAR(255) NULL, status VARCHAR(30) NOT NULL DEFAULT 'draft', created_by INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, INDEX idx_concierge_reports_company (company_id, building_id, period_from) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Source : sql/2026-06-mobile-api.sql -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS mobile_refresh_tokens ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, company_id INT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL, device_name VARCHAR(190) NULL, device_id VARCHAR(190) NULL, expires_at DATETIME NOT NULL, revoked_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_mobile_refresh_token_hash (token_hash), INDEX idx_mobile_refresh_user (company_id, user_id, revoked_at), INDEX idx_mobile_refresh_device (company_id, user_id, device_id), INDEX idx_mobile_refresh_expiry (expires_at), CONSTRAINT fk_mobile_refresh_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_mobile_refresh_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS push_tokens ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, company_id INT UNSIGNED NOT NULL, platform VARCHAR(30) NOT NULL, token VARCHAR(255) NOT NULL, device_id VARCHAR(190) NULL, device_name VARCHAR(190) NULL, enabled TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP, last_seen_at DATETIME NULL, INDEX idx_push_tokens_user (company_id, user_id, enabled), INDEX idx_push_tokens_device (company_id, user_id, device_id), INDEX idx_push_tokens_token (token(128)), CONSTRAINT fk_push_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_push_tokens_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- Comptes de démonstration (tenant saas-demo) — mot de passe : DemoTest123! -- demo-admin@saas-demo.test (admin) -- demo-client@saas-demo.test (client) -- demo-employe@saas-demo.test (employé) -- ===================================================================== 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) SELECT c.id, 'admin', 'demo-admin@saas-demo.test', '$2b$10$FAytxnx9VhKD/YVi6vbvHeZ5p5zd8QI2E/twYFaokB9OWboC6EMMO', 'Démo', 'Admin', '079 000 00 01', 'active', NULL, 0, 1, NOW() FROM companies c WHERE c.slug = 'saas-demo' ON DUPLICATE KEY UPDATE company_id = VALUES(company_id), role = VALUES(role), password_hash = VALUES(password_hash), status = 'active', updated_at = NOW(); 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) SELECT c.id, 'client', 'demo-client@saas-demo.test', '$2b$10$FAytxnx9VhKD/YVi6vbvHeZ5p5zd8QI2E/twYFaokB9OWboC6EMMO', 'Démo', 'Client', '079 000 00 02', 'active', NULL, 0, 1, NOW() FROM companies c WHERE c.slug = 'saas-demo' ON DUPLICATE KEY UPDATE company_id = VALUES(company_id), role = VALUES(role), password_hash = VALUES(password_hash), status = 'active', updated_at = NOW(); 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) SELECT c.id, 'employee', 'demo-employe@saas-demo.test', '$2b$10$FAytxnx9VhKD/YVi6vbvHeZ5p5zd8QI2E/twYFaokB9OWboC6EMMO', 'Démo', 'Employé', '079 000 00 03', 'active', 32, 0, 1, NOW() FROM companies c WHERE c.slug = 'saas-demo' ON DUPLICATE KEY UPDATE company_id = VALUES(company_id), role = VALUES(role), password_hash = VALUES(password_hash), status = 'active', updated_at = NOW(); INSERT INTO clients_profiles (user_id, company_id, client_type, address, postal_code, city, created_at) SELECT u.id, u.company_id, 'particulier', 'Rue de la Démo 4', '1003', 'Lausanne', NOW() FROM users u WHERE u.email = 'demo-client@saas-demo.test' ON DUPLICATE KEY UPDATE company_id = VALUES(company_id), address = VALUES(address), updated_at = NOW(); SET FOREIGN_KEY_CHECKS = 1;