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;