/**
 * Turns a {@link TagFilter} into a SQL WHERE condition that scopes a property
 * scan. Shared by every reporting query (baseline, scenario overlay, drill-down)
 * so tag scoping is defined once. Pure w.r.t. the DB — it only builds a `sql`
 * fragment; the caller ANDs it into its own WHERE.
 *
 *   WHERE p.portfolio_id = ${pid} AND ${tagFilterCondition(tags)}
 *
 * `propertyId` is the property-id column in the calling query (default `p.id`).
 */
import { sql, type SQL } from "drizzle-orm";
import type { TagFilter } from "./viewState";

/** `tag_id IN (…)` over bigint ids. Assumes ids are already numeric strings. */
function tagIdIn(ids: string[]): SQL {
  return sql`pt.tag_id IN (${sql.join(
    ids.map((id) => sql`${BigInt(id)}`),
    sql`, `,
  )})`;
}

export function tagFilterCondition(
  tags: TagFilter,
  propertyId: SQL = sql`p.id`,
): SQL {
  const conds: SQL[] = [];

  if (tags.include.length > 0) {
    if (tags.includeMode === "all") {
      // Home must carry EVERY included tag: the distinct matches among the
      // included set must equal the size of that set.
      conds.push(sql`(
        SELECT COUNT(DISTINCT pt.tag_id)
        FROM property_tag pt
        WHERE pt.property_id = ${propertyId} AND ${tagIdIn(tags.include)}
      ) = ${tags.include.length}`);
    } else {
      // Home carries at least one of the included tags.
      conds.push(sql`EXISTS (
        SELECT 1 FROM property_tag pt
        WHERE pt.property_id = ${propertyId} AND ${tagIdIn(tags.include)}
      )`);
    }
  }

  if (tags.exclude.length > 0) {
    // Home carries none of the excluded tags.
    conds.push(sql`NOT EXISTS (
      SELECT 1 FROM property_tag pt
      WHERE pt.property_id = ${propertyId} AND ${tagIdIn(tags.exclude)}
    )`);
  }

  if (conds.length === 0) return sql`TRUE`;
  return sql.join(conds, sql` AND `);
}
