-- 052_scrape_schedules.sql
-- In-app scheduler config — заменяет cron-script (avito-city-sweep.sh) для UI-managed scheduling.
-- Background loop в FastAPI lifespan periodically (every 60s) checks этой таблицы.

BEGIN;

CREATE TABLE IF NOT EXISTS scrape_schedules (
    id              bigserial PRIMARY KEY,
    source          text NOT NULL UNIQUE,        -- 'avito_city_sweep'
    enabled         boolean NOT NULL DEFAULT true,
    window_start_hour int NOT NULL DEFAULT 2,    -- UTC hour 0-23 inclusive
    window_end_hour   int NOT NULL DEFAULT 5,    -- UTC hour 0-23 inclusive (если меньше start → ночное окно cross-midnight)
    default_params  jsonb NOT NULL DEFAULT '{}'::jsonb,
                                                  -- {"pages_per_anchor":3,"detail_top_n":20,
                                                  --  "request_delay_sec":7,"enrich_houses":true,"radius_m":1500}
    last_run_id     bigint REFERENCES scrape_runs(id),
    last_run_at     timestamptz,
    next_run_at     timestamptz,                 -- UTC; computed после каждого fire
    created_at      timestamptz NOT NULL DEFAULT NOW(),
    updated_at      timestamptz NOT NULL DEFAULT NOW(),

    CONSTRAINT window_start_range CHECK (window_start_hour BETWEEN 0 AND 23),
    CONSTRAINT window_end_range   CHECK (window_end_hour BETWEEN 0 AND 23)
);

CREATE INDEX IF NOT EXISTS scrape_schedules_next_run_idx
    ON scrape_schedules (next_run_at)
    WHERE enabled = true;

-- Seed: default schedule для avito_city_sweep (02:00-05:00 UTC = 05:00-08:00 МСК)
INSERT INTO scrape_schedules (source, enabled, window_start_hour, window_end_hour, default_params)
VALUES (
    'avito_city_sweep',
    true,
    2,
    5,
    '{"pages_per_anchor": 3, "detail_top_n": 20, "request_delay_sec": 7.0, "enrich_houses": true, "radius_m": 1500}'::jsonb
)
ON CONFLICT (source) DO NOTHING;

COMMENT ON TABLE  scrape_schedules IS 'In-app scheduler config (заменяет cron-script setup).';
COMMENT ON COLUMN scrape_schedules.window_start_hour IS 'UTC hour 0-23. Next_run_at пикается random в [window_start_hour, window_end_hour].';
COMMENT ON COLUMN scrape_schedules.window_end_hour IS 'UTC. Если меньше start → cross-midnight (например 22→3 = 22:00-03:00 UTC).';
COMMENT ON COLUMN scrape_schedules.next_run_at IS 'Когда scheduler должен trigger next run (UTC). Recomputed после каждого fire.';

COMMIT;