# your_app/management/commands/unique_values.py
from django.core.management.base import BaseCommand, CommandError
from django.db import connection

class Command(BaseCommand):
    help = (
        "Wypisz unikalne wartości dla kolumny w tabeli (domyślnie: extractly_adsmanual). "
        "Możesz też włączyć zliczanie częstotliwości."
    )

    def add_arguments(self, parser):
        parser.add_argument(
            "--table",
            default="extractly_adsmanual",
            help="Nazwa tabeli w DB (domyślnie: extractly_adsmanual).",
        )
        parser.add_argument(
            "--column",
            required=True,
            help="Nazwa kolumny, dla której chcesz unikalne wartości.",
        )
        parser.add_argument(
            "--count",
            action="store_true",
            help="Zamiast listy wartości zwróć wartości wraz z liczbą wystąpień.",
        )
        parser.add_argument(
            "--limit",
            type=int,
            default=0,
            help="Ogranicz liczbę wyników (0 = bez limitu).",
        )
        parser.add_argument(
            "--order",
            choices=["alpha_asc", "alpha_desc", "freq_asc", "freq_desc", "none"],
            default="alpha_asc",
            help="Kolejność sortowania wyników.",
        )
        parser.add_argument(
            "--include-null",
            action="store_true",
            help="Uwzględnij wartości NULL jako osobny wiersz.",
        )
        parser.add_argument(
            "--lowercase",
            action="store_true",
            help="Porównuj/kategoryzuj po LOWER(text) (przydatne do normalizacji).",
        )

    def handle(self, *args, **opts):
        table = opts["table"]
        column = opts["column"]
        count_mode = opts["count"]
        limit = opts["limit"]
        order = opts["order"]
        include_null = opts["include_null"]
        use_lower = opts["lowercase"]

        # --- Basic validation
        if not column:
            raise CommandError("Musisz podać --column.")

        # --- Quote identifiers safely
        qn = connection.ops.quote_name
        q_table = qn(table)
        q_col = qn(column)

        # Build expression for grouping/selection
        # We cast to TEXT so it works uniformly for JSON/other types.
        base_expr = f"CAST({q_col} AS TEXT)"
        expr = f"LOWER({base_expr})" if use_lower else base_expr

        where_clauses = []
        if not include_null:
            where_clauses.append(f"{q_col} IS NOT NULL")

        where_sql = ""
        if where_clauses:
            where_sql = "WHERE " + " AND ".join(where_clauses)

        # --- Build SQL
        # DISTINCT or GROUP BY with COUNT depending on count_mode
        params = []
        if count_mode:
            # Return value + count
            sql = (
                f"SELECT {expr} AS value, COUNT(*) AS freq "
                f"FROM {q_table} "
                f"{where_sql} "
                f"GROUP BY {expr} "
            )
            # Ordering
            if order == "freq_desc":
                sql += "ORDER BY freq DESC, value ASC "
            elif order == "freq_asc":
                sql += "ORDER BY freq ASC, value ASC "
            elif order == "alpha_desc":
                sql += "ORDER BY value DESC "
            elif order == "alpha_asc":
                sql += "ORDER BY value ASC "
            # else: 'none' → no ORDER BY

        else:
            # DISTINCT values only
            sql = (
                f"SELECT DISTINCT {expr} AS value "
                f"FROM {q_table} "
                f"{where_sql} "
            )
            if order == "alpha_desc":
                sql += "ORDER BY value DESC "
            elif order == "alpha_asc":
                sql += "ORDER BY value ASC "
            # 'freq_*' doesn't apply in distinct-only mode
            # 'none' → no ORDER BY

        if limit and limit > 0:
            sql += f"LIMIT {int(limit)} "

        # --- Execute
        with connection.cursor() as cursor:
            try:
                cursor.execute(sql, params)
                rows = cursor.fetchall()
            except Exception as e:
                raise CommandError(f"Błąd wykonania SQL: {e}\nSQL: {sql}")

        # --- Pretty print
        if count_mode:
            # print header
            self.stdout.write("value\tfreq")
            for value, freq in rows:
                # show <NULL> explicitly if include_null and text-cast produced None
                val_str = "<NULL>" if value is None else str(value)
                self.stdout.write(f"{val_str}\t{freq}")
        else:
            for (value,) in rows:
                val_str = "<NULL>" if value is None else str(value)
                self.stdout.write(val_str)