package catalogapp

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"strings"
)

// ReadWriterLineage returns Catalog's org-wide publication fence. The value is
// an optimistic input to BeginOperation; that owner still locks and compares
// the writer row, so a concurrent advance is returned as a normal conflict.
func (a *Authority) ReadWriterLineage(ctx context.Context, orgID string) (WriterLineage, error) {
	if a == nil || a.db == nil || strings.TrimSpace(orgID) == "" {
		return WriterLineage{}, ErrNotFound
	}
	var lineage WriterLineage
	var rawCatalog, rawSecurity []byte
	var activeOperation sql.NullString
	err := a.db.QueryRowContext(ctx, `SELECT OrgId, RuntimeAudience, CatalogRevision,
LastSecurityEpoch, ActiveOperationId FROM _System_CatalogOrgState WHERE OrgId = ?`, orgID).Scan(
		&lineage.OrgID, &lineage.RuntimeAudience, &rawCatalog, &rawSecurity, &activeOperation,
	)
	if errors.Is(err, sql.ErrNoRows) {
		return WriterLineage{}, ErrNotFound
	}
	if err != nil {
		return WriterLineage{}, err
	}
	lineage.CatalogRevision, err = parseEpochAllowZero(rawCatalog)
	if err != nil {
		return WriterLineage{}, err
	}
	lineage.SecurityEpoch, err = parseEpoch(rawSecurity)
	if err != nil {
		return WriterLineage{}, err
	}
	lineage.ActiveOperationID = activeOperation.String
	if lineage.OrgID != orgID || strings.TrimSpace(lineage.RuntimeAudience) == "" {
		return WriterLineage{}, ErrCorrupt
	}
	return lineage, nil
}

func (a *Authority) ReadPublisher(ctx context.Context, publisherID string) (RegisteredPublisher, error) {
	var result RegisteredPublisher
	var rawEpoch []byte
	var state string
	err := a.db.QueryRowContext(ctx, `SELECT PublisherId, OwnershipEpoch, State FROM _System_CatalogPublisher
WHERE PublisherId = ?`, publisherID).Scan(&result.PublisherID, &rawEpoch, &state)
	if errors.Is(err, sql.ErrNoRows) {
		return result, ErrNotFound
	}
	if err != nil {
		return result, err
	}
	result.OwnershipEpoch, err = parseEpoch(rawEpoch)
	if err != nil {
		return result, err
	}
	result.Active = state == "ACTIVE"
	rows, err := a.db.QueryContext(ctx, `SELECT KeyId, Algorithm, PublicKey, ValidFrom, ValidUntil, State
FROM _System_CatalogPublisherKey WHERE PublisherId = ? ORDER BY KeyId`, publisherID)
	if err != nil {
		return result, err
	}
	defer rows.Close()
	for rows.Next() {
		var key RegisteredPublisherKey
		var keyState string
		if err := rows.Scan(&key.KeyID, &key.Algorithm, &key.PublicKey, &key.ValidFrom, &key.ValidUntil, &keyState); err != nil {
			return result, err
		}
		key.Active = keyState == "ACTIVE"
		result.Keys = append(result.Keys, key)
	}
	return result, rows.Err()
}

func (a *Authority) ReadInstallation(ctx context.Context, installationID string) (AppInstallation, error) {
	var result AppInstallation
	var activeRelease, candidateRelease, lastGood, activeOperation sql.NullString
	var rawCatalog, rawActivation []byte
	var activeWorkloads, candidateWorkloads, activeCapabilities, candidateCapabilities, activeDependencies, candidateDependencies []byte
	err := a.db.QueryRowContext(ctx, `SELECT AppInstallationId, OrgId, AppId, State,
ActiveReleaseDigest, CandidateReleaseDigest, LastKnownGoodReleaseDigest, CatalogRevision, ActivationEpoch, ActiveOperationId,
ActiveAuthorizedWorkloads, CandidateAuthorizedWorkloads, ActiveGrantedCapabilities, CandidateGrantedCapabilities,
ActiveResolvedDependencies, CandidateResolvedDependencies
FROM _System_CatalogAppInstallation WHERE AppInstallationId = ?`, installationID).Scan(
		&result.AppInstallationID, &result.OrgID, &result.AppID, &result.State,
		&activeRelease, &candidateRelease, &lastGood, &rawCatalog, &rawActivation, &activeOperation,
		&activeWorkloads, &candidateWorkloads, &activeCapabilities, &candidateCapabilities, &activeDependencies, &candidateDependencies)
	if errors.Is(err, sql.ErrNoRows) {
		return result, ErrNotFound
	}
	if err != nil {
		return result, err
	}
	result.ActiveReleaseDigest, result.CandidateReleaseDigest = activeRelease.String, candidateRelease.String
	result.LastKnownGoodReleaseDigest, result.ActiveOperationID = lastGood.String, activeOperation.String
	if result.CatalogRevision, err = parseEpochAllowZero(rawCatalog); err != nil {
		return result, err
	}
	if result.ActivationEpoch, err = parseEpochAllowZero(rawActivation); err != nil {
		return result, err
	}
	for raw, target := range map[*[]byte]any{
		&activeWorkloads: &result.ActiveAuthorizedWorkloads, &candidateWorkloads: &result.CandidateAuthorizedWorkloads,
		&activeCapabilities: &result.ActiveGrantedCapabilities, &candidateCapabilities: &result.CandidateGrantedCapabilities,
		&activeDependencies: &result.ActiveResolvedDependencies, &candidateDependencies: &result.CandidateResolvedDependencies,
	} {
		if len(*raw) > 0 {
			if err := json.Unmarshal(*raw, target); err != nil {
				return result, err
			}
		}
	}
	return result, nil
}

func (a *Authority) ReadHandlerBinding(ctx context.Context, bindingID string) (HandlerBinding, error) {
	var binding HandlerBinding
	var rawEpoch []byte
	err := a.db.QueryRowContext(ctx, `SELECT HandlerBindingId, OrgId, AppInstallationId, AppReleaseDigest,
CapabilityId, HandlerRef, WorkloadId, ServiceReleaseDigest, OwnershipEpoch, State, CompatibilityReceiptDigest
FROM _System_CatalogHandlerBinding WHERE HandlerBindingId = ?`, bindingID).Scan(&binding.HandlerBindingID, &binding.OrgID,
		&binding.AppInstallationID, &binding.AppReleaseDigest, &binding.CapabilityID, &binding.HandlerRef,
		&binding.WorkloadID, &binding.ServiceReleaseDigest, &rawEpoch, &binding.State, &binding.CompatibilityReceiptDigest)
	if errors.Is(err, sql.ErrNoRows) {
		return binding, ErrNotFound
	}
	if err != nil {
		return binding, err
	}
	binding.OwnershipEpoch, err = parseEpoch(rawEpoch)
	return binding, err
}

// ReadOperationHandlerBinding returns the unique immutable binding selected by
// one operation/capability. Reconcile uses it both after a crash and for
// historical idempotent replay; it never substitutes the currently active
// owner chosen by a later operation.
func (a *Authority) ReadOperationHandlerBinding(ctx context.Context, operationID, capabilityID string) (HandlerBinding, error) {
	rows, err := a.db.QueryContext(ctx, `SELECT HandlerBindingId, OrgId, AppInstallationId, AppReleaseDigest,
CapabilityId, HandlerRef, WorkloadId, ServiceReleaseDigest, OwnershipEpoch, State, CompatibilityReceiptDigest
FROM _System_CatalogHandlerBinding WHERE OperationId = ? AND CapabilityId = ? AND State <> 'FAILED'
ORDER BY HandlerBindingId`, operationID, capabilityID)
	if err != nil {
		return HandlerBinding{}, err
	}
	defer rows.Close()
	var binding HandlerBinding
	count := 0
	for rows.Next() {
		count++
		var rawEpoch []byte
		if err := rows.Scan(&binding.HandlerBindingID, &binding.OrgID, &binding.AppInstallationID, &binding.AppReleaseDigest,
			&binding.CapabilityID, &binding.HandlerRef, &binding.WorkloadID, &binding.ServiceReleaseDigest,
			&rawEpoch, &binding.State, &binding.CompatibilityReceiptDigest); err != nil {
			return HandlerBinding{}, err
		}
		binding.OwnershipEpoch, err = parseEpoch(rawEpoch)
		if err != nil {
			return HandlerBinding{}, err
		}
	}
	if err := rows.Err(); err != nil {
		return HandlerBinding{}, err
	}
	if count == 0 {
		return HandlerBinding{}, ErrNotFound
	}
	if count != 1 {
		return HandlerBinding{}, fmt.Errorf("%w: operation has multiple bindings for capability %q", ErrConflict, capabilityID)
	}
	return binding, nil
}

// ReadOperationFrontendBinding returns the immutable frontend selection made
// by one operation even after a later operation has replaced its active owner.
func (a *Authority) ReadOperationFrontendBinding(ctx context.Context, operationID string) (FrontendBinding, error) {
	rows, err := a.db.QueryContext(ctx, `SELECT FrontendBindingId, OrgId, AppInstallationId, AppReleaseDigest,
FrontendReleaseDigest, State, CompatibilityReceiptDigest FROM _System_CatalogFrontendBinding
WHERE OperationId = ? AND State <> 'FAILED' ORDER BY FrontendBindingId`, operationID)
	if err != nil {
		return FrontendBinding{}, err
	}
	defer rows.Close()
	var binding FrontendBinding
	count := 0
	for rows.Next() {
		count++
		if err := rows.Scan(&binding.FrontendBindingID, &binding.OrgID, &binding.AppInstallationID,
			&binding.AppReleaseDigest, &binding.FrontendReleaseDigest, &binding.State,
			&binding.CompatibilityReceiptDigest); err != nil {
			return FrontendBinding{}, err
		}
	}
	if err := rows.Err(); err != nil {
		return FrontendBinding{}, err
	}
	if count == 0 {
		return FrontendBinding{}, ErrNotFound
	}
	if count != 1 {
		return FrontendBinding{}, fmt.Errorf("%w: operation has multiple frontend bindings", ErrConflict)
	}
	return binding, nil
}

// ReadActiveHandlerBinding returns the Catalog-owned current service owner for
// one installed capability. The active-key uniqueness constraint makes this a
// single authoritative read rather than a caller-maintained binding registry.
func (a *Authority) ReadActiveHandlerBinding(ctx context.Context, appInstallationID, capabilityID string) (HandlerBinding, error) {
	var binding HandlerBinding
	var rawEpoch []byte
	err := a.db.QueryRowContext(ctx, `SELECT HandlerBindingId, OrgId, AppInstallationId, AppReleaseDigest,
CapabilityId, HandlerRef, WorkloadId, ServiceReleaseDigest, OwnershipEpoch, State, CompatibilityReceiptDigest
FROM _System_CatalogHandlerBinding
WHERE AppInstallationId = ? AND CapabilityId = ? AND State = 'ACTIVE' AND ActiveKey IS NOT NULL`,
		appInstallationID, capabilityID).Scan(&binding.HandlerBindingID, &binding.OrgID,
		&binding.AppInstallationID, &binding.AppReleaseDigest, &binding.CapabilityID, &binding.HandlerRef,
		&binding.WorkloadID, &binding.ServiceReleaseDigest, &rawEpoch, &binding.State, &binding.CompatibilityReceiptDigest)
	if errors.Is(err, sql.ErrNoRows) {
		return binding, ErrNotFound
	}
	if err != nil {
		return binding, err
	}
	binding.OwnershipEpoch, err = parseEpoch(rawEpoch)
	return binding, err
}

// ReadLatestHandlerOwnershipEpoch returns the highest epoch ever allocated for
// one installed capability. Failed staged bindings deliberately burn their
// epoch, so a recovery owner must use this history fact rather than derive the
// next epoch from only the currently active binding.
func (a *Authority) ReadLatestHandlerOwnershipEpoch(ctx context.Context, appInstallationID, capabilityID string) (uint64, error) {
	var rawEpoch []byte
	err := a.db.QueryRowContext(ctx, `SELECT OwnershipEpoch FROM _System_CatalogHandlerBinding
WHERE AppInstallationId = ? AND CapabilityId = ? ORDER BY OwnershipEpoch DESC LIMIT 1`,
		appInstallationID, capabilityID).Scan(&rawEpoch)
	if errors.Is(err, sql.ErrNoRows) {
		return 0, ErrNotFound
	}
	if err != nil {
		return 0, err
	}
	return parseEpoch(rawEpoch)
}

func (a *Authority) ReadFrontendBinding(ctx context.Context, bindingID string) (FrontendBinding, error) {
	var binding FrontendBinding
	err := a.db.QueryRowContext(ctx, `SELECT FrontendBindingId, OrgId, AppInstallationId, AppReleaseDigest,
FrontendReleaseDigest, State, CompatibilityReceiptDigest FROM _System_CatalogFrontendBinding WHERE FrontendBindingId = ?`, bindingID).Scan(
		&binding.FrontendBindingID, &binding.OrgID, &binding.AppInstallationID, &binding.AppReleaseDigest,
		&binding.FrontendReleaseDigest, &binding.State, &binding.CompatibilityReceiptDigest)
	if errors.Is(err, sql.ErrNoRows) {
		return binding, ErrNotFound
	}
	return binding, err
}

// ReadActiveFrontendBinding returns the unique Catalog-owned frontend for one
// installed App. ActiveKey is unique per installation, so callers do not need
// a second registry or release-shape cache to reconstruct an activated
// operation.
func (a *Authority) ReadActiveFrontendBinding(ctx context.Context, appInstallationID string) (FrontendBinding, error) {
	var binding FrontendBinding
	err := a.db.QueryRowContext(ctx, `SELECT FrontendBindingId, OrgId, AppInstallationId, AppReleaseDigest,
FrontendReleaseDigest, State, CompatibilityReceiptDigest FROM _System_CatalogFrontendBinding
WHERE AppInstallationId = ? AND State = 'ACTIVE' AND ActiveKey IS NOT NULL`, appInstallationID).Scan(
		&binding.FrontendBindingID, &binding.OrgID, &binding.AppInstallationID, &binding.AppReleaseDigest,
		&binding.FrontendReleaseDigest, &binding.State, &binding.CompatibilityReceiptDigest)
	if errors.Is(err, sql.ErrNoRows) {
		return binding, ErrNotFound
	}
	return binding, err
}