A18 을 받는 DB 스키마
마이그레이션 한 장

-- ============================================================================
--  V4__a18_seat_forecast.sql
--  연어(Salmonbus) — 1차 운영 모델을 A18(차량 상태 조건부 좌석 분포)로 교체
--
--  V1__collector.sql · V2__model.sql · V3__forecast.sql 위에 얹는다.
--  **기존 열의 타입은 하나도 바꾸지 않는다.** 계약 대조 결과 설명문 외 변경 0건이다.
--
--  하는 일 — 열 추가 6 · 신설 표 2 · 인덱스 3 · 제약 1
--  하지 않는 일 — forecast_publication 과 stop_prediction 의 DROP (아래 6장)
--
--  적용 순서가 강제된다:
--    1) 이 마이그레이션
--    2) **노선정보 수집을 한 번 돌려** 방향별 첫차·막차 4열을 채운다
--    3) 그 다음에 api 를 올린다
--  2 를 건너뛰면 /board 를 낼 수 없다 — directions[].firstDepartureTime 이 필수 필드다.
-- ============================================================================

-- ============================================================
-- 1. route_reference_version — 방향별 첫차·막차 4열
--    상류 getBusRouteInfoItemv2 의 up/down First/Last Time 에서 온다.
--    NULL 허용이라 백필이 없다. 첫 노선정보 수집이 채운다.
--
--    방향마다 다르다 — 1650 은 상행 22:35 · 하행 23:55 로 80분 차이가 난다.
--    노선 하나를 통째로 "운행 종료"라고 하면 그 80분 동안 틀린다.
-- ============================================================
ALTER TABLE route_reference_version
    ADD COLUMN up_first_departure_time   char(5),   -- KST HH:MM. 상행 첫차
    ADD COLUMN up_last_departure_time    char(5),   -- KST HH:MM. 상행 막차
    ADD COLUMN down_first_departure_time char(5),   -- KST HH:MM. 하행 첫차
    ADD COLUMN down_last_departure_time  char(5);   -- KST HH:MM. 하행 막차

ALTER TABLE route_reference_version
    ADD CONSTRAINT ck_ref_departure_format CHECK (
        (up_first_departure_time   IS NULL OR up_first_departure_time   ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$') AND
        (up_last_departure_time    IS NULL OR up_last_departure_time    ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$') AND
        (down_first_departure_time IS NULL OR down_first_departure_time ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$') AND
        (down_last_departure_time  IS NULL OR down_last_departure_time  ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$'));

-- ============================================================
-- 2. location_poll — 예보까지 끝났음을 찍는 열
--    /board 스냅샷 선택의 정렬 축이자 Board.observedAt 의 값이다.
--    관측 행의 observed_at 과 같은 값이지만 poll 쪽을 권위로 삼는다 —
--    차량이 0대인 판(SUCCESS_EMPTY)에는 관측 행이 없는데
--    Board.observedAt 은 그때도 필수 필드다.
--
--    NULL 허용이라 백필이 없다. 마이그레이션 이전 poll 은 전부 스냅샷 후보에서
--    빠지는데, 어차피 그 poll 들에는 예보 행이 없으므로 고르면 안 되는 것이 맞다.
-- ============================================================
ALTER TABLE location_poll
    ADD COLUMN forecast_completed_at timestamptz;  -- 한 판의 예보를 다 쓴 시각

ALTER TABLE location_poll
    ADD CONSTRAINT ck_poll_forecast CHECK (
        forecast_completed_at IS NULL
        OR (response_received_at IS NOT NULL
            AND forecast_completed_at >= response_received_at));

-- 예보까지 끝난 마지막 poll — /board 스냅샷 선택.
-- 부분 조건이 있어야 한 번의 인덱스 스캔으로 끝난다.
CREATE INDEX ix_poll_forecast_ready
    ON location_poll (route_reference_version_id, response_received_at DESC)
    WHERE outcome IN ('SUCCESS_ROWS', 'SUCCESS_EMPTY')
      AND forecast_completed_at IS NOT NULL;

-- ============================================================
-- 3. vehicle_observation — 여정 키
--    같은 차량이라도 여정이 바뀌면 다른 계열이다. 상류 좌석 기울기를
--    한 여정 안에서만 잇기 위한 키다.
--
--    NULL 허용이라 백필이 필요 없다. 다만 마이그레이션 이전 관측은 여정이
--    비어 있어 **라벨** 대상에서 빠진다(서빙은 결측 지시자로 받는다).
--    소급 계산은 정규화 판을 새로 매기는 일이라 별도 작업이다.
-- ============================================================
ALTER TABLE vehicle_observation
    ADD COLUMN vehicle_trip_key varchar(120);  -- 한 여정을 잇는 키. NULL = 여정 미상

-- 같은 여정의 앞선 관측 — 상류 좌석 기울기
CREATE INDEX ix_obs_trip
    ON vehicle_observation (route_reference_version_id, vehicle_trip_key, stop_order)
    WHERE vehicle_trip_key IS NOT NULL;

-- 그 정류장에 직전 도착한 차량과 직전 연속 만석 대수(90분 창)
CREATE INDEX ix_obs_stop_recent
    ON vehicle_observation (route_reference_version_id, stop_order, observed_at DESC);

-- 아래 vehicle_stop_prediction 의 복합 FK 가 이 유니크를 상대편으로 삼는다.
-- **이것이 없으면 5장의 fk_vsp_obs 생성이 실패한다** — PostgreSQL 은 참조 대상에
-- unique 제약을 요구한다. V1 의 vehicle_observation 에는 (id) PK 만 있었다.
ALTER TABLE vehicle_observation
    ADD CONSTRAINT ux_obs_context UNIQUE (id, route_reference_version_id);

-- ============================================================
-- 4. demand_profile — (노선 판본, 정류장, 시간대) 셀의 경험 통계
--    A18 의 셀 재료가 읽고, 통과 구간의 행들을 합쳐 읽는다.
--
--    저장하는 값은 **원값**이다. z화((노선, 시간대) 안에서)는 같은 세대의
--    행들에서 유도한다 — 표준화 상수를 따로 저장하면 표가 갱신될 때 상수가
--    뒤처져 z 가 어긋난다.
--
--    재계산은 같은 키를 덮어쓴다(UPSERT). 빈 채로 만들어지고, 비는 동안은
--    이웃 폴백만 돈다(개편 직후 새 판본에도 같은 일이 일어난다 — 콜드스타트, 미결).
-- ============================================================
CREATE TABLE demand_profile (
    route_reference_version_id bigint       NOT NULL,
    stop_order                 int          NOT NULL,
    time_cell_id               varchar(40)  NOT NULL,  -- morning | evening | other
    feature_contract_version   varchar(40)  NOT NULL,  -- 셀 정의의 소유자
    revision                   int          NOT NULL,  -- 재계산 세대. 배치마다 단조 증가

    occupancy_mean             double precision NOT NULL,  -- 도착 시 평균 점유율
    net_demand_mean            double precision NOT NULL,  -- 평균 순수요. 음수가 정상
    sample_count               int          NOT NULL,  -- 이 셀을 만든 유효 라벨 수
    day_count                  int          NOT NULL,  -- 기여 날짜 수. 날짜 균등가중이라 함께 둔다
    trained_through            timestamptz  NOT NULL,  -- 이 시각까지의 도착 라벨만 들어갔다
    computed_at                timestamptz  NOT NULL,

    PRIMARY KEY (route_reference_version_id, stop_order, time_cell_id, feature_contract_version),
    CONSTRAINT fk_prof_stop FOREIGN KEY (route_reference_version_id, stop_order)
        REFERENCES route_stop (route_reference_version_id, stop_order),

    CONSTRAINT ck_prof_revision  CHECK (revision >= 1),
    CONSTRAINT ck_prof_occupancy CHECK (occupancy_mean >= 0 AND occupancy_mean <= 1
                                        AND occupancy_mean = occupancy_mean),
    CONSTRAINT ck_prof_demand    CHECK (net_demand_mean >= -1 AND net_demand_mean <= 1
                                        AND net_demand_mean = net_demand_mean),
    CONSTRAINT ck_prof_counts    CHECK (sample_count >= 0 AND day_count >= 0
                                        AND day_count <= sample_count)
);

-- ============================================================
-- 5. vehicle_stop_prediction — 한 관측에서 낸, 앞으로 지날 정류장 하나의 좌석 예보
--    **이 표가 /board 의 본체이고, 한 행이 곧 응답의 한 항목이다.**
--    행이 없으면 그 차량은 그 정류장에 대해 조용히 빠진다.
--
--    판 단위 봉인이 없다. 한 행은 정확히 한 vehicle_observation 행에 매달리는
--    파생이고, 한 판의 단위는 poll 하나다. 판의 원자성은 봉인 절차가 아니라
--    location_poll.forecast_completed_at 이 낸다.
--
--    행을 만들지 않는 경우 — 미정차 정류장 · 여정 끝 너머 · 좌석 결측 관측.
-- ============================================================
CREATE TABLE vehicle_stop_prediction (
    vehicle_observation_id     bigint       NOT NULL,
    route_reference_version_id bigint       NOT NULL,
    target_stop_order          int          NOT NULL,
    horizon_stops              int          NOT NULL,  -- 몇 정류장 전에서 예측했나. 모델 키다
    model_deployment_id        bigint       NOT NULL,  -- 읽기 필터가 아니라 출처 표시
    cell_profile_revision      int          NOT NULL,  -- 읽은 demand_profile 세대

    p_full_raw                 double precision NOT NULL,  -- 사전확률 이동 적용 **전**
    p_full                     double precision NOT NULL,  -- 응답에 쓰는 값. 좌석 분포의 0 지점
    expected_seats             double precision,           -- 기댓값. NULL 이면 api 가 필드를 뺀다
    generated_at               timestamptz  NOT NULL,      -- 한 poll 의 행은 전부 같은 값

    settlement                 varchar(16)  NOT NULL,      -- 라벨 회수 상태
    outcome_observation_id     bigint,                     -- 대상 정류장 도착 관측
    arrived_seats              int,                        -- 도착 시 실제 잔여석. 0 이면 만석
    settled_at                 timestamptz,

    PRIMARY KEY (vehicle_observation_id, target_stop_order),

    -- 관측과 다른 판본의 순번이 끼어들지 못한다
    CONSTRAINT fk_vsp_obs FOREIGN KEY (vehicle_observation_id, route_reference_version_id)
        REFERENCES vehicle_observation (id, route_reference_version_id),
    CONSTRAINT fk_vsp_stop FOREIGN KEY (route_reference_version_id, target_stop_order)
        REFERENCES route_stop (route_reference_version_id, stop_order),
    CONSTRAINT fk_vsp_deployment FOREIGN KEY (model_deployment_id)
        REFERENCES model_deployment (id),
    CONSTRAINT fk_vsp_outcome FOREIGN KEY (outcome_observation_id)
        REFERENCES vehicle_observation (id),

    CONSTRAINT ck_vsp_target   CHECK (target_stop_order >= 1),
    -- 서빙 상한 12. 지평이 모델 키라 유도값인데도 열로 둔다
    CONSTRAINT ck_vsp_horizon  CHECK (horizon_stops >= 1 AND horizon_stops <= 12),
    CONSTRAINT ck_vsp_revision CHECK (cell_profile_revision >= 1),
    -- NaN · ±Infinity 까지 거절한다. 응답이 1 - NaN 이 되면 안 된다
    CONSTRAINT ck_vsp_praw     CHECK (p_full_raw >= 0 AND p_full_raw <= 1
                                      AND p_full_raw = p_full_raw),
    CONSTRAINT ck_vsp_pfull    CHECK (p_full >= 0 AND p_full <= 1 AND p_full = p_full),
    CONSTRAINT ck_vsp_expected CHECK (expected_seats IS NULL
                                      OR (expected_seats >= 0 AND expected_seats = expected_seats)),
    CONSTRAINT ck_vsp_arrived  CHECK (arrived_seats IS NULL OR arrived_seats >= 0),

    CONSTRAINT ck_vsp_settlement CHECK (settlement IN
        ('PENDING', 'SETTLED', 'SKIPPED', 'LOST', 'SEAT_MISSING')),
    -- SETTLED 일 때만 실제 잔여석이 있다
    CONSTRAINT ck_vsp_seats_state CHECK ((settlement = 'SETTLED') = (arrived_seats IS NOT NULL)),
    -- 도착 관측은 회수가 성공한 두 상태에만 있다
    CONSTRAINT ck_vsp_outcome_state CHECK (
        (settlement IN ('SETTLED', 'SEAT_MISSING')) = (outcome_observation_id IS NOT NULL)),
    -- PENDING 이면 회수 시각이 없고, 아니면 있다
    CONSTRAINT ck_vsp_settled_state CHECK ((settlement = 'PENDING') = (settled_at IS NULL)),
    CONSTRAINT ck_vsp_settled_order CHECK (settled_at IS NULL OR settled_at >= generated_at)
);

-- 라벨 회수 배치가 집는 경로 — 아직 안 닫힌 예보
CREATE INDEX ix_vsp_pending
    ON vehicle_stop_prediction (route_reference_version_id, generated_at)
    WHERE settlement = 'PENDING';

-- ----------------------------------------------------------------------------
-- 열 하나로는 못 거는 불변식 두 개. 쓰기 경로에서 강제하고 아래 질의로 감시한다.
--
--   horizon_stops = target_stop_order − (관측의 stop_order)
--   generated_at >= 관측의 observed_at
--
--   SELECT p.vehicle_observation_id, p.target_stop_order
--     FROM vehicle_stop_prediction p
--     JOIN vehicle_observation o ON o.id = p.vehicle_observation_id
--    WHERE p.horizon_stops <> p.target_stop_order - o.stop_order
--       OR p.generated_at < o.observed_at;
--
-- 다른 표의 값이라 CHECK 로 표현할 수 없다. 트리거로 걸 수도 있지만 쓰기 경로가
-- 한 곳(processor)뿐이라 그쪽 테스트로 막는 편을 택했다.
-- ----------------------------------------------------------------------------

-- ============================================================
-- 6. 발행 계층 — 이 마이그레이션은 DROP 하지 않는다
--
--    forecast_publication 과 stop_prediction 은 v4 계약에서 빠졌다.
--    배포에서 하는 일은 발행 스케줄러를 멈추고, 봉인·승격 프로시저와
--    두 표를 읽는 코드를 걷어내는 데까지다.
--
--    **물리 DROP 을 언제 할지는 이 문서가 정하지 않는다**(미결 14).
--    두 표에는 발행본이 쌓인 적이 없어 옮길 자료도 백필도 없다.
--    정해지면 별도 마이그레이션으로 아래를 쓴다 —
--    stop_prediction 이 먼저다(forecast_publication 을 FK 로 가리킨다).
--
--      DROP TABLE IF EXISTS stop_prediction;
--      DROP TABLE IF EXISTS forecast_publication;
-- ============================================================

55|범계역|204000262|지평12|0.9100

-- 아직 실행하지 않는다. 시점이 정해지면 별도 마이그레이션(V5 이후)으로 옮긴다.
-- 순서 — stop_prediction 이 forecast_publication 을 FK 로 가리킨다.
--
--   DROP TABLE IF EXISTS stop_prediction;
--   DROP TABLE IF EXISTS forecast_publication;

SELECT s.stop_order, s.name, o.vehicle_id, p.horizon_stops,
       round((1 - p.p_full)::numeric, 4) AS seat_available_probability
  FROM location_poll lp
  JOIN vehicle_stop_prediction p ON p.route_reference_version_id = lp.route_reference_version_id
  JOIN vehicle_observation o ON o.id = p.vehicle_observation_id AND o.poll_id = lp.id
  JOIN route_stop s ON s.route_reference_version_id = p.route_reference_version_id
                   AND s.stop_order = p.target_stop_order
 WHERE lp.id = (SELECT id FROM location_poll
                 WHERE route_reference_version_id = 1
                   AND outcome IN ('SUCCESS_ROWS','SUCCESS_EMPTY')
                   AND forecast_completed_at IS NOT NULL
                 ORDER BY response_received_at DESC LIMIT 1)
   AND s.boarding_allowed
 ORDER BY s.stop_order, p.horizon_stops;