/**
 * Audience segments: CRUD over saved rule sets + evaluator that compiles
 * a rules JSON into a parametrised SQL query against the customers table.
 *
 * Segments are NEVER materialized — every preview/launch re-evaluates so
 * the audience always reflects the latest customer state. The campaign
 * runner snapshots the rules JSON onto the campaign at launch time so
 * subsequent segment edits don't retro-affect an in-flight broadcast.
 */

import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { ValidationError } from '@server/errors';
import { bindValue } from '@server/db/bind';
import type {
  AudienceRules, SegmentRow, RecipientPreviewItem,
} from './types';

// ─── Rules normalization / validation ──────────────────────────────────────

const ALLOWED_CHANNELS = new Set(['any', 'email', 'whatsapp']);

export function normalizeRules(raw: unknown): AudienceRules {
  const r = (raw ?? {}) as Record<string, unknown>;
  const out: AudienceRules = {};

  if (Array.isArray(r.branchIds)) {
    out.branchIds = r.branchIds
      .filter((x): x is string => typeof x === 'string' && x.trim().length > 0)
      .slice(0, 50);
  }
  if (typeof r.orderRecencyDays === 'number' && Number.isFinite(r.orderRecencyDays) && r.orderRecencyDays > 0) {
    out.orderRecencyDays = Math.min(3650, Math.floor(r.orderRecencyDays));
  }
  if (typeof r.orderCountMin === 'number' && Number.isFinite(r.orderCountMin) && r.orderCountMin > 0) {
    out.orderCountMin = Math.min(10_000, Math.floor(r.orderCountMin));
  }
  if (typeof r.lifetimeSpendMin === 'number' && Number.isFinite(r.lifetimeSpendMin) && r.lifetimeSpendMin > 0) {
    out.lifetimeSpendMin = r.lifetimeSpendMin;
  }
  if (Array.isArray(r.tags)) {
    out.tags = r.tags
      .filter((x): x is string => typeof x === 'string' && x.trim().length > 0)
      .slice(0, 50);
  }
  if (typeof r.channel === 'string' && ALLOWED_CHANNELS.has(r.channel)) {
    out.channel = r.channel as AudienceRules['channel'];
  }
  if (typeof r.includeOptedOut === 'boolean') {
    out.includeOptedOut = r.includeOptedOut;
  }
  return out;
}

// ─── CRUD ──────────────────────────────────────────────────────────────────

function rowToSegment(r: Record<string, unknown>): SegmentRow {
  return {
    id: r.id as string,
    restaurant_id: r.restaurant_id as string,
    branch_id: (r.branch_id as string | null) ?? null,
    name: r.name as string,
    description: (r.description as string | null) ?? null,
    rules: normalizeRules(r.rules),
    created_by: (r.created_by as string | null) ?? null,
    created_at: String(r.created_at),
    updated_at: String(r.updated_at),
  };
}

/**
 * When `effectiveBranchId` is non-null, narrow the result to that branch's
 * own segments + restaurant-wide segments (branch_id IS NULL). When null,
 * return everything in the restaurant — used by owners viewing "All
 * branches" in the topbar.
 */
function branchScopeClause(effectiveBranchId: string | null): SQL {
  if (!effectiveBranchId) return sql`TRUE`;
  return sql`(branch_id IS NULL OR branch_id = ${effectiveBranchId})`;
}

export async function listSegments(
  restaurantId: string,
  effectiveBranchId: string | null = null,
): Promise<SegmentRow[]> {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT id, restaurant_id, branch_id, name, description, rules, created_by, created_at, updated_at
    FROM marketing_segments
    WHERE restaurant_id = ${restaurantId}
      AND ${branchScopeClause(effectiveBranchId)}
    ORDER BY created_at DESC
  `);
  return (rows as Record<string, unknown>[]).map(rowToSegment);
}

export async function getSegment(
  restaurantId: string,
  id: string,
  effectiveBranchId: string | null = null,
): Promise<SegmentRow | null> {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT id, restaurant_id, branch_id, name, description, rules, created_by, created_at, updated_at
    FROM marketing_segments
    WHERE restaurant_id = ${restaurantId} AND id = ${id}
      AND ${branchScopeClause(effectiveBranchId)}
    LIMIT 1
  `);
  const r = rows[0] as Record<string, unknown> | undefined;
  return r ? rowToSegment(r) : null;
}

/** Map a Postgres unique-violation on the audience-name index to a friendly
 *  ValidationError so the API layer surfaces a clean inline error instead of
 *  bubbling a 500. The `fields.name` payload is what the marketing UI keys
 *  off to highlight the Name input and keep the dialog open with the rest
 *  of the operator's input intact (Task #289). Any other error is re-thrown
 *  unchanged. */
function rethrowSegmentNameConflict(err: unknown): never {
  const code = (err as { code?: string })?.code;
  const constraint = (err as { constraint?: string })?.constraint;
  if (code === '23505' && (
    !constraint || constraint === 'uniq_marketing_segments_restaurant_name'
  )) {
    const message = 'An audience with this name already exists';
    throw new ValidationError(message, { name: [message] });
  }
  throw err;
}

export async function createSegment(
  restaurantId: string,
  input: { name: string; description?: string | null; rules: unknown; createdBy?: string | null; branchId?: string | null }
): Promise<SegmentRow> {
  await initDatabase();
  const name = (input.name || '').trim();
  if (!name) throw new ValidationError('Segment name is required');
  const rules = normalizeRules(input.rules);
  try {
    const { rows } = await db.execute(sql`
      INSERT INTO marketing_segments (restaurant_id, branch_id, name, description, rules, created_by)
      VALUES (${restaurantId}, ${input.branchId ?? null}, ${name}, ${input.description ?? null},
              ${JSON.stringify(rules)}::jsonb, ${input.createdBy ?? null})
      RETURNING id, restaurant_id, branch_id, name, description, rules, created_by, created_at, updated_at
    `);
    return rowToSegment(rows[0] as Record<string, unknown>);
  } catch (err) {
    rethrowSegmentNameConflict(err);
  }
}

export async function updateSegment(
  restaurantId: string,
  id: string,
  input: { name?: string; description?: string | null; rules?: unknown },
  /** Caller's effective branch — used to scope the existence check so a
   *  pinned staff user can never edit a sibling branch's segment by id. */
  effectiveBranchId: string | null = null,
): Promise<SegmentRow> {
  await initDatabase();
  const existing = await getSegment(restaurantId, id, effectiveBranchId);
  if (!existing) throw new ValidationError('Segment not found');

  const name = input.name !== undefined ? input.name.trim() : existing.name;
  if (!name) throw new ValidationError('Segment name is required');
  const description = input.description !== undefined ? input.description : existing.description;
  const rules = input.rules !== undefined ? normalizeRules(input.rules) : existing.rules;

  try {
    const { rows } = await db.execute(sql`
      UPDATE marketing_segments
      SET name = ${name},
          description = ${description},
          rules = ${JSON.stringify(rules)}::jsonb,
          updated_at = now()
      WHERE restaurant_id = ${restaurantId} AND id = ${id}
      RETURNING id, restaurant_id, branch_id, name, description, rules, created_by, created_at, updated_at
    `);
    return rowToSegment(rows[0] as Record<string, unknown>);
  } catch (err) {
    rethrowSegmentNameConflict(err);
  }
}

/**
 * List the campaigns currently linked to this segment that are still in a
 * non-terminal state (draft / scheduled / sending). These are the ones a
 * delete would auto-detach via {@link snapshotSegmentOntoCampaigns}, so the
 * UI uses this as a pre-check to warn the operator *before* they confirm.
 *
 * Completed/cancelled/failed campaigns are intentionally excluded — they
 * will never send again, so they don't need to be flagged.
 */
export type LinkedActiveCampaignStatus = 'draft' | 'scheduled' | 'sending';

export async function listLinkedActiveCampaigns(
  restaurantId: string, segmentId: string,
  effectiveBranchId: string | null = null,
): Promise<Array<{ id: string; name: string; status: LinkedActiveCampaignStatus }>> {
  await initDatabase();
  // Branch-scope campaigns so a SHARED segment (branch_id IS NULL) can't be
  // used to enumerate sibling-branch campaigns.
  const branchClause = effectiveBranchId
    ? sql`AND (branch_id IS NULL OR branch_id = ${effectiveBranchId})`
    : sql``;
  const { rows } = await db.execute(sql`
    SELECT id, name, status
    FROM marketing_campaigns
    WHERE restaurant_id = ${restaurantId}
      AND segment_id = ${segmentId}
      AND status IN ('draft', 'scheduled', 'sending')
      ${branchClause}
    ORDER BY
      CASE status WHEN 'sending' THEN 0 WHEN 'scheduled' THEN 1 ELSE 2 END,
      created_at DESC
  `);
  return (rows as Array<{ id: string; name: string; status: LinkedActiveCampaignStatus }>).map((r) => ({
    id: r.id, name: r.name, status: r.status,
  }));
}

/**
 * Delete a saved audience.
 *
 * Before the row is dropped we re-snapshot its rules onto every still-active
 * campaign that references it (status IN draft / scheduled / sending) and
 * stamp `segment_deleted_at` so the campaigns list can show a "segment
 * deleted" badge. Cancelled/failed/completed campaigns are left alone — they
 * will never send again, so their snapshot stays as it was. Without this
 * step the schema's `ON DELETE SET NULL` would null the link, but a campaign
 * whose audience_rules snapshot was never refreshed after a segment edit
 * would silently fan out to zero recipients. Wrapping the snapshot + delete
 * in one transaction guarantees we never race the cascade.
 *
 * Returns the IDs of campaigns that were auto-detached so callers can show
 * a helpful confirmation message.
 */
export async function deleteSegment(
  restaurantId: string,
  id: string,
  /** Caller's effective branch — pinned staff can only delete segments
   *  visible to them (their branch's own + restaurant-wide segments are
   *  excluded for pinned users via the same scope clause). */
  effectiveBranchId: string | null = null,
): Promise<{ detachedCampaignIds: string[] }> {
  await initDatabase();
  const existing = await getSegment(restaurantId, id, effectiveBranchId);
  if (!existing) return { detachedCampaignIds: [] };

  // Defer the import so segments.service ↔ campaigns.service don't form a
  // module-init cycle (campaigns.service already imports from this file).
  const { snapshotSegmentOntoCampaigns } = await import('./campaigns.service');

  return db.transaction(async (tx) => {
    const detachedCampaignIds = await snapshotSegmentOntoCampaigns(
      tx, restaurantId, id, existing.rules,
    );
    await tx.execute(sql`
      DELETE FROM marketing_segments
      WHERE restaurant_id = ${restaurantId} AND id = ${id}
    `);
    return { detachedCampaignIds };
  });
}

// ─── Evaluator ─────────────────────────────────────────────────────────────

/**
 * Compose the WHERE-clause fragments for a given rule set. Returns the
 * fragment list along with the implicit channel constraint so the caller
 * can decide whether they want a count, sample preview, or full recipient
 * list. All fragments are parametrised — no string concatenation of caller
 * input ever reaches the SQL.
 */
function compileWhere(restaurantId: string, rules: AudienceRules): SQL[] {
  const parts: SQL[] = [];
  parts.push(sql`c.restaurant_id = ${restaurantId}`);

  if (!rules.includeOptedOut) {
    parts.push(sql`c.marketing_opt_out = false`);
  }

  // Channel reachability — drives audience size more than any other field.
  if (rules.channel === 'email') {
    parts.push(sql`c.email IS NOT NULL AND c.email <> ''`);
  } else if (rules.channel === 'whatsapp') {
    parts.push(sql`c.phone IS NOT NULL AND c.phone <> ''`);
  } else {
    // 'any' — at least one contact path must exist
    parts.push(sql`((c.email IS NOT NULL AND c.email <> '') OR (c.phone IS NOT NULL AND c.phone <> ''))`);
  }

  if (typeof rules.orderCountMin === 'number') {
    parts.push(sql`COALESCE(c.total_visits, 0) >= ${rules.orderCountMin}`);
  }
  if (typeof rules.lifetimeSpendMin === 'number') {
    parts.push(sql`COALESCE(c.total_spend, 0) >= ${rules.lifetimeSpendMin}`);
  }

  if (Array.isArray(rules.tags) && rules.tags.length > 0) {
    parts.push(sql`c.tags ?| ${rules.tags}::text[]`);
  }

  // Branch + recency are an EXISTS join on orders — keeps the customer table
  // index-friendly even with millions of orders.
  const branchIds = (rules.branchIds ?? []).filter((b) => typeof b === 'string' && b.length > 0);
  const recencyDays = rules.orderRecencyDays;
  if (branchIds.length > 0 || (typeof recencyDays === 'number' && recencyDays > 0)) {
    const condParts: SQL[] = [
      sql`o.restaurant_id = c.restaurant_id`,
      sql`o.customer_id = c.id`,
    ];
    if (branchIds.length > 0) {
      condParts.push(sql`o.branch_id = ANY(${bindValue(branchIds)}::uuid[])`);
    }
    if (typeof recencyDays === 'number' && recencyDays > 0) {
      condParts.push(sql`o.created_at >= NOW() - (${recencyDays} || ' days')::interval`);
    }
    const exists = sql`EXISTS (SELECT 1 FROM orders o WHERE ${sql.join(condParts, sql` AND `)})`;
    parts.push(exists);
  }

  return parts;
}

export async function previewSegmentCount(restaurantId: string, rulesRaw: unknown): Promise<number> {
  await initDatabase();
  const rules = normalizeRules(rulesRaw);
  const where = compileWhere(restaurantId, rules);
  const { rows } = await db.execute(sql`
    SELECT COUNT(*)::int AS n FROM customers c WHERE ${sql.join(where, sql` AND `)}
  `);
  return ((rows[0] as { n?: number })?.n) ?? 0;
}

export async function previewSegmentSample(
  restaurantId: string, rulesRaw: unknown, limit = 25
): Promise<RecipientPreviewItem[]> {
  await initDatabase();
  const rules = normalizeRules(rulesRaw);
  const cap = Math.min(Math.max(limit, 1), 100);
  const where = compileWhere(restaurantId, rules);
  const { rows } = await db.execute(sql`
    SELECT c.id AS customer_id, c.name, c.email, c.phone
    FROM customers c WHERE ${sql.join(where, sql` AND `)}
    ORDER BY c.created_at DESC
    LIMIT ${cap}
  `);
  return (rows as Array<{ customer_id: string; name: string; email: string | null; phone: string | null }>)
    .map((r) => ({ customer_id: r.customer_id, name: r.name, email: r.email, phone: r.phone }));
}

/**
 * Resolve a rule set into the full recipient list. Returns one row per
 * customer whose contact path matches the requested channel — `recipient`
 * is the email or E.164 phone the runner should target.
 */
export async function resolveRecipients(
  restaurantId: string, rulesRaw: unknown, channel: 'email' | 'whatsapp'
): Promise<Array<{ customer_id: string; name: string; recipient: string }>> {
  await initDatabase();
  // Force the rule set to the campaign's channel before resolving — we never
  // try to whatsapp an email-only segment.
  const rules: AudienceRules = { ...normalizeRules(rulesRaw), channel };
  const where = compileWhere(restaurantId, rules);
  const contactCol = channel === 'email' ? sql`c.email` : sql`c.phone`;
  const { rows } = await db.execute(sql`
    SELECT c.id AS customer_id, c.name, ${contactCol} AS recipient
    FROM customers c WHERE ${sql.join(where, sql` AND `)}
    ORDER BY c.id
  `);
  return rows as Array<{ customer_id: string; name: string; recipient: string }>;
}

/**
 * Server-side fan-out: inserts one campaign_delivery row per matching customer
 * WITHOUT pulling recipients into Node.js memory. The INSERT...SELECT runs
 * entirely in Postgres — safe for 50k+ audiences.
 *
 * Returns the number of newly-inserted rows (conflicts are silently skipped
 * by the ON CONFLICT DO NOTHING clause so re-runs are idempotent).
 */
export async function fanOutInsertDirect(
  campaignId: string,
  restaurantId: string,
  rulesRaw: unknown,
  channel: 'email' | 'whatsapp',
): Promise<{ inserted: number; audienceCount: number }> {
  await initDatabase();
  const rules: AudienceRules = { ...normalizeRules(rulesRaw), channel };
  const where = compileWhere(restaurantId, rules);
  const contactCol = channel === 'email' ? sql`c.email` : sql`c.phone`;

  const { rowCount } = await db.execute(sql`
    INSERT INTO campaign_deliveries (
      campaign_id, restaurant_id, customer_id, channel, recipient, status, scheduled_for
    )
    SELECT
      ${campaignId}::uuid,
      ${restaurantId}::uuid,
      c.id,
      ${channel},
      ${contactCol},
      'queued',
      now()
    FROM customers c
    WHERE ${sql.join(where, sql` AND `)}
    ON CONFLICT (campaign_id, customer_id, recipient) DO NOTHING
  `);
  const inserted = rowCount ?? 0;

  // Count total deliveries for this campaign — accurate for both first runs
  // and idempotent re-runs where rowCount would undercount.
  const { rows: cRows } = await db.execute(sql`
    SELECT COUNT(*)::int AS n FROM campaign_deliveries WHERE campaign_id = ${campaignId}
  `);
  const audienceCount = ((cRows[0] as { n?: number })?.n) ?? inserted;

  return { inserted, audienceCount };
}