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

;