/**
 * Instruct-measure service (issue #253)
 *
 * Implements the headline approver flow: an approver instructs a measure
 * that the coordinator did not propose, picking from the canonical
 * `MEASURE_NAMES` catalogue. The instruction is recorded locally and
 * pushed back to HubSpot.
 *
 * Single DB transaction:
 *   1. insert a `user_defined_deal_measures` row with `source = "instructed"`
 *   2. insert a `deal_measure_approvals` row with `is_approved = true`
 *   3. insert a corresponding `deal_measure_approval_events` row
 *   4. read the deal's current `proposed_measures` list
 *   5. read all currently approved measure names for the deal
 *
 * After the transaction commits, push three HubSpot fields:
 *   - `instructed_measures`: full list of all instructed measures (audit trail)
 *   - `proposed_measures`: existing list merged with the new measure (always)
 *   - `approved_measures`: all currently approved measures for the deal
 *
 * HubSpot push failures DO NOT roll back the DB. Successful pushes stamp
 * `pushed_at` on the new local row; failures leave it null so a follow-up
 * reconcile job can retry.
 */
import { and, eq } from "drizzle-orm";
import { db } from "@/app/db/db";
import { hubspotDealData } from "@/app/db/schema/crm/hubspot_deal_table";
import {
  dealMeasureApprovals,
  dealMeasureApprovalEvents,
} from "@/app/db/schema/approvals";
import { userDefinedDealMeasures } from "@/app/db/schema/user_defined_deal_measures";
import {
  MEASURE_NAMES,
  type MeasureName,
} from "@/app/lib/measureDocumentRequirements";
import { parseMeasures } from "@/app/lib/parseMeasures";
import { syncMeasuresFieldToHubSpot as defaultSyncMeasuresField } from "@/app/lib/hubspot/dealSync";

export const INSTRUCTED_MEASURES_PROP = "instructed_measures";
export const PROPOSED_MEASURES_PROP = "proposed_measures";
export const APPROVED_MEASURES_PROP = "approved_measures";

/** Snapshot returned by the transactional step of the service. */
export interface InstructTxOutcome {
  instructedRowId: bigint;
  /** Existing proposed measures from hubspot_deal_data before this instruction. */
  existingProposedMeasures: string[];
  /** All approved measure names for the deal after this instruction's approval row is upserted. */
  allApprovedMeasureNames: string[];
}

export type SyncMeasuresField = typeof defaultSyncMeasuresField;

export type RunInstructTx = (params: {
  dealId: string;
  measureName: MeasureName;
  userId: bigint;
  notes: string | null;
}) => Promise<InstructTxOutcome>;

export type ReadInstructedMeasureNames = (
  dealId: string,
) => Promise<string[]>;

export type StampPushedAt = (rowId: bigint) => Promise<void>;

export type InstructMeasureResult =
  | {
      ok: true;
      instructedRowId: bigint;
      hubspotSync: "ok" | "failed";
      hubspotError?: string;
    }
  | { ok: false; error: string };

export interface InstructMeasureInput {
  dealId: string;
  measureName: string;
  userId: bigint;
  notes?: string;
  /**
   * Hooks for the unit tests so the service stays environment-free. The
   * route wires the real DB + HubSpot client through.
   */
  deps?: {
    runInstructTx?: RunInstructTx;
    readInstructedMeasureNames?: ReadInstructedMeasureNames;
    syncMeasuresField?: SyncMeasuresField;
    stampPushedAt?: StampPushedAt;
  };
}

function isMeasureName(value: string): value is MeasureName {
  return (MEASURE_NAMES as ReadonlyArray<string>).includes(value);
}

// ---------------------------------------------------------------------------
// Default DB-backed implementations of the injectable hooks. The real route
// uses these; tests substitute lightweight in-memory versions.
// ---------------------------------------------------------------------------
const defaultRunInstructTx: RunInstructTx = async ({
  dealId,
  measureName,
  userId,
  notes,
}) => {
  return await db.transaction(async (tx) => {
    const inserted = await tx
      .insert(userDefinedDealMeasures)
      .values({
        hubspotDealId: dealId,
        measureName,
        source: "instructed",
        createdByUserId: userId,
        notes,
      })
      .returning({ id: userDefinedDealMeasures.id });
    const instructedRowId = inserted[0]?.id;
    if (instructedRowId === undefined || instructedRowId === null) {
      throw new Error("Failed to insert user_defined_deal_measures row");
    }

    // Approval row — keep one row per (deal, measure). The unique
    // constraint is on (hubspot_deal_id, measure_name); a duplicate
    // instruction for an already-approved measure refreshes the row but
    // still produces a new event + a new instructed row.
    await tx
      .insert(dealMeasureApprovals)
      .values({
        hubspotDealId: dealId,
        measureName,
        isApproved: true,
        approvedBy: userId,
      })
      .onConflictDoUpdate({
        target: [
          dealMeasureApprovals.hubspotDealId,
          dealMeasureApprovals.measureName,
        ],
        set: {
          isApproved: true,
          approvedBy: userId,
          approvedAt: new Date(),
        },
      });

    await tx.insert(dealMeasureApprovalEvents).values({
      hubspotDealId: dealId,
      measureName,
      action: "approved",
      actedBy: userId,
    });

    const dealRows = await tx
      .select({ proposedMeasures: hubspotDealData.proposedMeasures })
      .from(hubspotDealData)
      .where(eq(hubspotDealData.dealId, dealId))
      .limit(1);
    const existingProposedMeasures = parseMeasures(dealRows[0]?.proposedMeasures ?? null);

    const approvedRows = await tx
      .select({ measureName: dealMeasureApprovals.measureName })
      .from(dealMeasureApprovals)
      .where(
        and(
          eq(dealMeasureApprovals.hubspotDealId, dealId),
          eq(dealMeasureApprovals.isApproved, true),
        ),
      );
    const allApprovedMeasureNames = approvedRows.map((r) => r.measureName);

    return { instructedRowId, existingProposedMeasures, allApprovedMeasureNames };
  });
};

const defaultReadInstructedMeasureNames: ReadInstructedMeasureNames = async (
  dealId,
) => {
  const rows = await db
    .select({ measureName: userDefinedDealMeasures.measureName })
    .from(userDefinedDealMeasures)
    .where(
      and(
        eq(userDefinedDealMeasures.hubspotDealId, dealId),
        eq(userDefinedDealMeasures.source, "instructed"),
      ),
    );
  return rows.map((r) => r.measureName);
};

const defaultStampPushedAt: StampPushedAt = async (rowId) => {
  await db
    .update(userDefinedDealMeasures)
    .set({ pushedAt: new Date() })
    .where(eq(userDefinedDealMeasures.id, rowId));
};

// ---------------------------------------------------------------------------
// Batch (plural) types
// ---------------------------------------------------------------------------

export interface InstructMeasuresTxOutcome {
  instructedRowIds: bigint[];
  existingProposedMeasures: string[];
  allApprovedMeasureNames: string[];
}

export type RunInstructMeasuresTx = (params: {
  dealId: string;
  measureNames: MeasureName[];
  userId: bigint;
  notes: string | null;
}) => Promise<InstructMeasuresTxOutcome>;

export type InstructMeasuresResult =
  | {
      ok: true;
      instructedRowIds: bigint[];
      hubspotSync: "ok" | "failed";
      hubspotError?: string;
    }
  | { ok: false; error: string };

export interface InstructMeasuresInput {
  dealId: string;
  measureNames: string[];
  userId: bigint;
  notes?: string;
  deps?: {
    runInstructMeasuresTx?: RunInstructMeasuresTx;
    readInstructedMeasureNames?: ReadInstructedMeasureNames;
    syncMeasuresField?: SyncMeasuresField;
    stampPushedAt?: StampPushedAt;
  };
}

const defaultRunInstructMeasuresTx: RunInstructMeasuresTx = async ({
  dealId,
  measureNames,
  userId,
  notes,
}) => {
  return await db.transaction(async (tx) => {
    const instructedRowIds: bigint[] = [];

    for (const measureName of measureNames) {
      const inserted = await tx
        .insert(userDefinedDealMeasures)
        .values({
          hubspotDealId: dealId,
          measureName,
          source: "instructed",
          createdByUserId: userId,
          notes,
        })
        .returning({ id: userDefinedDealMeasures.id });
      const rowId = inserted[0]?.id;
      if (rowId === undefined || rowId === null) {
        throw new Error("Failed to insert user_defined_deal_measures row");
      }
      instructedRowIds.push(rowId);

      await tx
        .insert(dealMeasureApprovals)
        .values({
          hubspotDealId: dealId,
          measureName,
          isApproved: true,
          approvedBy: userId,
        })
        .onConflictDoUpdate({
          target: [
            dealMeasureApprovals.hubspotDealId,
            dealMeasureApprovals.measureName,
          ],
          set: {
            isApproved: true,
            approvedBy: userId,
            approvedAt: new Date(),
          },
        });

      await tx.insert(dealMeasureApprovalEvents).values({
        hubspotDealId: dealId,
        measureName,
        action: "approved",
        actedBy: userId,
      });
    }

    const dealRows = await tx
      .select({ proposedMeasures: hubspotDealData.proposedMeasures })
      .from(hubspotDealData)
      .where(eq(hubspotDealData.dealId, dealId))
      .limit(1);
    const existingProposedMeasures = parseMeasures(dealRows[0]?.proposedMeasures ?? null);

    const approvedRows = await tx
      .select({ measureName: dealMeasureApprovals.measureName })
      .from(dealMeasureApprovals)
      .where(
        and(
          eq(dealMeasureApprovals.hubspotDealId, dealId),
          eq(dealMeasureApprovals.isApproved, true),
        ),
      );
    const allApprovedMeasureNames = approvedRows.map((r) => r.measureName);

    return { instructedRowIds, existingProposedMeasures, allApprovedMeasureNames };
  });
};

export async function instructMeasures(
  input: InstructMeasuresInput,
): Promise<InstructMeasuresResult> {
  if (input.measureNames.length === 0) {
    return { ok: false, error: "measureNames must not be empty" };
  }

  const validatedNames: MeasureName[] = [];
  for (const name of input.measureNames) {
    const trimmed = name.trim();
    if (!isMeasureName(trimmed)) {
      return { ok: false, error: `Unknown measure: ${trimmed}` };
    }
    validatedNames.push(trimmed);
  }

  const runInstructMeasuresTx =
    input.deps?.runInstructMeasuresTx ?? defaultRunInstructMeasuresTx;
  const readInstructed =
    input.deps?.readInstructedMeasureNames ?? defaultReadInstructedMeasureNames;
  const syncMeasuresField =
    input.deps?.syncMeasuresField ?? defaultSyncMeasuresField;
  const stampPushedAt = input.deps?.stampPushedAt ?? defaultStampPushedAt;

  let txResult: InstructMeasuresTxOutcome;
  try {
    txResult = await runInstructMeasuresTx({
      dealId: input.dealId,
      measureNames: validatedNames,
      userId: input.userId,
      notes: input.notes ?? null,
    });
  } catch (err) {
    const message =
      err instanceof Error ? err.message : "Failed to instruct measures";
    console.error("[instructMeasures] transaction failed", {
      dealId: input.dealId,
      measureNames: validatedNames,
      error: err,
    });
    return { ok: false, error: message };
  }

  const allInstructed = await readInstructed(input.dealId);

  const mergedProposed = Array.from(
    new Set([...txResult.existingProposedMeasures, ...validatedNames]),
  );

  const instructedSync = await syncMeasuresField({
    hubspotDealId: input.dealId,
    propName: INSTRUCTED_MEASURES_PROP,
    measureNames: allInstructed,
  });

  const proposedSync = await syncMeasuresField({
    hubspotDealId: input.dealId,
    propName: PROPOSED_MEASURES_PROP,
    measureNames: mergedProposed,
  });

  const approvedSync = await syncMeasuresField({
    hubspotDealId: input.dealId,
    propName: APPROVED_MEASURES_PROP,
    measureNames: txResult.allApprovedMeasureNames,
  });

  const overallOk = instructedSync.ok && proposedSync.ok && approvedSync.ok;

  if (overallOk) {
    for (const rowId of txResult.instructedRowIds) {
      try {
        await stampPushedAt(rowId);
      } catch (err) {
        console.error("[instructMeasures] failed to stamp pushed_at", {
          rowId: String(rowId),
          error: err,
        });
      }
    }
    return {
      ok: true,
      instructedRowIds: txResult.instructedRowIds,
      hubspotSync: "ok",
    };
  }

  const hubspotError = !instructedSync.ok
    ? instructedSync.error
    : !proposedSync.ok
      ? proposedSync.error
      : !approvedSync.ok
        ? approvedSync.error
        : "HubSpot sync failed";

  return {
    ok: true,
    instructedRowIds: txResult.instructedRowIds,
    hubspotSync: "failed",
    hubspotError,
  };
}

export async function instructMeasure(
  input: InstructMeasureInput,
): Promise<InstructMeasureResult> {
  const measureName = input.measureName.trim();
  if (!measureName) {
    return { ok: false, error: "measureName is required" };
  }
  if (!isMeasureName(measureName)) {
    return { ok: false, error: `Unknown measure: ${measureName}` };
  }

  const runInstructTx = input.deps?.runInstructTx ?? defaultRunInstructTx;
  const readInstructed =
    input.deps?.readInstructedMeasureNames ??
    defaultReadInstructedMeasureNames;
  const syncMeasuresField =
    input.deps?.syncMeasuresField ?? defaultSyncMeasuresField;
  const stampPushedAt = input.deps?.stampPushedAt ?? defaultStampPushedAt;

  // ---------------------------------------------------------------------
  // DB transaction. Any throw here rolls everything back — no row is
  // created, no approval is logged, no HubSpot push happens.
  // ---------------------------------------------------------------------
  let txResult: InstructTxOutcome;
  try {
    txResult = await runInstructTx({
      dealId: input.dealId,
      measureName,
      userId: input.userId,
      notes: input.notes ?? null,
    });
  } catch (err) {
    const message =
      err instanceof Error ? err.message : "Failed to instruct measure";
    console.error("[instructMeasure] transaction failed", {
      dealId: input.dealId,
      measureName,
      error: err,
    });
    return { ok: false, error: message };
  }

  // ---------------------------------------------------------------------
  // Post-commit: push to HubSpot. Failures here do NOT roll back the DB;
  // `pushed_at` simply stays null so a reconcile job can retry.
  // ---------------------------------------------------------------------
  const allInstructed = await readInstructed(input.dealId);

  const mergedProposed = Array.from(
    new Set([...txResult.existingProposedMeasures, measureName]),
  );

  const instructedSync = await syncMeasuresField({
    hubspotDealId: input.dealId,
    propName: INSTRUCTED_MEASURES_PROP,
    measureNames: allInstructed,
  });

  const proposedSync = await syncMeasuresField({
    hubspotDealId: input.dealId,
    propName: PROPOSED_MEASURES_PROP,
    measureNames: mergedProposed,
  });

  const approvedSync = await syncMeasuresField({
    hubspotDealId: input.dealId,
    propName: APPROVED_MEASURES_PROP,
    measureNames: txResult.allApprovedMeasureNames,
  });

  const overallOk = instructedSync.ok && proposedSync.ok && approvedSync.ok;

  if (overallOk) {
    try {
      await stampPushedAt(txResult.instructedRowId);
    } catch (err) {
      console.error("[instructMeasure] failed to stamp pushed_at", {
        rowId: String(txResult.instructedRowId),
        error: err,
      });
    }
    return {
      ok: true,
      instructedRowId: txResult.instructedRowId,
      hubspotSync: "ok",
    };
  }

  const hubspotError = !instructedSync.ok
    ? instructedSync.error
    : !proposedSync.ok
      ? proposedSync.error
      : !approvedSync.ok
        ? approvedSync.error
        : "HubSpot sync failed";

  return {
    ok: true,
    instructedRowId: txResult.instructedRowId,
    hubspotSync: "failed",
    hubspotError,
  };
}
