"""Bands a tenant's own prices into budget, mid and premium.

"Premium" has no global meaning -- it means expensive for this catalog. The live
catalog spans 79 to 3,699,999 minor units, so a fixed threshold would put nearly
everything in one bucket.
"""
import logging

from psycopg2 import sql

from app.services.infra.database import get_db_connection

logger = logging.getLogger(__name__)

MIN_PRICED_PRODUCTS = 10


def compute_bands(tenant_id: str):
    """The 33rd and 67th percentile of a tenant's prices, or None.

    Prefers price_reference_cents so a tenant with two currencies is not banded
    on unconverted numbers.
    """
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(sql.SQL("""
                SELECT count(*),
                       percentile_disc(0.33) WITHIN GROUP (ORDER BY p),
                       percentile_disc(0.67) WITHIN GROUP (ORDER BY p)
                FROM (
                    SELECT COALESCE(price_reference_cents, price_cents) AS p
                    FROM {}.strategist_products
                    WHERE COALESCE(price_reference_cents, price_cents) IS NOT NULL
                ) prices
            """).format(sql.Identifier(tenant_id)))
            count, low, high = cur.fetchone()
    finally:
        conn.close()

    if not count or count < MIN_PRICED_PRODUCTS or low is None or high is None:
        logger.info("Too few priced products in %s to band prices", tenant_id)
        return None
    return int(low), int(high)


def classify(price_cents, bands):
    if price_cents is None or bands is None:
        return None
    low, high = bands
    if price_cents < low:
        return "budget"
    if price_cents < high:
        return "mid"
    return "premium"