from datetime import date
from sqlalchemy import create_engine, text

url = "postgresql+psycopg://qa:qa_sl001_only@stocklab-qa-pg-sl001-20260910-101737:5432/stocklab_qa"

# The hostname is replaced by the runner below.

from datetime import date
from sqlalchemy import create_engine, text

engine = create_engine(url)

with engine.begin() as conn:
    conn.execute(text("""
        CREATE TABLE financial_periods (
            id INTEGER PRIMARY KEY,
            security_id INTEGER NOT NULL,
            period_type VARCHAR(10) NOT NULL,
            period_end DATE NOT NULL,
            filing_date DATE
        )
    """))

    conn.execute(text("""
        INSERT INTO financial_periods
            (id, security_id, period_type, period_end, filing_date)
        VALUES
            (1, 100, 'FY', DATE '2025-12-31', DATE '2026-02-15'),
            (2, 100, 'FY', DATE '2026-12-31', DATE '2027-02-15')
    """))

    as_of = date(2026, 9, 10)

    rows = conn.execute(
        text("""
            SELECT id, period_end, filing_date
            FROM financial_periods
            WHERE security_id = :security_id
              AND period_type = 'FY'
              AND period_end <= :as_of
            ORDER BY period_end DESC
            LIMIT 11
        """),
        {
            "security_id": 100,
            "as_of": as_of,
        },
    ).fetchall()

    print("as_of:", as_of)
    print("rows returned:", len(rows))

    for row in rows:
        print(
            "id=", row.id,
            "period_end=", row.period_end,
            "filing_date=", row.filing_date,
        )

    assert len(rows) == 1
    assert rows[0].period_end == date(2025, 12, 31)
    assert all(row.period_end <= as_of for row in rows)

print("REAL POSTGRES PIT TEST: PASS")