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;