//go:build integration

package controlplane

import (
	"context"
	"database/sql"
	"os"
	"path/filepath"
	"testing"

	"github.com/nexuscrm/shared/pkg/models"
)

func TestIssue1393InstallAndRepairWithoutPersistedRelationshipDefaults(t *testing.T) {
	ctx := context.Background()
	cfg, cleanup := newTemporaryIntegrationDatabase(t)
	defer cleanup()
	setDatabaseEnvFromConfig(t, cfg)

	conn := openIntegrationConnection(t, cfg)
	if err := InitL0(ctx, conn); err != nil {
		_ = conn.Close()
		t.Fatalf("init-l0: %v", err)
	}
	_ = conn.Close()

	conn = openIntegrationConnection(t, cfg)
	defer conn.Close()

	r, err := NewRunner(conn)
	if err != nil {
		t.Fatalf("new runner: %v", err)
	}
	if err := r.RefreshCache(ctx); err != nil {
		t.Fatalf("initial cache refresh: %v", err)
	}

	sourceDir := writeIssue1393RelationshipResolverPackSource(t)
	source := "local_path:" + sourceDir
	if err := InstallPackFromSource(ctx, r, source); err != nil {
		t.Fatalf("install source-backed pack: %v", err)
	}
	if err := VerifyInstalledPackByName(ctx, conn.DB(), "issue-1393-relationship-smoke", true); err != nil {
		t.Fatalf("VerifyInstalledPackByName after install: %v", err)
	}

	assertRelationshipRowCount(t, conn.DB(), "Contact", "AccountId", "Account", 0)
	if got, ok := r.SvcMgr.Metadata().ForeignKeyForRelationship(ctx, "Contact", "Account"); !ok || got != "AccountId" {
		t.Fatalf("ForeignKeyForRelationship(Contact, Account) = (%q, %v), want (AccountId, true)", got, ok)
	}
	children := r.SvcMgr.Metadata().GetChildRelationships(ctx, "Account")
	if !containsChildSchema(children, "Contact") {
		t.Fatalf("GetChildRelationships(Account) = %#v, want Contact child", children)
	}
	if _, err := r.SvcMgr.FormulaView().PreviewDDL(ctx, "Contact"); err != nil {
		t.Fatalf("PreviewDDL(Contact): %v", err)
	}
	if err := r.SvcMgr.FormulaView().RebuildView(ctx, "Contact"); err != nil {
		t.Fatalf("RebuildView(Contact): %v", err)
	}
	assertRelationshipRowCount(t, conn.DB(), "Contact", "AccountId", "Account", 0)
	assertViewQueryable(t, conn.DB(), "v_contact", "AccountName__c")

	const overlayLabel = "Customers"
	const overlayFields = `["Name"]`
	if _, err := conn.DB().ExecContext(ctx, `
		INSERT INTO _System_Relationship
			(Id, ChildObjectApiName, ParentObjectApiName, FieldApiName, RelationshipName, RelatedListLabel, RelatedListFields, CreatedDate, LastModifiedDate)
		VALUES
			('rel_contact_account_overlay', 'Contact', 'Account', 'AccountId', 'Account', ?, ?, NOW(), NOW())
	`, overlayLabel, overlayFields); err != nil {
		t.Fatalf("insert explicit relationship overlay: %v", err)
	}

	if _, err := conn.DB().ExecContext(ctx, "DROP VIEW IF EXISTS `v_contact`"); err != nil {
		t.Fatalf("drop view v_contact: %v", err)
	}
	assertViewMissing(t, conn.DB(), "v_contact")

	if err := ApplyAll(ctx, conn); err != nil {
		t.Fatalf("ApplyAll after dropping view: %v", err)
	}

	checkConn := openIntegrationConnection(t, cfg)
	defer checkConn.Close()

	checkRunner, err := NewRunner(checkConn)
	if err != nil {
		t.Fatalf("new check runner: %v", err)
	}
	if err := checkRunner.RefreshCache(ctx); err != nil {
		t.Fatalf("check runner refresh cache: %v", err)
	}

	assertRelationshipRowCount(t, checkConn.DB(), "Contact", "AccountId", "Account", 1)
	assertRelationshipOverlay(t, checkConn.DB(), "Contact", "AccountId", "Account", overlayLabel, overlayFields)
	assertViewQueryable(t, checkConn.DB(), "v_contact", "AccountName__c")

	rels := checkRunner.SvcMgr.Metadata().GetRelationships(ctx, "Contact")
	if !containsRelationshipOverlay(rels, "AccountId", "Account", overlayLabel, overlayFields) {
		t.Fatalf("GetRelationships(Contact) did not surface explicit overlay after ApplyAll: %#v", rels)
	}
}

func writeIssue1393RelationshipResolverPackSource(t *testing.T) string {
	t.Helper()

	root := t.TempDir()
	objectsDir := filepath.Join(root, "objects")
	if err := os.MkdirAll(objectsDir, 0o755); err != nil {
		t.Fatalf("mkdir objects dir: %v", err)
	}

	manifest := `{
  "manifestFormatVersion": "1",
  "name": "issue-1393-relationship-smoke",
  "displayName": "Issue 1393 Relationship Smoke",
  "category": "app-pack",
  "version": "0.1.0",
  "platformCompatibility": {
    "min": "0.2.0",
    "maxExclusive": "0.3.0"
  },
  "dependencies": [],
  "components": [
    {
      "kind": "object_spec",
      "businessKey": "account-contact",
      "manageability": "locked",
      "path": "objects/account_contact.json"
    }
  ]
}`
	if err := os.WriteFile(filepath.Join(root, "pack.json"), []byte(manifest), 0o644); err != nil {
		t.Fatalf("write pack manifest: %v", err)
	}

	objectSpec := `[
  {
    "apiName": "Account",
    "label": "Account",
    "pluralLabel": "Accounts",
    "tableType": "standard_object",
    "category": "crm",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "fields": [
      {"apiName": "Name", "label": "Account Name", "type": "string", "required": true, "isNameField": true}
    ]
  },
  {
    "apiName": "Contact",
    "label": "Contact",
    "pluralLabel": "Contacts",
    "tableType": "standard_object",
    "category": "crm",
    "sharingModel": "Private",
    "enableHierarchySharing": false,
    "fields": [
      {"apiName": "Name", "label": "Contact Name", "type": "string", "required": true, "isNameField": true},
      {
        "apiName": "AccountId",
        "label": "Account",
        "type": "reference",
        "referenceTo": ["Account"],
        "relationshipName": "Account",
        "childRelationshipName": "Contacts"
      },
      {
        "apiName": "AccountName__c",
        "label": "Account Name Formula",
        "type": "formula",
        "createable": false,
        "updateable": false,
        "formulaMode": "active",
        "formula": "Account.Name",
        "returnType": "text"
      }
    ]
  }
]`
	if err := os.WriteFile(filepath.Join(objectsDir, "account_contact.json"), []byte(objectSpec), 0o644); err != nil {
		t.Fatalf("write object spec: %v", err)
	}

	return root
}

func assertRelationshipRowCount(t *testing.T, db *sql.DB, childObjectAPIName, fieldAPIName, parentObjectAPIName string, want int) {
	t.Helper()

	var got int
	if err := db.QueryRow(`
		SELECT COUNT(*)
		  FROM _System_Relationship
		 WHERE ChildObjectApiName = ?
		   AND FieldApiName = ?
		   AND ParentObjectApiName = ?`,
		childObjectAPIName, fieldAPIName, parentObjectAPIName,
	).Scan(&got); err != nil {
		t.Fatalf("count relationship rows %s.%s -> %s: %v", childObjectAPIName, fieldAPIName, parentObjectAPIName, err)
	}
	if got != want {
		t.Fatalf("relationship row count for %s.%s -> %s = %d, want %d", childObjectAPIName, fieldAPIName, parentObjectAPIName, got, want)
	}
}

func assertRelationshipOverlay(t *testing.T, db *sql.DB, childObjectAPIName, fieldAPIName, parentObjectAPIName, wantLabel, wantFields string) {
	t.Helper()

	var gotLabel, gotFields string
	if err := db.QueryRow(`
		SELECT COALESCE(RelatedListLabel, ''), COALESCE(RelatedListFields, '')
		  FROM _System_Relationship
		 WHERE ChildObjectApiName = ?
		   AND FieldApiName = ?
		   AND ParentObjectApiName = ?
		 LIMIT 1`,
		childObjectAPIName, fieldAPIName, parentObjectAPIName,
	).Scan(&gotLabel, &gotFields); err != nil {
		t.Fatalf("query relationship overlay %s.%s -> %s: %v", childObjectAPIName, fieldAPIName, parentObjectAPIName, err)
	}
	if gotLabel != wantLabel || gotFields != wantFields {
		t.Fatalf("relationship overlay = (%q, %q), want (%q, %q)", gotLabel, gotFields, wantLabel, wantFields)
	}
}

func containsChildSchema(children []*models.ObjectMetadata, apiName string) bool {
	for _, child := range children {
		if child != nil && child.APIName == apiName {
			return true
		}
	}
	return false
}

func containsRelationshipOverlay(rels []*models.Relationship, fieldAPIName, parentObjectAPIName, wantLabel, wantFields string) bool {
	for _, rel := range rels {
		if rel == nil || rel.FieldAPIName != fieldAPIName || rel.ParentObjectAPIName != parentObjectAPIName {
			continue
		}
		if rel.RelatedListLabel == nil || rel.RelatedListFields == nil {
			continue
		}
		if *rel.RelatedListLabel == wantLabel && *rel.RelatedListFields == wantFields {
			return true
		}
	}
	return false
}