

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 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 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;

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());
