package controlplane

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	bootstate "github.com/nexuscrm/db/pkg/bootstate"
)

const migrationIdBackfillProcessInstanceHistory = "20260522_backfill_process_instance_history"

// BackfillProcessInstanceHistory materializes durable approval history rows for
// orgs that already had ProcessInstance/ProcessInstanceWorkitem data before
// ProcessInstanceHistory became the authoritative StepsAndWorkitems source.
func BackfillProcessInstanceHistory(ctx context.Context, db *sql.DB) (int, error) {
	applied, err := bootstate.MigrationApplied(ctx, db, migrationIdBackfillProcessInstanceHistory)
	if err != nil {
		return 0, err
	}
	if applied {
		log.Printf("   ⏭  BackfillProcessInstanceHistory: already applied; skipping")
		return 0, nil
	}

	ready, err := processInstanceHistoryBackfillTablesReady(ctx, db)
	if err != nil {
		return 0, err
	}
	if !ready {
		return 0, fmt.Errorf("BackfillProcessInstanceHistory: required approval-history tables missing after reconciliation")
	}

	total := 0
	for _, stmt := range []struct {
		name string
		sql  string
	}{
		{"target object type repair", processInstanceHistoryTargetObjectTypeBackfillSQL},
		{"started", processInstanceHistoryStartedBackfillSQL},
		{"completed nodes", processInstanceHistoryCompletedNodeBackfillSQL},
		{"terminal instances", processInstanceHistoryTerminalInstanceBackfillSQL},
		{"pending workitems", processInstanceHistoryPendingWorkitemBackfillSQL},
	} {
		res, execErr := db.ExecContext(ctx, stmt.sql)
		if execErr != nil {
			return total, fmt.Errorf("BackfillProcessInstanceHistory %s: %w", stmt.name, execErr)
		}
		affected, rowsErr := res.RowsAffected()
		if rowsErr != nil {
			return total, fmt.Errorf("BackfillProcessInstanceHistory %s rows affected: %w", stmt.name, rowsErr)
		}
		total += int(affected)
	}

	remaining, err := processInstanceHistoryBackfillRemaining(ctx, db)
	if err != nil {
		return total, err
	}
	if remaining != 0 {
		return total, fmt.Errorf("BackfillProcessInstanceHistory: %d legacy approval row(s) remain without ProcessInstanceHistory coverage", remaining)
	}

	if err := bootstate.RecordMigrationApplied(
		ctx, db,
		migrationIdBackfillProcessInstanceHistory,
		1,
		"Backfill ProcessInstanceHistory rows from legacy approval runtime tables before StepsAndWorkitems cutover.",
	); err != nil {
		return total, err
	}

	if total > 0 {
		log.Printf("   🧼 BackfillProcessInstanceHistory: materialized %d approval history row(s)", total)
	}
	return total, nil
}

func processInstanceHistoryBackfillTablesReady(ctx context.Context, db *sql.DB) (bool, error) {
	var count int
	if err := db.QueryRowContext(ctx, `
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN (
    'ProcessInstance',
    'ProcessDefinition',
    'ProcessInstanceHistory',
    'ProcessInstanceNode',
    'ProcessInstanceWorkitem'
  )`).Scan(&count); err != nil {
		return false, fmt.Errorf("processInstanceHistoryBackfillTablesReady: %w", err)
	}
	return count == 5, nil
}

func processInstanceHistoryBackfillRemaining(ctx context.Context, db *sql.DB) (int, error) {
	var remaining int
	if err := db.QueryRowContext(ctx, processInstanceHistoryBackfillClosureSQL).Scan(&remaining); err != nil {
		return 0, fmt.Errorf("BackfillProcessInstanceHistory closure check: %w", err)
	}
	return remaining, nil
}

const processInstanceHistoryTargetObjectTypeBackfillSQL = `
UPDATE ProcessInstanceHistory h
JOIN ProcessInstance pi
  ON pi.Id = h.ProcessInstanceId
JOIN ProcessDefinition pd
  ON pd.Id = pi.ProcessDefinitionId
SET
  h.TargetObjectId = COALESCE(NULLIF(h.TargetObjectId, ''), pi.TargetObjectId),
  h.TargetObjectType = pd.TableEnumOrId,
  h.LastModifiedDate = NOW()
WHERE COALESCE(h.TargetObjectType, '') = ''
   OR COALESCE(h.TargetObjectId, '') = ''`

const processInstanceHistoryStartedBackfillSQL = `
INSERT INTO ProcessInstanceHistory (
  Id, ProcessInstanceId, TargetObjectId, TargetObjectType, StepStatus, ActorId,
  OriginalActorId, Comments, IsPending, CreatedDate, LastModifiedDate
)
SELECT
  CONCAT('04h-start-', pi.Id),
  pi.Id,
  pi.TargetObjectId,
  pd.TableEnumOrId,
  'Started',
  COALESCE(NULLIF(pi.SubmittedById, ''), NULLIF(pi.CreatedById, ''), 'system'),
  COALESCE(NULLIF(pi.SubmittedById, ''), NULLIF(pi.CreatedById, ''), 'system'),
  COALESCE(wic.SubmitComments, ''),
  0,
  COALESCE(pi.CreatedDate, NOW()),
  NOW()
FROM ProcessInstance pi
LEFT JOIN (
  SELECT ProcessInstanceId, MIN(NULLIF(Comments, '')) AS SubmitComments
  FROM ProcessInstanceWorkitem
  WHERE Comments IS NOT NULL
    AND Comments <> ''
  GROUP BY ProcessInstanceId
) wic
  ON wic.ProcessInstanceId = pi.Id
JOIN ProcessDefinition pd
  ON pd.Id = pi.ProcessDefinitionId
WHERE NOT EXISTS (
  SELECT 1
  FROM ProcessInstanceHistory h
  WHERE h.ProcessInstanceId = pi.Id
    AND h.StepStatus = 'Started'
)`

const processInstanceHistoryCompletedNodeBackfillSQL = `
INSERT INTO ProcessInstanceHistory (
  Id, ProcessInstanceId, StepNodeId, ProcessNodeId, TargetObjectId, TargetObjectType,
  StepStatus, ActorId, OriginalActorId, Comments, IsPending,
  CreatedDate, LastModifiedDate
)
SELECT
  CONCAT('04h-node-', pin.Id),
  pin.ProcessInstanceId,
  pin.Id,
  pin.ProcessNodeId,
  pi.TargetObjectId,
  pd.TableEnumOrId,
  pin.NodeStatus,
  COALESCE(NULLIF(pin.LastActorId, ''), NULLIF(pi.SubmittedById, ''), NULLIF(pi.CreatedById, ''), 'system'),
  COALESCE(NULLIF(pin.LastActorId, ''), NULLIF(pi.SubmittedById, ''), NULLIF(pi.CreatedById, ''), 'system'),
  COALESCE(pin.Comments, ''),
  0,
  COALESCE(pin.CompletedDate, pin.CreatedDate, NOW()),
  NOW()
FROM ProcessInstanceNode pin
JOIN ProcessInstance pi
  ON pi.Id = pin.ProcessInstanceId
JOIN ProcessDefinition pd
  ON pd.Id = pi.ProcessDefinitionId
WHERE COALESCE(pin.NodeStatus, '') <> ''
  AND pin.NodeStatus <> 'Pending'
  AND NOT EXISTS (
    SELECT 1
    FROM ProcessInstanceHistory h
    WHERE h.StepNodeId = pin.Id
      AND h.StepStatus = pin.NodeStatus
      AND COALESCE(h.IsPending, 0) = 0
  )`

const processInstanceHistoryTerminalInstanceBackfillSQL = `
INSERT INTO ProcessInstanceHistory (
  Id, ProcessInstanceId, TargetObjectId, TargetObjectType, StepStatus, ActorId,
  OriginalActorId, Comments, IsPending, CreatedDate, LastModifiedDate
)
SELECT
  CONCAT('04h-terminal-', pi.Id),
  pi.Id,
  pi.TargetObjectId,
  pd.TableEnumOrId,
  pi.Status,
  COALESCE(NULLIF(pi.SubmittedById, ''), NULLIF(pi.CreatedById, ''), 'system'),
  COALESCE(NULLIF(pi.SubmittedById, ''), NULLIF(pi.CreatedById, ''), 'system'),
  COALESCE(wic.SubmitComments, ''),
  0,
  COALESCE(pi.CompletedDate, pi.LastModifiedDate, pi.CreatedDate, NOW()),
  NOW()
FROM ProcessInstance pi
LEFT JOIN (
  SELECT ProcessInstanceId, MIN(NULLIF(Comments, '')) AS SubmitComments
  FROM ProcessInstanceWorkitem
  WHERE Comments IS NOT NULL
    AND Comments <> ''
  GROUP BY ProcessInstanceId
) wic
  ON wic.ProcessInstanceId = pi.Id
JOIN ProcessDefinition pd
  ON pd.Id = pi.ProcessDefinitionId
WHERE COALESCE(pi.Status, '') <> ''
  AND pi.Status <> 'Pending'
  AND NOT EXISTS (
    SELECT 1
    FROM ProcessInstanceNode pin
    WHERE pin.ProcessInstanceId = pi.Id
  )
  AND NOT EXISTS (
    SELECT 1
    FROM ProcessInstanceWorkitem wi
    WHERE wi.ProcessInstanceId = pi.Id
  )
  AND NOT EXISTS (
    SELECT 1
    FROM ProcessInstanceHistory h
    WHERE h.ProcessInstanceId = pi.Id
      AND h.StepStatus = pi.Status
      AND COALESCE(h.IsPending, 0) = 0
  )`

// Pending ProcessInstanceHistory rows intentionally borrow the live workitem's
// 04i Id. While the table's own keyPrefix is 04h for durable synthetic events,
// sharing the pending row Id keeps the legacy workitem bridge and PIH pending
// projection deduped to one runtime fact.
const processInstanceHistoryPendingWorkitemBackfillSQL = `
INSERT INTO ProcessInstanceHistory (
  Id, ProcessInstanceId, StepNodeId, ProcessNodeId, TargetObjectId, TargetObjectType,
  StepStatus, ActorId, OriginalActorId, Comments, IsPending,
  CreatedDate, LastModifiedDate
)
SELECT
  wi.Id,
  wi.ProcessInstanceId,
  pn.StepNodeId,
  pn.ProcessNodeId,
  pi.TargetObjectId,
  pd.TableEnumOrId,
  'Pending',
  wi.ActorId,
  COALESCE(NULLIF(wi.OriginalActorId, ''), wi.ActorId),
  COALESCE(wi.Comments, ''),
  1,
  COALESCE(wi.CreatedDate, NOW()),
  COALESCE(wi.LastModifiedDate, NOW())
FROM ProcessInstanceWorkitem wi
JOIN ProcessInstance pi
  ON pi.Id = wi.ProcessInstanceId
JOIN ProcessDefinition pd
  ON pd.Id = pi.ProcessDefinitionId
LEFT JOIN (
  SELECT
    ProcessInstanceId,
    MIN(Id) AS StepNodeId,
    MIN(ProcessNodeId) AS ProcessNodeId
  FROM ProcessInstanceNode
  WHERE NodeStatus = 'Pending'
  GROUP BY ProcessInstanceId
) pn
  ON pn.ProcessInstanceId = wi.ProcessInstanceId
WHERE NOT EXISTS (
  SELECT 1
  FROM ProcessInstanceHistory h
  WHERE h.Id = wi.Id
)`

const processInstanceHistoryBackfillClosureSQL = `
SELECT
  (
    SELECT COUNT(*)
    FROM ProcessInstanceHistory h
    JOIN ProcessInstance pi
      ON pi.Id = h.ProcessInstanceId
    WHERE COALESCE(h.TargetObjectType, '') = ''
       OR COALESCE(h.TargetObjectId, '') = ''
  ) +
  (
    SELECT COUNT(*)
    FROM ProcessInstance pi
    WHERE NOT EXISTS (
      SELECT 1
      FROM ProcessInstanceHistory h
      WHERE h.ProcessInstanceId = pi.Id
        AND h.StepStatus = 'Started'
    )
  ) +
  (
    SELECT COUNT(*)
    FROM ProcessInstanceNode pin
    WHERE COALESCE(pin.NodeStatus, '') <> ''
      AND pin.NodeStatus <> 'Pending'
      AND NOT EXISTS (
        SELECT 1
        FROM ProcessInstanceHistory h
        WHERE h.StepNodeId = pin.Id
          AND h.StepStatus = pin.NodeStatus
          AND COALESCE(h.IsPending, 0) = 0
      )
  ) +
  (
    SELECT COUNT(*)
    FROM ProcessInstance pi
    WHERE COALESCE(pi.Status, '') <> ''
      AND pi.Status <> 'Pending'
      AND NOT EXISTS (
        SELECT 1
        FROM ProcessInstanceNode pin
        WHERE pin.ProcessInstanceId = pi.Id
      )
      AND NOT EXISTS (
        SELECT 1
        FROM ProcessInstanceWorkitem wi
        WHERE wi.ProcessInstanceId = pi.Id
      )
      AND NOT EXISTS (
        SELECT 1
        FROM ProcessInstanceHistory h
        WHERE h.ProcessInstanceId = pi.Id
          AND h.StepStatus = pi.Status
          AND COALESCE(h.IsPending, 0) = 0
      )
  ) +
  (
    SELECT COUNT(*)
    FROM ProcessInstanceWorkitem wi
    WHERE NOT EXISTS (
      SELECT 1
      FROM ProcessInstanceHistory h
      WHERE h.Id = wi.Id
        AND h.StepStatus = 'Pending'
    )
  ) AS remaining`