"""How much the organisation holds, measured in its own schema.

Vectors are measured from the database itself: every table in the schema with a vector or float-array column,
indexes included, so a new product's vector table is counted the day it appears and nothing has to be registered.
Cloud objects are the sum of what products recorded in storage_objects and have not deleted.
"""
from __future__ import annotations

VECTOR_COLUMN_TYPES = ("vector", "_float4", "_float8")   # pgvector, REAL[], DOUBLE PRECISION[]


def vector_tables(conn, schema: str) -> list:
    """(table, bytes) for every table in the schema that holds vectors."""
    return conn.execute(
        "SELECT c.table_name, pg_total_relation_size(quote_ident(c.table_schema) || '.' || quote_ident(c.table_name)) "
        "FROM information_schema.columns c JOIN information_schema.tables t "
        "  ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND t.table_type = 'BASE TABLE' "
        "WHERE c.table_schema = %s AND c.udt_name = ANY(%s) "
        "GROUP BY c.table_name, c.table_schema ORDER BY 2 DESC",
        (schema, list(VECTOR_COLUMN_TYPES))).fetchall()


def vector_bytes(conn, schema: str) -> int:
    return sum(int(size) for _, size in vector_tables(conn, schema))


def cloud_bytes(conn, schema: str) -> int:
    row = conn.execute(f'SELECT coalesce(sum(bytes), 0) FROM "{schema}".storage_objects WHERE deleted_at IS NULL').fetchone()
    return int(row[0] or 0)


def cloud_by_product(conn, schema: str) -> dict:
    rows = conn.execute(f'SELECT product, coalesce(sum(bytes), 0), count(*) FROM "{schema}".storage_objects '
                        "WHERE deleted_at IS NULL GROUP BY product ORDER BY 2 DESC").fetchall()
    return {product: {"bytes": int(size), "objects": int(n)} for product, size, n in rows}
