package organization

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

const (
	organizationDirectorySyncRunStatusRunning   = "running"
	organizationDirectorySyncRunStatusCompleted = "completed"
	organizationDirectorySyncRunStatusFailed    = "failed"
)

type organizationDirectorySyncRunRecord struct {
	ID                string
	DirectoryRevision int64
	ExternalProvider  string
	SyncSource        string
	Status            string
	StartedAt         *time.Time
	CompletedAt       *time.Time
}

type organizationDirectorySyncRunDetailRecord struct {
	ID                string
	DirectoryRevision int64
	ExternalProvider  string
	SyncSource        string
	Status            string
	StartedAt         time.Time
	CompletedAt       *time.Time
	LinkedTeamCount   int
	LinkedUnitCount   int
	LinkedMemberCount int
}

func (txs TxStore) loadOrganizationDirectorySyncRun(ctx context.Context, queryer queryRower, organizationID string, syncRunID string) (organizationDirectorySyncRunRecord, error) {
	row := queryer.QueryRow(ctx, `
SELECT id,
       directory_revision,
       external_provider,
       sync_source,
       status
FROM organization_directory_sync_runs
WHERE organization_id = $1
  AND id = $2
`, organizationID, syncRunID)

	var run organizationDirectorySyncRunRecord
	if err := row.Scan(
		&run.ID,
		&run.DirectoryRevision,
		&run.ExternalProvider,
		&run.SyncSource,
		&run.Status,
	); err != nil {
		if coredb.IsNotFound(err) {
			return organizationDirectorySyncRunRecord{}, ErrNotFound
		}
		return organizationDirectorySyncRunRecord{}, fmt.Errorf("load organization directory sync run: %w", err)
	}
	return run, nil
}

func (txs TxStore) loadLatestRunningOrganizationDirectorySyncRun(
	ctx context.Context,
	queryer queryRower,
	organizationID string,
	externalProvider string,
	syncSource string,
) (organizationDirectorySyncRunRecord, bool, error) {
	row := queryer.QueryRow(ctx, `
SELECT id,
       directory_revision,
       external_provider,
       sync_source,
       status,
       started_at,
       completed_at
FROM organization_directory_sync_runs
WHERE organization_id = $1
  AND external_provider = $2
  AND sync_source = $3
  AND status = 'running'
ORDER BY started_at DESC, id DESC
LIMIT 1
`, organizationID, externalProvider, syncSource)

	var run organizationDirectorySyncRunRecord
	var startedAt time.Time
	var completedAt sql.NullTime
	if err := row.Scan(
		&run.ID,
		&run.DirectoryRevision,
		&run.ExternalProvider,
		&run.SyncSource,
		&run.Status,
		&startedAt,
		&completedAt,
	); err != nil {
		if coredb.IsNotFound(err) {
			return organizationDirectorySyncRunRecord{}, false, nil
		}
		return organizationDirectorySyncRunRecord{}, false, fmt.Errorf("load latest running organization directory sync run: %w", err)
	}
	run.StartedAt = &startedAt
	if completedAt.Valid {
		value := completedAt.Time
		run.CompletedAt = &value
	}
	return run, true, nil
}

func (txs TxStore) GetOrganizationDirectorySyncRunForUser(ctx context.Context, userID string, organizationID string, syncRunID string) (OrganizationDirectorySyncRunDetailView, error) {
	if _, err := txs.requireActiveOrganizationMembership(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationDirectorySyncRunDetailView{}, err
	}

	record, err := txs.loadOrganizationDirectorySyncRunDetail(ctx, txs.db, organizationID, syncRunID)
	if err != nil {
		return OrganizationDirectorySyncRunDetailView{}, err
	}
	return record.view(), nil
}

func (txs TxStore) loadOrganizationDirectorySyncRunDetail(ctx context.Context, queryer queryRower, organizationID string, syncRunID string) (organizationDirectorySyncRunDetailRecord, error) {
	row := queryer.QueryRow(ctx, `
SELECT osr.id,
       osr.directory_revision,
       osr.external_provider,
       osr.sync_source,
       osr.status,
       osr.started_at,
       osr.completed_at,
       (SELECT COUNT(*) FROM organization_teams WHERE organization_id = $1 AND last_sync_run_id = osr.id),
       (SELECT COUNT(*) FROM organization_units WHERE organization_id = $1 AND last_sync_run_id = osr.id),
       (SELECT COUNT(*) FROM organization_members WHERE organization_id = $1 AND last_sync_run_id = osr.id)
FROM organization_directory_sync_runs osr
WHERE osr.organization_id = $1
  AND osr.id = $2
`, organizationID, syncRunID)

	var record organizationDirectorySyncRunDetailRecord
	var completedAt sql.NullTime
	if err := row.Scan(
		&record.ID,
		&record.DirectoryRevision,
		&record.ExternalProvider,
		&record.SyncSource,
		&record.Status,
		&record.StartedAt,
		&completedAt,
		&record.LinkedTeamCount,
		&record.LinkedUnitCount,
		&record.LinkedMemberCount,
	); err != nil {
		if coredb.IsNotFound(err) {
			return organizationDirectorySyncRunDetailRecord{}, ErrNotFound
		}
		return organizationDirectorySyncRunDetailRecord{}, fmt.Errorf("load organization directory sync run detail: %w", err)
	}
	if completedAt.Valid {
		value := completedAt.Time
		record.CompletedAt = &value
	}
	return record, nil
}

func (record organizationDirectorySyncRunDetailRecord) view() OrganizationDirectorySyncRunDetailView {
	startedAt := record.StartedAt
	return OrganizationDirectorySyncRunDetailView{
		SyncRunID:         record.ID,
		ExternalProvider:  record.ExternalProvider,
		SyncSource:        record.SyncSource,
		Status:            record.Status,
		DirectoryRevision: record.DirectoryRevision,
		StartedAt:         &startedAt,
		CompletedAt:       record.CompletedAt,
		LinkedTeamCount:   record.LinkedTeamCount,
		LinkedUnitCount:   record.LinkedUnitCount,
		LinkedMemberCount: record.LinkedMemberCount,
	}
}

func (txs TxStore) ensureOrganizationDirectorySyncRun(ctx context.Context, tx coredb.Tx, organizationID string, state organizationDirectoryState, identity normalizedOrganizationSyncIdentity, params MarkOrganizationSyncedParams) (organizationDirectorySyncRunRecord, error) {
	syncRunID := strings.TrimSpace(params.SyncRunID)
	if syncRunID == "" {
		return txs.createImplicitOrganizationDirectorySyncRun(ctx, tx, organizationID, state.revision, identity)
	}

	run, err := txs.loadOrganizationDirectorySyncRun(ctx, tx, organizationID, syncRunID)
	if err != nil {
		if errors.Is(err, ErrNotFound) {
			return organizationDirectorySyncRunRecord{}, badRequest("syncRunId is invalid")
		}
		return organizationDirectorySyncRunRecord{}, err
	}
	if run.DirectoryRevision != state.revision {
		return organizationDirectorySyncRunRecord{}, conflict("organization directory backend revision changed")
	}
	if run.ExternalProvider != identity.externalProvider {
		return organizationDirectorySyncRunRecord{}, badRequest("sync run provider does not match object provider")
	}
	if strings.TrimSpace(run.SyncSource) != identity.syncSource {
		return organizationDirectorySyncRunRecord{}, badRequest("sync run source does not match object source")
	}
	if run.Status == organizationDirectorySyncRunStatusFailed {
		return organizationDirectorySyncRunRecord{}, badRequest("sync run is not writable")
	}
	return run, nil
}

func (txs TxStore) requireRunningOrganizationDirectorySyncRun(ctx context.Context, queryer queryRower, organizationID string, state organizationDirectoryState, syncRunID string) (organizationDirectorySyncRunRecord, error) {
	run, err := txs.loadOrganizationDirectorySyncRun(ctx, queryer, organizationID, strings.TrimSpace(syncRunID))
	if err != nil {
		return organizationDirectorySyncRunRecord{}, err
	}
	if run.DirectoryRevision != state.revision {
		return organizationDirectorySyncRunRecord{}, conflict("organization directory backend revision changed")
	}
	if run.ExternalProvider != organizationProviderFeishu {
		return organizationDirectorySyncRunRecord{}, badRequest("sync run provider does not match object provider")
	}
	if strings.TrimSpace(run.SyncSource) != organizationSyncSourceDefault {
		return organizationDirectorySyncRunRecord{}, badRequest("sync run source does not match object source")
	}
	if run.Status != organizationDirectorySyncRunStatusRunning {
		return organizationDirectorySyncRunRecord{}, conflict("sync run is not running")
	}
	return run, nil
}

func (txs TxStore) failOrganizationDirectorySyncRun(ctx context.Context, execer coredb.QueryExecer, organizationID string, syncRunID string, failedAt time.Time) error {
	trimmedSyncRunID := strings.TrimSpace(syncRunID)
	if trimmedSyncRunID == "" {
		return badRequest("syncRunId is required")
	}
	now := failedAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	commandTag, err := execer.Exec(ctx, `
UPDATE organization_directory_sync_runs
SET status = 'failed',
    completed_at = $3,
    updated_at = $3
WHERE organization_id = $1
  AND id = $2
  AND status = 'running'
`, organizationID, trimmedSyncRunID, now)
	if err != nil {
		return fmt.Errorf("fail organization directory sync run: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

func (txs TxStore) createImplicitOrganizationDirectorySyncRun(ctx context.Context, tx coredb.Tx, organizationID string, directoryRevision int64, identity normalizedOrganizationSyncIdentity) (organizationDirectorySyncRunRecord, error) {
	syncRunID, err := identifiers.NewEntityID("odsr")
	if err != nil {
		return organizationDirectorySyncRunRecord{}, fmt.Errorf("generate organization directory sync run id: %w", err)
	}

	now := time.Now().UTC()
	if _, err := tx.Exec(ctx, `
INSERT INTO organization_directory_sync_runs (
    id,
    organization_id,
    directory_revision,
    external_provider,
    sync_source,
    status,
    started_at,
    completed_at,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $7, $8, $8)
`, syncRunID, organizationID, directoryRevision, identity.externalProvider, identity.syncSource, organizationDirectorySyncRunStatusCompleted, identity.syncedAt, now); err != nil {
		return organizationDirectorySyncRunRecord{}, fmt.Errorf("insert organization directory sync run: %w", err)
	}

	return organizationDirectorySyncRunRecord{
		ID:                syncRunID,
		DirectoryRevision: directoryRevision,
		ExternalProvider:  identity.externalProvider,
		SyncSource:        identity.syncSource,
		Status:            organizationDirectorySyncRunStatusCompleted,
	}, nil
}