"""Morning pick journal — the human/LLM forecast experiment (2026-08-19).

Tests whether a PROSPECTIVE forecaster (the owner, an LLM, anyone) carries
signal the machine levers never found. Picks land here premarket; the scorer
(research/pick_scoring.py) judges them against the same bars and cost model
as everything else. Zero trading impact — this table is never read by the
live loop.

Validity rule: a pick only counts if picked_at is BEFORE 09:30 ET on its
session date. Late picks are stored (audit) but flagged invalid and excluded
from scoring — no retroactive genius.
"""
from __future__ import annotations

import uuid
from datetime import datetime, time as dtime
from zoneinfo import ZoneInfo

ET = ZoneInfo("America/New_York")
OPEN_ET = dtime(9, 30)
DIRECTIONS = ("long", "short")

DDL = """
CREATE TABLE IF NOT EXISTS human_picks (
    id VARCHAR PRIMARY KEY,
    session_date DATE NOT NULL,
    picked_at TIMESTAMPTZ NOT NULL,
    forecaster VARCHAR NOT NULL,
    symbol VARCHAR NOT NULL,
    direction VARCHAR NOT NULL DEFAULT 'long',
    conviction INTEGER NOT NULL DEFAULT 2,
    note VARCHAR,
    valid BOOLEAN NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_human_picks_session
    ON human_picks(session_date, forecaster);
"""


def ensure_schema(con) -> None:
    con.execute(DDL)


def add_pick(con, symbol: str, *, forecaster: str = "phil",
             direction: str = "long", conviction: int = 2,
             note: str | None = None, now: datetime | None = None) -> dict:
    """Insert one pick, stamping validity from the wall clock. Returns the row."""
    symbol = (symbol or "").strip().upper()
    if not symbol or len(symbol) > 6 or not symbol.replace(".", "").isalnum():
        raise ValueError(f"bad symbol {symbol!r}")
    if direction not in DIRECTIONS:
        raise ValueError(f"direction must be one of {DIRECTIONS}")
    conviction = max(1, min(3, int(conviction)))
    now_et = (now or datetime.now(tz=ET)).astimezone(ET)
    valid = now_et.timetz().replace(tzinfo=None) < OPEN_ET
    row = dict(id=str(uuid.uuid4()), session_date=now_et.date(),
               picked_at=now_et, forecaster=forecaster[:64], symbol=symbol,
               direction=direction, conviction=conviction,
               note=(note or "")[:280] or None, valid=valid)
    ensure_schema(con)
    con.execute(
        "INSERT INTO human_picks (id, session_date, picked_at, forecaster, symbol, "
        "direction, conviction, note, valid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
        [row["id"], row["session_date"], row["picked_at"], row["forecaster"],
         row["symbol"], row["direction"], row["conviction"], row["note"], row["valid"]])
    return row


def list_picks(con, session_date=None) -> list[dict]:
    ensure_schema(con)
    if session_date is None:
        session_date = datetime.now(tz=ET).date()
    rows = con.execute(
        "SELECT id, session_date, picked_at, forecaster, symbol, direction, "
        "conviction, note, valid FROM human_picks WHERE session_date = ? "
        "ORDER BY picked_at", [session_date]).fetchall()
    cols = ["id", "session_date", "picked_at", "forecaster", "symbol",
            "direction", "conviction", "note", "valid"]
    return [dict(zip(cols, r)) for r in rows]