package controlplane

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

const connectorPackDependencyInstallIDPreflightSQL = `
SELECT COUNT(*)
  FROM (
       SELECT r.Id
         FROM _System_ConnectorInboundRoute r
         JOIN _System_InstalledPack p ON LOWER(TRIM(p.Name)) = LOWER(TRIM(r.Namespace))
        WHERE p.Status = 'active'
          AND COALESCE(p.IsDeleted, 0) = 0
          AND COALESCE(r.IsDeleted, 0) = 0
          AND COALESCE(r.IsCurrent, 0) = 1
          AND (
              COALESCE(p.PackInstallId, '') = ''
              OR COALESCE(r.InstalledPackId, '') = ''
              OR r.InstalledPackId <> p.PackInstallId
          )
       UNION ALL
       SELECT c.Id
         FROM _System_ConnectorInboundHandlerConfig c
         JOIN _System_InstalledPack p ON LOWER(TRIM(p.Name)) = LOWER(TRIM(c.Namespace))
        WHERE p.Status = 'active'
          AND COALESCE(p.IsDeleted, 0) = 0
          AND COALESCE(c.IsDeleted, 0) = 0
          AND (
              COALESCE(p.PackInstallId, '') = ''
              OR NOT EXISTS (
                  SELECT 1
                    FROM _System_ConnectorInboundHandlerConfig active_child
                   WHERE LOWER(TRIM(active_child.Namespace)) = LOWER(TRIM(c.Namespace))
                     AND active_child.CapabilityKind = c.CapabilityKind
                     AND active_child.InstalledPackId = p.PackInstallId
                     AND COALESCE(active_child.IsDeleted, 0) = 0
              )
          )
       UNION ALL
       SELECT c.Id
         FROM _System_ConnectorCalloutProfile c
         JOIN _System_InstalledPack p ON LOWER(TRIM(p.Name)) = LOWER(TRIM(c.OwnerPack))
        WHERE p.Status = 'active'
          AND COALESCE(p.IsDeleted, 0) = 0
          AND COALESCE(c.IsDeleted, 0) = 0
          AND (
              COALESCE(p.PackInstallId, '') = ''
              OR NOT EXISTS (
                  SELECT 1
                    FROM _System_ConnectorCalloutProfile active_child
                   WHERE LOWER(TRIM(active_child.OwnerPack)) = LOWER(TRIM(c.OwnerPack))
                     AND active_child.ProfileName = c.ProfileName
                     AND active_child.InstalledPackId = p.PackInstallId
                     AND COALESCE(active_child.IsDeleted, 0) = 0
              )
          )
  ) AS unresolved_connector_dependencies`

const demoteStaleConnectorInboundRouteIncarnationsSQL = `
UPDATE _System_ConnectorInboundRoute r
JOIN _System_InstalledPack p
  ON LOWER(TRIM(p.Name)) = LOWER(TRIM(r.Namespace))
 SET r.IsCurrent = 0,
     r.LastModifiedDate = NOW()
WHERE p.Status = 'active'
  AND COALESCE(p.IsDeleted, 0) = 0
  AND COALESCE(p.PackInstallId, '') <> ''
  AND COALESCE(r.IsDeleted, 0) = 0
  AND COALESCE(r.IsCurrent, 0) = 1
  AND COALESCE(r.InstalledPackId, '') <> p.PackInstallId`

// demoteStaleConnectorInboundRouteIncarnations repairs the live-routing bit
// without deleting an old route incarnation. Exact-version backlog reads stay
// available; only new traffic stops treating an obsolete install as current.
func demoteStaleConnectorInboundRouteIncarnations(ctx context.Context, db *sql.DB) error {
	if _, err := db.ExecContext(ctx, demoteStaleConnectorInboundRouteIncarnationsSQL); err != nil {
		return fmt.Errorf("demote stale connector inbound route incarnations: %w", err)
	}
	return nil
}

// requireConnectorPackDependencyInstallIDsConverged keeps completed-marker
// startup read-only for pack identities. A missing or stale connector
// dependency identity without a same-identity active child is an explicit
// full-apply prerequisite; only the full setup path may reconcile identities.
func requireConnectorPackDependencyInstallIDsConverged(ctx context.Context, db *sql.DB) error {
	var count int
	err := db.QueryRowContext(ctx, connectorPackDependencyInstallIDPreflightSQL).Scan(&count)
	if err != nil {
		return fmt.Errorf("query connector dependency install identities: %w", err)
	}
	if count != 0 {
		return fmt.Errorf("%d live connector dependency install identity value(s) are unresolved; run a full setup apply before WorkItem-only cutover", count)
	}
	return nil
}