-- TIJARA FULL SCHEMA MySQL — installation from zero
-- Importer ce fichier UNE FOIS dans une base VIDE (phpMyAdmin)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

-- ===== CORE =====
-- TIJARA MySQL schema (Namecheap)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS companies (
  id VARCHAR(32) PRIMARY KEY,
  trade_name VARCHAR(190) NOT NULL,
  legal_name VARCHAR(190) NOT NULL,
  ice VARCHAR(64) NULL,
  if_number VARCHAR(64) NULL,
  rc VARCHAR(64) NULL,
  address TEXT NULL,
  city VARCHAR(100) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  bank_name VARCHAR(190) NULL,
  rib VARCHAR(100) NULL,
  payment_terms VARCHAR(100) DEFAULT '30 jours',
  default_vat_rate DECIMAL(5,2) DEFAULT 20,
  currency VARCHAR(10) DEFAULT 'MAD',
  legal_mentions TEXT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(32) PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  role VARCHAR(30) DEFAULT 'ADMIN',
  active TINYINT(1) DEFAULT 1,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS customers (
  id VARCHAR(32) PRIMARY KEY,
  type VARCHAR(30) DEFAULT 'ENTREPRISE',
  first_name VARCHAR(100) NULL,
  last_name VARCHAR(100) NULL,
  company_name VARCHAR(190) NULL,
  ice VARCHAR(64) NULL,
  address TEXT NULL,
  city VARCHAR(100) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  payment_terms VARCHAR(100) DEFAULT '30 jours',
  archived TINYINT(1) DEFAULT 0,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id),
  INDEX (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS suppliers (
  id VARCHAR(32) PRIMARY KEY,
  company_name VARCHAR(190) NOT NULL,
  ice VARCHAR(64) NULL,
  city VARCHAR(100) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  archived TINYINT(1) DEFAULT 0,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS categories (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  UNIQUE KEY uq_cat (company_id, name),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id VARCHAR(32) PRIMARY KEY,
  reference VARCHAR(64) NOT NULL,
  name VARCHAR(190) NOT NULL,
  unit VARCHAR(30) DEFAULT 'unité',
  purchase_price DECIMAL(12,2) DEFAULT 0,
  sale_price DECIMAL(12,2) NOT NULL,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  stock DECIMAL(12,2) DEFAULT 0,
  alert_threshold DECIMAL(12,2) NULL,
  track_stock TINYINT(1) DEFAULT 1,
  is_service TINYINT(1) DEFAULT 0,
  active TINYINT(1) DEFAULT 1,
  category_id VARCHAR(32) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_ref (company_id, reference),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS quotes (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  valid_until DATE NULL,
  object VARCHAR(255) NULL,
  status VARCHAR(30) DEFAULT 'BROUILLON',
  payment_terms VARCHAR(100) NULL,
  total_ht DECIMAL(12,2) DEFAULT 0,
  total_vat DECIMAL(12,2) DEFAULT 0,
  total_ttc DECIMAL(12,2) DEFAULT 0,
  notes TEXT NULL,
  signature_data MEDIUMTEXT NULL,
  signed_at DATETIME NULL,
  signed_by VARCHAR(32) NULL,
  signed_ip VARCHAR(64) NULL,
  customer_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_q (company_id, number),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS quote_items (
  id VARCHAR(32) PRIMARY KEY,
  quote_id VARCHAR(32) NOT NULL,
  product_id VARCHAR(32) NULL,
  reference VARCHAR(64) NULL,
  designation VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,2) DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  discount DECIMAL(12,2) DEFAULT 0,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  total_ht DECIMAL(12,2) NOT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  status VARCHAR(30) DEFAULT 'BROUILLON',
  total_ht DECIMAL(12,2) DEFAULT 0,
  total_vat DECIMAL(12,2) DEFAULT 0,
  total_ttc DECIMAL(12,2) DEFAULT 0,
  customer_id VARCHAR(32) NOT NULL,
  quote_id VARCHAR(32) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_o (company_id, number),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_items (
  id VARCHAR(32) PRIMARY KEY,
  order_id VARCHAR(32) NOT NULL,
  product_id VARCHAR(32) NULL,
  reference VARCHAR(64) NULL,
  designation VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,2) DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  discount DECIMAL(12,2) DEFAULT 0,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  total_ht DECIMAL(12,2) NOT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS delivery_notes (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  status VARCHAR(30) DEFAULT 'PREPARE',
  delivery_address TEXT NULL,
  receiver_name VARCHAR(190) NULL,
  signature_data MEDIUMTEXT NULL,
  signed_at DATETIME NULL,
  signed_by VARCHAR(32) NULL,
  signed_ip VARCHAR(64) NULL,
  customer_id VARCHAR(32) NOT NULL,
  order_id VARCHAR(32) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_bl (company_id, number),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS delivery_note_items (
  id VARCHAR(32) PRIMARY KEY,
  delivery_note_id VARCHAR(32) NOT NULL,
  product_id VARCHAR(32) NULL,
  designation VARCHAR(255) NOT NULL,
  quantity_ordered DECIMAL(12,2) DEFAULT 0,
  quantity_delivered DECIMAL(12,2) DEFAULT 0,
  FOREIGN KEY (delivery_note_id) REFERENCES delivery_notes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  due_date DATE NULL,
  status VARCHAR(30) DEFAULT 'EMISE',
  payment_terms VARCHAR(100) NULL,
  total_ht DECIMAL(12,2) DEFAULT 0,
  total_vat DECIMAL(12,2) DEFAULT 0,
  total_ttc DECIMAL(12,2) DEFAULT 0,
  amount_paid DECIMAL(12,2) DEFAULT 0,
  amount_due DECIMAL(12,2) DEFAULT 0,
  notes TEXT NULL,
  last_reminder_at DATETIME NULL,
  customer_id VARCHAR(32) NOT NULL,
  quote_id VARCHAR(32) NULL,
  order_id VARCHAR(32) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_i (company_id, number),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoice_items (
  id VARCHAR(32) PRIMARY KEY,
  invoice_id VARCHAR(32) NOT NULL,
  product_id VARCHAR(32) NULL,
  reference VARCHAR(64) NULL,
  designation VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,2) DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  discount DECIMAL(12,2) DEFAULT 0,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  total_ht DECIMAL(12,2) NOT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS credit_notes (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  reason VARCHAR(255) NULL,
  total_ht DECIMAL(12,2) DEFAULT 0,
  total_vat DECIMAL(12,2) DEFAULT 0,
  total_ttc DECIMAL(12,2) DEFAULT 0,
  status VARCHAR(30) DEFAULT 'EMIS',
  invoice_id VARCHAR(32) NULL,
  customer_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_av (company_id, number),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  method VARCHAR(30) NOT NULL,
  reference VARCHAR(100) NULL,
  invoice_id VARCHAR(32) NOT NULL,
  customer_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_p (company_id, number),
  FOREIGN KEY (invoice_id) REFERENCES invoices(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expenses (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  description VARCHAR(255) NOT NULL,
  amount_ht DECIMAL(12,2) NOT NULL,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  amount_vat DECIMAL(12,2) NOT NULL,
  amount_ttc DECIMAL(12,2) NOT NULL,
  method VARCHAR(30) NULL,
  category VARCHAR(100) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_e (company_id, number),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stock_movements (
  id VARCHAR(32) PRIMARY KEY,
  type VARCHAR(30) NOT NULL,
  quantity DECIMAL(12,2) NOT NULL,
  reason VARCHAR(255) NULL,
  product_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cash_accounts (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  initial_balance DECIMAL(12,2) DEFAULT 0,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cash_transactions (
  id VARCHAR(32) PRIMARY KEY,
  type VARCHAR(20) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  label VARCHAR(255) NOT NULL,
  date DATE NOT NULL,
  cash_account_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (cash_account_id) REFERENCES cash_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bank_accounts (
  id VARCHAR(32) PRIMARY KEY,
  bank_name VARCHAR(100) NOT NULL,
  rib VARCHAR(100) NULL,
  initial_balance DECIMAL(12,2) DEFAULT 0,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bank_transactions (
  id VARCHAR(32) PRIMARY KEY,
  type VARCHAR(30) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  label VARCHAR(255) NOT NULL,
  date DATE NOT NULL,
  bank_account_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS branches (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  city VARCHAR(100) NULL,
  company_id VARCHAR(32) NOT NULL,
  active TINYINT(1) DEFAULT 1,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscriptions (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  frequency VARCHAR(30) NOT NULL,
  amount_ht DECIMAL(12,2) NOT NULL,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  start_date DATE NOT NULL,
  next_billing DATE NOT NULL,
  active TINYINT(1) DEFAULT 1,
  customer_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
  id VARCHAR(32) PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  type VARCHAR(30) DEFAULT 'INFO',
  is_read TINYINT(1) DEFAULT 0,
  link VARCHAR(255) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS document_counters (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  type VARCHAR(30) NOT NULL,
  year INT NOT NULL,
  value INT DEFAULT 0,
  UNIQUE KEY uq_c (company_id, type, year),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS activity_logs (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  user_id VARCHAR(32) NULL,
  action VARCHAR(255) NOT NULL,
  entity VARCHAR(50) NULL,
  entity_id VARCHAR(32) NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS attachments (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  entity_type VARCHAR(50) NOT NULL,
  entity_id VARCHAR(32) NOT NULL,
  filename VARCHAR(255) NOT NULL,
  path VARCHAR(255) NOT NULL,
  mime VARCHAR(100) NULL,
  size INT DEFAULT 0,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id),
  INDEX (company_id, entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_companies (
  user_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  role VARCHAR(30) DEFAULT 'ADMIN',
  PRIMARY KEY (user_id, company_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS credit_note_items (
  id VARCHAR(32) PRIMARY KEY,
  credit_note_id VARCHAR(32) NOT NULL,
  product_id VARCHAR(32) NULL,
  reference VARCHAR(64) NULL,
  designation VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,2) DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  discount DECIMAL(12,2) DEFAULT 0,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  total_ht DECIMAL(12,2) NOT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (credit_note_id) REFERENCES credit_notes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

-- ===== FLEET =====
-- Module Flotte / Missions (MySQL) — additive
CREATE TABLE IF NOT EXISTS drivers (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  user_id VARCHAR(32) NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  cin VARCHAR(50) NULL,
  address TEXT NULL,
  license_number VARCHAR(100) NULL,
  license_category VARCHAR(50) NULL,
  license_expiry DATE NULL,
  hire_date DATE NULL,
  status VARCHAR(30) DEFAULT 'DISPONIBLE',
  notes TEXT NULL,
  archived TINYINT(1) DEFAULT 0,
  created_at DATETIME NOT NULL,
  INDEX (company_id),
  INDEX (user_id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicles (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  registration VARCHAR(50) NOT NULL,
  brand VARCHAR(100) NULL,
  model VARCHAR(100) NULL,
  year INT NULL,
  fuel_type VARCHAR(30) DEFAULT 'DIESEL',
  mileage DECIMAL(12,2) DEFAULT 0,
  driver_id VARCHAR(32) NULL,
  status VARCHAR(30) DEFAULT 'DISPONIBLE',
  notes TEXT NULL,
  archived TINYINT(1) DEFAULT 0,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_reg (company_id, registration),
  INDEX (company_id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mission_orders (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  number VARCHAR(40) NOT NULL,
  driver_id VARCHAR(32) NULL,
  vehicle_id VARCHAR(32) NULL,
  customer_id VARCHAR(32) NULL,
  destination VARCHAR(255) NULL,
  departure_place VARCHAR(255) NULL,
  departure_at DATETIME NULL,
  return_at DATETIME NULL,
  purpose VARCHAR(255) NULL,
  instructions TEXT NULL,
  advance_amount DECIMAL(12,2) DEFAULT 0,
  status VARCHAR(30) DEFAULT 'BROUILLON',
  km_start DECIMAL(12,2) NULL,
  km_end DECIMAL(12,2) NULL,
  km_total DECIMAL(12,2) NULL,
  start_at DATETIME NULL,
  end_at DATETIME NULL,
  start_photo VARCHAR(255) NULL,
  end_photo VARCHAR(255) NULL,
  start_lat DECIMAL(10,7) NULL,
  start_lng DECIMAL(10,7) NULL,
  end_lat DECIMAL(10,7) NULL,
  end_lng DECIMAL(10,7) NULL,
  start_notes TEXT NULL,
  end_notes TEXT NULL,
  validated_by VARCHAR(32) NULL,
  controlled_by VARCHAR(32) NULL,
  closed_by VARCHAR(32) NULL,
  created_by VARCHAR(32) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_om (company_id, number),
  INDEX (company_id),
  INDEX (driver_id),
  INDEX (status),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mission_expenses (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  mission_id VARCHAR(32) NOT NULL,
  driver_id VARCHAR(32) NOT NULL,
  vehicle_id VARCHAR(32) NULL,
  expense_type VARCHAR(50) NOT NULL,
  expense_date DATE NOT NULL,
  expense_time VARCHAR(10) NULL,
  amount DECIMAL(12,2) NOT NULL,
  description TEXT NULL,
  status VARCHAR(30) DEFAULT 'BROUILLON',
  reject_reason TEXT NULL,
  photo_path VARCHAR(255) NULL,
  station_name VARCHAR(190) NULL,
  fuel_type VARCHAR(30) NULL,
  quantity_liters DECIMAL(12,3) NULL,
  price_per_liter DECIMAL(12,3) NULL,
  ticket_number VARCHAR(100) NULL,
  mileage_at_fill DECIMAL(12,2) NULL,
  client_uuid VARCHAR(64) NULL,
  synced_at DATETIME NULL,
  ocr_raw TEXT NULL,
  ocr_status VARCHAR(30) DEFAULT 'PENDING',
  validated_by VARCHAR(32) NULL,
  validated_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX (company_id),
  INDEX (mission_id),
  INDEX (driver_id),
  INDEX (status),
  INDEX (client_uuid),
  FOREIGN KEY (company_id) REFERENCES companies(id),
  FOREIGN KEY (mission_id) REFERENCES mission_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ===== ACHATS / OCR / GED =====
-- Extension Achats / OCR / GED / Maintenance (SQLite) — additive
-- Ne pas DROP de tables existantes.

CREATE TABLE IF NOT EXISTS supplier_invoices (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  supplier_id VARCHAR(255) NOT NULL,
  number VARCHAR(255) NOT NULL,
  supplier_invoice_number TEXT,
  date VARCHAR(255) NOT NULL,
  due_date TEXT,
  reference TEXT,
  object TEXT,
  total_ht DOUBLE DEFAULT 0,
  discount DOUBLE DEFAULT 0,
  total_vat DOUBLE DEFAULT 0,
  total_ttc DOUBLE DEFAULT 0,
  amount_paid DOUBLE DEFAULT 0,
  amount_due DOUBLE DEFAULT 0,
  status VARCHAR(100) DEFAULT 'BROUILLON',
  notes TEXT,
  file_path TEXT,
  file_hash TEXT,
  purchase_order_id TEXT,
  mission_id TEXT,
  vehicle_id TEXT,
  driver_id TEXT,
  ocr_job_id TEXT,
  created_by TEXT,
  validated_by TEXT,
  validated_at TEXT,
  created_at VARCHAR(255) NOT NULL,
  updated_at TEXT,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS supplier_invoice_items (
  id VARCHAR(32) PRIMARY KEY,
  supplier_invoice_id VARCHAR(255) NOT NULL,
  product_id TEXT,
  reference TEXT,
  designation VARCHAR(255) NOT NULL,
  quantity DOUBLE DEFAULT 1,
  unit_price DOUBLE DEFAULT 0,
  discount DOUBLE DEFAULT 0,
  vat_rate DOUBLE DEFAULT 20,
  total_ht DOUBLE DEFAULT 0,
  sort_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS supplier_payments (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  supplier_id VARCHAR(255) NOT NULL,
  supplier_invoice_id VARCHAR(255) NOT NULL,
  number VARCHAR(255) NOT NULL,
  date VARCHAR(255) NOT NULL,
  amount REAL NOT NULL,
  method VARCHAR(100) DEFAULT 'VIREMENT',
  reference TEXT,
  notes TEXT,
  created_by TEXT,
  created_at VARCHAR(255) NOT NULL,
  UNIQUE (company_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_orders (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  supplier_id VARCHAR(255) NOT NULL,
  number VARCHAR(255) NOT NULL,
  date VARCHAR(255) NOT NULL,
  status VARCHAR(100) DEFAULT 'BROUILLON',
  payment_terms TEXT,
  total_ht DOUBLE DEFAULT 0,
  total_vat DOUBLE DEFAULT 0,
  total_ttc DOUBLE DEFAULT 0,
  notes TEXT,
  purchase_request_id TEXT,
  created_by TEXT,
  created_at VARCHAR(255) NOT NULL,
  updated_at TEXT,
  UNIQUE (company_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_order_items (
  id VARCHAR(32) PRIMARY KEY,
  purchase_order_id VARCHAR(255) NOT NULL,
  product_id TEXT,
  reference TEXT,
  designation VARCHAR(255) NOT NULL,
  quantity DOUBLE DEFAULT 1,
  quantity_received DOUBLE DEFAULT 0,
  unit_price DOUBLE DEFAULT 0,
  discount DOUBLE DEFAULT 0,
  vat_rate DOUBLE DEFAULT 20,
  total_ht DOUBLE DEFAULT 0,
  sort_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_receipts (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  purchase_order_id VARCHAR(255) NOT NULL,
  supplier_id VARCHAR(255) NOT NULL,
  number VARCHAR(255) NOT NULL,
  date VARCHAR(255) NOT NULL,
  notes TEXT,
  created_by TEXT,
  created_at VARCHAR(255) NOT NULL,
  UNIQUE (company_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_receipt_items (
  id VARCHAR(32) PRIMARY KEY,
  purchase_receipt_id VARCHAR(255) NOT NULL,
  purchase_order_item_id TEXT,
  designation VARCHAR(255) NOT NULL,
  quantity_ordered DOUBLE DEFAULT 0,
  quantity_received DOUBLE DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS documents (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  number TEXT,
  category VARCHAR(255) NOT NULL,
  title TEXT,
  original_name TEXT,
  path VARCHAR(255) NOT NULL,
  mime TEXT,
  size INT DEFAULT 0,
  file_hash TEXT,
  doc_date TEXT,
  tags TEXT,
  comment TEXT,
  supplier_id TEXT,
  customer_id TEXT,
  driver_id TEXT,
  vehicle_id TEXT,
  mission_id TEXT,
  invoice_id TEXT,
  supplier_invoice_id TEXT,
  expense_id TEXT,
  mission_expense_id TEXT,
  purchase_order_id TEXT,
  uploaded_by TEXT,
  created_at VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ocr_jobs (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  document_id TEXT,
  source_path VARCHAR(255) NOT NULL,
  doc_kind VARCHAR(100) DEFAULT 'UNKNOWN',
  provider VARCHAR(100) DEFAULT 'stub',
  status VARCHAR(100) DEFAULT 'PENDING',
  confidence_avg DOUBLE,
  raw_text TEXT,
  raw_json TEXT,
  created_by TEXT,
  created_at VARCHAR(255) NOT NULL,
  processed_at TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ocr_fields (
  id VARCHAR(32) PRIMARY KEY,
  ocr_job_id VARCHAR(255) NOT NULL,
  field_key VARCHAR(255) NOT NULL,
  extracted_value TEXT,
  corrected_value TEXT,
  confidence DOUBLE,
  source VARCHAR(100) DEFAULT 'ocr',
  needs_review INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_maintenance (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  vehicle_id VARCHAR(255) NOT NULL,
  maintenance_type VARCHAR(255) NOT NULL,
  date VARCHAR(255) NOT NULL,
  mileage DOUBLE,
  amount DOUBLE DEFAULT 0,
  supplier_id TEXT,
  garage_name TEXT,
  next_due_date TEXT,
  next_due_mileage DOUBLE,
  file_path TEXT,
  notes TEXT,
  created_by TEXT,
  created_at VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS approval_logs (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  entity_type VARCHAR(255) NOT NULL,
  entity_id VARCHAR(255) NOT NULL,
  step_name VARCHAR(255) NOT NULL,
  action VARCHAR(255) NOT NULL,
  user_id TEXT,
  comment TEXT,
  created_at VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS company_rules (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL UNIQUE,
  expense_limit_mad DOUBLE DEFAULT 2000,
  fuel_anomaly_percent DOUBLE DEFAULT 30,
  require_expense_photo INT DEFAULT 1,
  updated_at TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX IF NOT EXISTS idx_supinv_company ON supplier_invoices(company_id);

CREATE INDEX IF NOT EXISTS idx_supinv_supplier ON supplier_invoices(supplier_id);

CREATE INDEX IF NOT EXISTS idx_supinv_due ON supplier_invoices(due_date);

CREATE INDEX IF NOT EXISTS idx_suppay_invoice ON supplier_payments(supplier_invoice_id);

CREATE INDEX IF NOT EXISTS idx_po_company ON purchase_orders(company_id);

CREATE INDEX IF NOT EXISTS idx_docs_company ON documents(company_id);

CREATE INDEX IF NOT EXISTS idx_docs_cat ON documents(category);

CREATE INDEX IF NOT EXISTS idx_ocr_company ON ocr_jobs(company_id);

CREATE INDEX IF NOT EXISTS idx_vmaint_vehicle ON vehicle_maintenance(vehicle_id);

;

-- ===== MODULES ERP / SAAS =====
-- ERP modulaire / SaaS / exercices (SQLite) — additive
CREATE TABLE IF NOT EXISTS company_modules (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  module_key VARCHAR(255) NOT NULL,
  enabled INT DEFAULT 1,
  updated_at TEXT,
  UNIQUE (company_id, module_key)
);

CREATE TABLE IF NOT EXISTS saas_plans (
  id VARCHAR(32) PRIMARY KEY,
  code VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  max_users INT DEFAULT 5,
  max_companies INT DEFAULT 1,
  max_storage_mb INT DEFAULT 1024,
  modules_json TEXT,
  price_monthly DOUBLE DEFAULT 0,
  active INT DEFAULT 1,
  created_at VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS company_subscriptions (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  plan_id VARCHAR(255) NOT NULL,
  status VARCHAR(100) DEFAULT 'ACTIVE',
  starts_at TEXT,
  ends_at TEXT,
  created_at VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_branches (
  user_id VARCHAR(255) NOT NULL,
  branch_id VARCHAR(255) NOT NULL,
  company_id VARCHAR(255) NOT NULL,
  PRIMARY KEY (user_id, branch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fiscal_years (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  label VARCHAR(255) NOT NULL,
  year INTEGER NOT NULL,
  starts_on VARCHAR(255) NOT NULL,
  ends_on VARCHAR(255) NOT NULL,
  is_current INT DEFAULT 0,
  closed INT DEFAULT 0,
  UNIQUE (company_id, year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS numbering_rules (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  doc_type VARCHAR(255) NOT NULL,
  prefix VARCHAR(255) NOT NULL,
  pad INT DEFAULT 5,
  include_year INT DEFAULT 1,
  UNIQUE (company_id, doc_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contracts (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  number VARCHAR(255) NOT NULL,
  contract_type VARCHAR(100) DEFAULT 'AUTRE',
  title TEXT,
  party_type VARCHAR(100) DEFAULT 'CLIENT',
  customer_id TEXT,
  supplier_id TEXT,
  driver_id TEXT,
  vehicle_id TEXT,
  start_date TEXT,
  end_date TEXT,
  amount DOUBLE DEFAULT 0,
  frequency TEXT,
  auto_renew INT DEFAULT 0,
  status VARCHAR(100) DEFAULT 'ACTIF',
  document_path TEXT,
  notes TEXT,
  created_by TEXT,
  created_at VARCHAR(255) NOT NULL,
  UNIQUE (company_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_queries (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  user_id VARCHAR(255) NOT NULL,
  question VARCHAR(255) NOT NULL,
  answer TEXT,
  created_at VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS gps_integrations (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(64) NOT NULL UNIQUE,
  provider VARCHAR(100) DEFAULT 'NONE',
  api_base_url TEXT,
  api_key TEXT,
  enabled INT DEFAULT 0,
  notes TEXT,
  updated_at TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX IF NOT EXISTS idx_company_modules_co ON company_modules(company_id);

CREATE INDEX IF NOT EXISTS idx_contracts_co ON contracts(company_id);

CREATE INDEX IF NOT EXISTS idx_fiscal_years_co ON fiscal_years(company_id);

SET FOREIGN_KEY_CHECKS=1;

-- ===== LEGAL RULES =====
-- Legal Rules Engine (paramétrable, versionné) — MySQL additive
CREATE TABLE IF NOT EXISTS legal_rules (
  id VARCHAR(32) PRIMARY KEY,
  country VARCHAR(8) NOT NULL DEFAULT 'MA',
  category VARCHAR(64) NOT NULL,
  code VARCHAR(64) NOT NULL,
  name VARCHAR(255) NOT NULL,
  value DOUBLE NOT NULL DEFAULT 0,
  unit VARCHAR(32) DEFAULT 'PERCENT',
  min_value DOUBLE NULL,
  max_value DOUBLE NULL,
  effective_from VARCHAR(32) NOT NULL,
  effective_to VARCHAR(32) NULL,
  version VARCHAR(32) NOT NULL DEFAULT '1',
  source TEXT NULL,
  notes TEXT NULL,
  status VARCHAR(32) DEFAULT 'ACTIVE',
  active INT DEFAULT 1,
  created_at VARCHAR(64) NOT NULL,
  updated_at VARCHAR(64) NULL,
  UNIQUE KEY uq_legal_rule (country, category, code, version, effective_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS legal_rule_audit (
  id VARCHAR(32) PRIMARY KEY,
  rule_id VARCHAR(255) NOT NULL,
  user_id VARCHAR(255) NULL,
  action VARCHAR(64) NOT NULL,
  old_value TEXT NULL,
  new_value TEXT NULL,
  reason TEXT NULL,
  created_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_legal_rules_lookup ON legal_rules(country, category, code, active);
CREATE INDEX idx_legal_rules_dates ON legal_rules(effective_from, effective_to);

-- ===== RH =====
-- Module RH (optionnel) — MySQL additive
CREATE TABLE IF NOT EXISTS employees (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  branch_id VARCHAR(255) NULL,
  user_id VARCHAR(255) NULL,
  matricule VARCHAR(64) NULL,
  first_name VARCHAR(120) NOT NULL,
  last_name VARCHAR(120) NOT NULL,
  cin VARCHAR(64) NULL,
  birth_date VARCHAR(32) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  address TEXT NULL,
  city VARCHAR(100) NULL,
  marital_status VARCHAR(64) NULL,
  dependents INT DEFAULT 0,
  hire_date VARCHAR(32) NULL,
  exit_date VARCHAR(32) NULL,
  job_title VARCHAR(190) NULL,
  department VARCHAR(190) NULL,
  contract_type VARCHAR(32) DEFAULT 'CDI',
  base_salary DOUBLE DEFAULT 0,
  rib VARCHAR(64) NULL,
  bank_name VARCHAR(120) NULL,
  cnss_number VARCHAR(64) NULL,
  status VARCHAR(32) DEFAULT 'ACTIF',
  notes TEXT NULL,
  photo_path TEXT NULL,
  archived INT DEFAULT 0,
  created_at VARCHAR(64) NOT NULL,
  updated_at VARCHAR(64) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS employment_contracts (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  number VARCHAR(64) NOT NULL,
  contract_type VARCHAR(32) DEFAULT 'CDI',
  start_date VARCHAR(32) NULL,
  end_date VARCHAR(32) NULL,
  trial_end_date VARCHAR(32) NULL,
  job_title VARCHAR(190) NULL,
  base_salary DOUBLE DEFAULT 0,
  work_schedule TEXT NULL,
  document_path TEXT NULL,
  status VARCHAR(32) DEFAULT 'ACTIF',
  notes TEXT NULL,
  created_at VARCHAR(64) NOT NULL,
  UNIQUE KEY uq_emp_contract (company_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leave_types (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  code VARCHAR(64) NOT NULL,
  name VARCHAR(190) NOT NULL,
  paid INT DEFAULT 1,
  active INT DEFAULT 1,
  UNIQUE KEY uq_leave_type (company_id, code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leave_requests (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  leave_type_id VARCHAR(255) NULL,
  start_date VARCHAR(32) NOT NULL,
  end_date VARCHAR(32) NOT NULL,
  days DOUBLE DEFAULT 0,
  reason TEXT NULL,
  status VARCHAR(32) DEFAULT 'BROUILLON',
  decided_by VARCHAR(255) NULL,
  decided_at VARCHAR(64) NULL,
  decision_note TEXT NULL,
  created_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attendance_records (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  work_date VARCHAR(32) NOT NULL,
  status VARCHAR(32) DEFAULT 'PRESENT',
  hours DOUBLE DEFAULT 8,
  overtime_hours DOUBLE DEFAULT 0,
  note TEXT NULL,
  created_at VARCHAR(64) NOT NULL,
  UNIQUE KEY uq_attendance (company_id, employee_id, work_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_employees_co ON employees(company_id);
CREATE INDEX idx_emp_contracts_co ON employment_contracts(company_id);
CREATE INDEX idx_leave_req_co ON leave_requests(company_id);

-- ===== PAYROLL =====
-- Paie / CNSS / IR / avances / notes frais / virements — MySQL additive
CREATE TABLE IF NOT EXISTS payroll_periods (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  label VARCHAR(190) NOT NULL,
  year INT NOT NULL,
  month INT NOT NULL,
  starts_on VARCHAR(32) NOT NULL,
  ends_on VARCHAR(32) NOT NULL,
  status VARCHAR(32) DEFAULT 'BROUILLON',
  rule_snapshot_json TEXT NULL,
  closed_at VARCHAR(64) NULL,
  created_by VARCHAR(255) NULL,
  created_at VARCHAR(64) NOT NULL,
  UNIQUE KEY uq_payroll_period (company_id, year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payslips (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  period_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  number VARCHAR(64) NOT NULL,
  base_salary DOUBLE DEFAULT 0,
  bonuses DOUBLE DEFAULT 0,
  overtime_amount DOUBLE DEFAULT 0,
  deductions DOUBLE DEFAULT 0,
  advance_deduction DOUBLE DEFAULT 0,
  cnss_employee DOUBLE DEFAULT 0,
  cnss_employer DOUBLE DEFAULT 0,
  amo_employee DOUBLE DEFAULT 0,
  amo_employer DOUBLE DEFAULT 0,
  taxable_base DOUBLE DEFAULT 0,
  ir_amount DOUBLE DEFAULT 0,
  net_pay DOUBLE DEFAULT 0,
  employer_cost DOUBLE DEFAULT 0,
  rule_version VARCHAR(64) NULL,
  status VARCHAR(32) DEFAULT 'CALCULEE',
  notes TEXT NULL,
  created_at VARCHAR(64) NOT NULL,
  UNIQUE KEY uq_payslip_number (company_id, number),
  UNIQUE KEY uq_payslip_emp_period (period_id, employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payslip_lines (
  id VARCHAR(32) PRIMARY KEY,
  payslip_id VARCHAR(255) NOT NULL,
  company_id VARCHAR(255) NOT NULL,
  line_type VARCHAR(64) NOT NULL,
  code VARCHAR(64) NULL,
  label VARCHAR(255) NOT NULL,
  amount DOUBLE DEFAULT 0,
  sort_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS salary_advances (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  advance_type VARCHAR(32) DEFAULT 'AVANCE',
  amount DOUBLE NOT NULL,
  remaining DOUBLE NOT NULL,
  granted_on VARCHAR(32) NOT NULL,
  monthly_deduction DOUBLE DEFAULT 0,
  status VARCHAR(32) DEFAULT 'OUVERT',
  notes TEXT NULL,
  created_by VARCHAR(255) NULL,
  created_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cnss_declarations (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  period_id VARCHAR(255) NULL,
  label VARCHAR(190) NOT NULL,
  year INT NOT NULL,
  month INT NOT NULL,
  status VARCHAR(32) DEFAULT 'PREPARATION',
  employee_count INT DEFAULT 0,
  total_salaries DOUBLE DEFAULT 0,
  total_cnss_employee DOUBLE DEFAULT 0,
  total_cnss_employer DOUBLE DEFAULT 0,
  total_amo_employee DOUBLE DEFAULT 0,
  total_amo_employer DOUBLE DEFAULT 0,
  export_path TEXT NULL,
  archive_note TEXT NULL,
  created_at VARCHAR(64) NOT NULL,
  validated_at VARCHAR(64) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expense_claims (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  number VARCHAR(64) NOT NULL,
  claim_date VARCHAR(32) NOT NULL,
  category VARCHAR(64) DEFAULT 'AUTRE',
  amount DOUBLE NOT NULL,
  description TEXT NULL,
  attachment_path TEXT NULL,
  status VARCHAR(32) DEFAULT 'BROUILLON',
  decided_by VARCHAR(255) NULL,
  decided_at VARCHAR(64) NULL,
  decision_note TEXT NULL,
  created_at VARCHAR(64) NOT NULL,
  UNIQUE KEY uq_expense_claim (company_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS salary_transfers (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  period_id VARCHAR(255) NOT NULL,
  employee_id VARCHAR(255) NOT NULL,
  bank_name VARCHAR(190) NULL,
  rib VARCHAR(64) NULL,
  amount DOUBLE NOT NULL,
  status VARCHAR(32) DEFAULT 'A_PAYER',
  created_at VARCHAR(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_payslips_period ON payslips(period_id);
CREATE INDEX idx_salary_adv_co ON salary_advances(company_id);
CREATE INDEX idx_expense_claims_co ON expense_claims(company_id);