search.yml

operationalpoint:
  table: osrd_search_operationalpoint
  # TODO: allow table: name: ..., as: ... to allow aliases
  columns:
    obj_id: string
    infra_id: integer
    name: string
    uic: integer
    ch: string
    trigram: string
  result:
    joins: |
      INNER JOIN osrd_infra_operationalpointmodel AS opm ON opm.id = osrd_search_operationalpoint.id
      INNER JOIN osrd_infra_operationalpointlayer AS opl ON opm.obj_id = opl.obj_id AND opm.infra_id = opl.infra_id
    columns:
      obj_id: opm.obj_id
      infra_id: opm.infra_id
      uic: opm.data->'extensions'->'identifier'->'uic'
      name: opm.data#>>'{extensions,identifier,name}'
      trigram: opm.data#>>'{extensions,sncf,trigram}'
      ch: opm.data#>>'{extensions,sncf,ch}'
      geographic: ST_AsGeoJSON(ST_Transform(opl.geographic, 4326))::json
      schematic: ST_AsGeoJSON(ST_Transform(opl.schematic, 4326))::json
      track_sections: opm.data->'parts'

track:
  table: osrd_search_track
  columns:
    infra_id: integer
    line_name: string
    line_code: integer
  result:
    columns:
      infra_id: osrd_search_track.infra_id
      line_code: osrd_search_track.line_code
      line_name: osrd_search_track.unprocessed_line_name

signal:
  table: osrd_search_signal
  columns:
    infra_id: integer
    line_name: string
    line_code: integer
    label: string
    aspects:
      array: string
    systems:
      array: string
  result:
    joins: |
      INNER JOIN osrd_infra_signalmodel AS sig ON sig.id = osrd_search_signal.id
      INNER JOIN osrd_infra_tracksectionmodel AS ts ON ts.obj_id = sig.data->>'track' AND ts.infra_id = sig.infra_id
      INNER JOIN osrd_infra_signallayer AS lay ON lay.infra_id = sig.infra_id AND lay.obj_id = sig.obj_id
    columns:
      infra_id: sig.infra_id
      label: sig.data->'extensions'->'sncf'->>'label'
      aspects: osrd_search_signal.aspects
      systems: osrd_search_signal.systems
      type: sig.data->'extensions'->'sncf'->>'installation_type'
      line_code: osrd_search_signal.line_code
      line_name: ts.data->'extensions'->'sncf'->>'line_name'
      geographic: ST_AsGeoJSON(ST_Transform(lay.geographic, 4326))::json
      schematic: ST_AsGeoJSON(ST_Transform(lay.schematic, 4326))::json

project:
  table: osrd_search_project
  columns:
    id: integer
    name: string
    description: string
    tags: string
  result:
    joins: |
      INNER JOIN osrd_infra_project AS project ON project.id = osrd_search_project.id
    columns:
      id: project.id
      image: project.image_id
      name: project.name
      studies_count: (SELECT COUNT(study.id) FROM osrd_infra_study AS study WHERE osrd_search_project.id = study.project_id)
      description: project.description
      last_modification: project.last_modification
      tags: project.tags

study:
  table: osrd_search_study
  columns:
    id: integer
    name: string
    description: string
    tags: string
    project_id: integer
  result:
    joins: |
      INNER JOIN osrd_infra_study AS study ON study.id = osrd_search_study.id
    columns:
      id: study.id
      project_id: study.project_id
      name: study.name
      scenarios_count: (SELECT COUNT(scenario.id) FROM osrd_infra_scenario AS scenario WHERE osrd_search_study.id = scenario.study_id)
      description: study.description
      last_modification: study.last_modification
      tags: study.tags

scenario:
  table: osrd_search_scenario
  columns:
    id: integer
    name: string
    description: string
    tags: string
    study_id: integer
  result:
    joins: |
      INNER JOIN osrd_infra_scenario AS scenario ON scenario.id = osrd_search_scenario.id
      INNER JOIN osrd_infra_infra AS infra ON infra.id = scenario.infra_id
    columns:
      id: scenario.id
      study_id: scenario.study_id
      name: scenario.name
      electrical_profile_set_id: scenario.electrical_profile_set_id
      infra_id: scenario.infra_id
      infra_name: infra.name
      trains_count: (SELECT COUNT(trains.id) FROM osrd_infra_trainschedule AS trains WHERE scenario.timetable_id = trains.timetable_id)
      description: scenario.description
      last_modification: scenario.last_modification
      tags: scenario.tags