CREATE TABLE IF NOT EXISTS source_documents (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  sha256 CHAR(64) NOT NULL,
  file_size_bytes BIGINT NOT NULL,
  page_count_estimated INT NOT NULL DEFAULT 0,
  filename_date DATE NULL,
  filename_title VARCHAR(255) NULL,
  text_layer_detected TINYINT(1) NOT NULL DEFAULT 0,
  extraction_status VARCHAR(30) NOT NULL DEFAULT 'parsed_filename',
  raw_text_excerpt TEXT NULL,
  last_error TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_source_documents_filename (filename),
  UNIQUE KEY uniq_source_documents_sha256 (sha256)
);

CREATE TABLE IF NOT EXISTS excursions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  source_document_id BIGINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  excursion_date DATE NULL,
  start_time_text VARCHAR(50) NULL,
  start_temperature_c DECIMAL(4,1) NULL,
  zone VARCHAR(255) NULL,
  start_point VARCHAR(255) NULL,
  end_point VARCHAR(255) NULL,
  is_circular TINYINT(1) NULL,
  distance_km DECIMAL(6,2) NULL,
  car_distance_km DECIMAL(6,2) NULL,
  elevation_gain_m INT NULL,
  min_altitude_m INT NULL,
  max_altitude_m INT NULL,
  duration_minutes INT NULL,
  difficulty VARCHAR(50) NULL,
  summary TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_excursions_source_document (source_document_id),
  CONSTRAINT fk_excursions_source_document FOREIGN KEY (source_document_id) REFERENCES source_documents(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS tags (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS excursion_tags (
  excursion_id BIGINT NOT NULL,
  tag_id BIGINT NOT NULL,
  PRIMARY KEY (excursion_id, tag_id),
  CONSTRAINT fk_excursion_tags_excursion FOREIGN KEY (excursion_id) REFERENCES excursions(id) ON DELETE CASCADE,
  CONSTRAINT fk_excursion_tags_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ingestion_runs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at TIMESTAMP NULL,
  status VARCHAR(30) NOT NULL,
  processed_count INT NOT NULL DEFAULT 0,
  inserted_count INT NOT NULL DEFAULT 0,
  updated_count INT NOT NULL DEFAULT 0,
  error_count INT NOT NULL DEFAULT 0,
  notes TEXT NULL
);
