CREATE TABLE IF NOT EXISTS runs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  status ENUM('running','ok','error') NOT NULL DEFAULT 'running',
  root_path TEXT NULL,
  total_docs INT NOT NULL DEFAULT 0,
  processed_docs INT NOT NULL DEFAULT 0,
  notes TEXT NULL
);

CREATE TABLE IF NOT EXISTS documents (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  run_id BIGINT NULL,
  file_path TEXT NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_hash CHAR(64) NOT NULL,
  pages INT NULL,
  status ENUM('new','processing','done','error') NOT NULL DEFAULT 'new',
  error_message TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY uq_hash (file_hash),
  KEY idx_run (run_id)
);

CREATE TABLE IF NOT EXISTS extractions (
  document_id BIGINT PRIMARY KEY,
  kommission VARCHAR(64) NULL,
  datum DATE NULL,
  kd_nr VARCHAR(64) NULL,
  auftrag VARCHAR(128) NULL,
  auftrag_nr VARCHAR(64) NULL,
  auftrag_nr2 VARCHAR(64) NULL,
  auftraggeber VARCHAR(255) NULL,
  confidence DECIMAL(5,4) NULL,
  raw_json JSON NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS checkboxes (
  document_id BIGINT PRIMARY KEY,
  fahrzeugpauschale TINYINT(1) NOT NULL DEFAULT 0,
  schmutzzulage TINYINT(1) NOT NULL DEFAULT 0,
  entsorgung TINYINT(1) NOT NULL DEFAULT 0,
  materialfahrt TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS employees (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  document_id BIGINT NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_doc_name (document_id, name),
  FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS employee_days (
  employee_id BIGINT NOT NULL,
  datum DATE NULL,
  gesamt VARCHAR(16) NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (employee_id, datum),
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS time_blocks (
  employee_id BIGINT NOT NULL,
  datum DATE NULL,
  block_type ENUM('fahrzeit','arbeitszeit') NOT NULL,
  von VARCHAR(8) NULL,
  bis VARCHAR(8) NULL,
  gesamt VARCHAR(16) NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (employee_id, datum, block_type),
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS http_debug (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  document_id BIGINT NULL,
  endpoint VARCHAR(255) NOT NULL,
  http_status INT NULL,
  request_json MEDIUMTEXT NULL,
  response_text MEDIUMTEXT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_doc (document_id)
);
