#!/usr/bin/env python3
"""Lightweight v4.5 validation checks for a deployed ClientFlow backend."""
from __future__ import annotations

import sys

from sqlalchemy import text

from app.db import engine, init_db


REQUIRED_TABLES = ["communications", "timeline_events", "tasks", "integration_outbox", "opportunities"]
REQUIRED_TASK_COLUMNS = ["communication_id", "document_id", "shipment_id", "outbox_id", "priority", "assigned_to"]


def main() -> int:
    init_db()
    with engine.begin() as conn:
        tables = {r[0] for r in conn.execute(text("""
            SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = 'public'
        """))}
        missing_tables = [t for t in REQUIRED_TABLES if t not in tables]

        cols = {r[0] for r in conn.execute(text("""
            SELECT column_name
            FROM information_schema.columns
            WHERE table_schema = 'public' AND table_name = 'tasks'
        """))}
        missing_cols = [c for c in REQUIRED_TASK_COLUMNS if c not in cols]

        comm_count = conn.execute(text("SELECT COUNT(*) FROM communications")).scalar()
        timeline_count = conn.execute(text("SELECT COUNT(*) FROM timeline_events")).scalar()

    if missing_tables or missing_cols:
        print("ClientFlow v4.5 validation failed")
        print("Missing tables:", ", ".join(missing_tables) or "none")
        print("Missing task columns:", ", ".join(missing_cols) or "none")
        return 1

    print("ClientFlow v4.5 validation OK")
    print(f"communications={comm_count} timeline_events={timeline_count}")
    return 0


if __name__ == "__main__":
    sys.exit(main())