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

CREATE TABLE IF NOT EXISTS supplier_invoices (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  supplier_id TEXT NOT NULL,
  number TEXT NOT NULL,
  supplier_invoice_number TEXT,
  date TEXT NOT NULL,
  due_date TEXT,
  reference TEXT,
  object TEXT,
  total_ht REAL DEFAULT 0,
  discount REAL DEFAULT 0,
  total_vat REAL DEFAULT 0,
  total_ttc REAL DEFAULT 0,
  amount_paid REAL DEFAULT 0,
  amount_due REAL DEFAULT 0,
  status TEXT 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 TEXT NOT NULL,
  updated_at TEXT,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS supplier_invoice_items (
  id TEXT PRIMARY KEY,
  supplier_invoice_id TEXT NOT NULL,
  product_id TEXT,
  reference TEXT,
  designation TEXT NOT NULL,
  quantity REAL DEFAULT 1,
  unit_price REAL DEFAULT 0,
  discount REAL DEFAULT 0,
  vat_rate REAL DEFAULT 20,
  total_ht REAL DEFAULT 0,
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS supplier_payments (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  supplier_id TEXT NOT NULL,
  supplier_invoice_id TEXT NOT NULL,
  number TEXT NOT NULL,
  date TEXT NOT NULL,
  amount REAL NOT NULL,
  method TEXT DEFAULT 'VIREMENT',
  reference TEXT,
  notes TEXT,
  created_by TEXT,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS purchase_orders (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  supplier_id TEXT NOT NULL,
  number TEXT NOT NULL,
  date TEXT NOT NULL,
  status TEXT DEFAULT 'BROUILLON',
  payment_terms TEXT,
  total_ht REAL DEFAULT 0,
  total_vat REAL DEFAULT 0,
  total_ttc REAL DEFAULT 0,
  notes TEXT,
  purchase_request_id TEXT,
  created_by TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS purchase_order_items (
  id TEXT PRIMARY KEY,
  purchase_order_id TEXT NOT NULL,
  product_id TEXT,
  reference TEXT,
  designation TEXT NOT NULL,
  quantity REAL DEFAULT 1,
  quantity_received REAL DEFAULT 0,
  unit_price REAL DEFAULT 0,
  discount REAL DEFAULT 0,
  vat_rate REAL DEFAULT 20,
  total_ht REAL DEFAULT 0,
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS purchase_receipts (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  purchase_order_id TEXT NOT NULL,
  supplier_id TEXT NOT NULL,
  number TEXT NOT NULL,
  date TEXT NOT NULL,
  notes TEXT,
  created_by TEXT,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS purchase_receipt_items (
  id TEXT PRIMARY KEY,
  purchase_receipt_id TEXT NOT NULL,
  purchase_order_item_id TEXT,
  designation TEXT NOT NULL,
  quantity_ordered REAL DEFAULT 0,
  quantity_received REAL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS documents (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  number TEXT,
  category TEXT NOT NULL,
  title TEXT,
  original_name TEXT,
  path TEXT NOT NULL,
  mime TEXT,
  size INTEGER 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 TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS ocr_jobs (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  document_id TEXT,
  source_path TEXT NOT NULL,
  doc_kind TEXT DEFAULT 'UNKNOWN',
  provider TEXT DEFAULT 'stub',
  status TEXT DEFAULT 'PENDING',
  confidence_avg REAL,
  raw_text TEXT,
  raw_json TEXT,
  created_by TEXT,
  created_at TEXT NOT NULL,
  processed_at TEXT
);

CREATE TABLE IF NOT EXISTS ocr_fields (
  id TEXT PRIMARY KEY,
  ocr_job_id TEXT NOT NULL,
  field_key TEXT NOT NULL,
  extracted_value TEXT,
  corrected_value TEXT,
  confidence REAL,
  source TEXT DEFAULT 'ocr',
  needs_review INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS vehicle_maintenance (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  vehicle_id TEXT NOT NULL,
  maintenance_type TEXT NOT NULL,
  date TEXT NOT NULL,
  mileage REAL,
  amount REAL DEFAULT 0,
  supplier_id TEXT,
  garage_name TEXT,
  next_due_date TEXT,
  next_due_mileage REAL,
  file_path TEXT,
  notes TEXT,
  created_by TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS approval_logs (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  step_name TEXT NOT NULL,
  action TEXT NOT NULL,
  user_id TEXT,
  comment TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS company_rules (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL UNIQUE,
  expense_limit_mad REAL DEFAULT 2000,
  fuel_anomaly_percent REAL DEFAULT 30,
  require_expense_photo INTEGER DEFAULT 1,
  updated_at TEXT
);

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