package opportunities

import (
	"context"
	"errors"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"

	"github.com/missionworkforce/missionmatch/internal/platform/audit"
	"github.com/missionworkforce/missionmatch/internal/platform/tenant"
)

// TEAMING PARTNERS AND KEY PERSONNEL COMMITMENTS.
//
// It lives in the capture module because that is where the relationship is formed. A teaming agreement is
// negotiated while the pursuit is being shaped, months before a proposal row exists, which is precisely the
// window the rest of the market does not cover: the ERPs handle partners after award and the capture CRMs
// drop them at proposal time.

var (
	// ErrPartnerExists is the unique constraint on one company per pursuit, surfaced as a clean conflict
	// rather than a raw constraint violation, because adding the same partner twice is an ordinary mistake.
	ErrPartnerExists = errors.New("opportunities: that company is already on this pursuit")
	// ErrCommitmentNeedsPerson is the CHECK that a commitment names exactly one person.
	ErrCommitmentNeedsPerson = errors.New(
		"a commitment must name either one of your candidates or a partner plus the person's name")
	// ErrSignedNeedsDate is the CHECK that a signed thing carries the date it was signed.
	ErrSignedNeedsDate = errors.New("a signed agreement or commitment must carry the date it was signed")
	// ErrRequirementMissing is a commitment against a requirement that does not exist or was deleted.
	ErrRequirementMissing = errors.New("that position does not exist")
	// ErrNotFound is an update that matched nothing in the caller's org, which under RLS is the same answer
	// as a row belonging to somebody else: not found, never "forbidden", because the difference would
	// confirm the row exists.
	ErrNotFound = errors.New("opportunities: not found")
)

// TeamingPartner is one company on one pursuit.
type TeamingPartner struct {
	ID                string   `json:"id"`
	OpportunityID     string   `json:"opportunity_id"`
	CompanyName       string   `json:"company_name"`
	PartnerRole       string   `json:"partner_role"`
	Status            string   `json:"status"`
	UEI               string   `json:"uei,omitempty"`
	CageCode          string   `json:"cage_code,omitempty"`
	SetAsides         []string `json:"set_asides"`
	ScopeSummary      string   `json:"scope_summary,omitempty"`
	AgreementSignedOn string   `json:"agreement_signed_on,omitempty"`
	CreatedAt         string   `json:"created_at"`
	UpdatedAt         string   `json:"updated_at"`
	// Commitments is how many key personnel this partner is providing, so a bid review can see at a glance
	// which partners carry evaluated people and which carry only scope.
	Commitments int `json:"commitments"`
}

// PartnerInput is the write shape.
type PartnerInput struct {
	CompanyName       string   `json:"company_name"`
	PartnerRole       string   `json:"partner_role"`
	Status            string   `json:"status"`
	UEI               string   `json:"uei"`
	CageCode          string   `json:"cage_code"`
	SetAsides         []string `json:"set_asides"`
	ScopeSummary      string   `json:"scope_summary"`
	AgreementSignedOn string   `json:"agreement_signed_on"`
}

// KeyPersonnelCommitment is one named person held to one evaluated position.
type KeyPersonnelCommitment struct {
	ID                string `json:"id"`
	RequirementID     string `json:"requirement_id"`
	RequirementTitle  string `json:"requirement_title,omitempty"`
	CandidateID       string `json:"candidate_id,omitempty"`
	CandidateName     string `json:"candidate_name,omitempty"`
	TeamingPartnerID  string `json:"teaming_partner_id,omitempty"`
	PartnerCompany    string `json:"partner_company,omitempty"`
	PartnerPersonName string `json:"partner_person_name,omitempty"`
	RoleTitle         string `json:"role_title,omitempty"`
	CommitmentType    string `json:"commitment_type"`
	Status            string `json:"status"`
	SignedOn          string `json:"signed_on,omitempty"`
	ExpiresOn         string `json:"expires_on,omitempty"`
	Notes             string `json:"notes,omitempty"`
	// DaysUntilExpiry is negative once lapsed. Absent when no expiry was recorded, which is a different and
	// honest state from an expiry far away.
	DaysUntilExpiry *int `json:"days_until_expiry,omitempty"`
	// Lapsed says the commitment has passed its expiry, which is the fact a bid review most needs and the
	// one nobody notices: a letter of intent signed for a bid that slipped six months is not a commitment.
	Lapsed bool `json:"lapsed,omitempty"`
}

// CommitmentInput is the write shape.
type CommitmentInput struct {
	RequirementID     string `json:"requirement_id"`
	CandidateID       string `json:"candidate_id"`
	TeamingPartnerID  string `json:"teaming_partner_id"`
	PartnerPersonName string `json:"partner_person_name"`
	RoleTitle         string `json:"role_title"`
	CommitmentType    string `json:"commitment_type"`
	Status            string `json:"status"`
	SignedOn          string `json:"signed_on"`
	ExpiresOn         string `json:"expires_on"`
	Notes             string `json:"notes"`
}

// ListPartners returns the partners on one pursuit, with how many evaluated people each is providing.
func (s *Service) ListPartners(ctx context.Context, oppID uuid.UUID) ([]TeamingPartner, error) {
	out := make([]TeamingPartner, 0, 16)
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		rows, e := tx.Query(ctx, `
			SELECT p.id::text, p.opportunity_id::text, p.company_name, p.partner_role, p.status,
			       p.uei, p.cage_code, p.set_asides, p.scope_summary,
			       p.agreement_signed_on, p.created_at, p.updated_at,
			       (SELECT count(*)::int FROM key_personnel_commitments k
			         WHERE k.teaming_partner_id = p.id AND k.status <> 'withdrawn')
			  FROM teaming_partners p
			 WHERE p.opportunity_id = $1
			 ORDER BY CASE p.partner_role WHEN 'prime' THEN 0 WHEN 'joint_venture' THEN 1 ELSE 2 END,
			          p.company_name ASC`, oppID)
		if e != nil {
			return e
		}
		defer rows.Close()
		for rows.Next() {
			var p TeamingPartner
			var signed, created, updated *time.Time
			if e := rows.Scan(&p.ID, &p.OpportunityID, &p.CompanyName, &p.PartnerRole, &p.Status,
				&p.UEI, &p.CageCode, &p.SetAsides, &p.ScopeSummary,
				&signed, &created, &updated, &p.Commitments); e != nil {
				return e
			}
			p.AgreementSignedOn, p.CreatedAt, p.UpdatedAt = dayStr(signed), tsStr(created), tsStr(updated)
			out = append(out, p)
		}
		return rows.Err()
	})
	return out, err
}

// AddPartner records a company on a pursuit.
func (s *Service) AddPartner(ctx context.Context, oppID uuid.UUID, in PartnerInput) (string, error) {
	in.CompanyName = strings.TrimSpace(in.CompanyName)
	if in.CompanyName == "" {
		return "", fmt.Errorf("%w: a partner needs a company name", ErrInvalidInput)
	}
	signed, err := optDay(in.AgreementSignedOn)
	if err != nil {
		return "", fmt.Errorf("%w: agreement_signed_on must be YYYY-MM-DD", ErrInvalidInput)
	}
	if in.SetAsides == nil {
		in.SetAsides = []string{}
	}
	var id string
	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		e := tx.QueryRow(ctx, `
			INSERT INTO teaming_partners (organization_id, opportunity_id, company_name, partner_role, status,
			                              uei, cage_code, set_asides, scope_summary, agreement_signed_on, created_by)
			VALUES (NULLIF(current_setting('app.current_org_id', true), '')::uuid, $1, $2,
			        coalesce(nullif($3,''),'subcontractor'), coalesce(nullif($4,''),'identified'),
			        $5, $6, $7, $8, $9, $10)
			RETURNING id::text`,
			oppID, in.CompanyName, in.PartnerRole, in.Status, in.UEI, in.CageCode, in.SetAsides,
			in.ScopeSummary, signed, actorPtr(ctx)).Scan(&id)
		if e != nil {
			return translateTeaming(e)
		}
		return s.auditTeaming(ctx, tx, "capture.partner.added", id)
	})
	return id, err
}

// UpdatePartner changes status, terms or identifiers on an existing partner.
func (s *Service) UpdatePartner(ctx context.Context, id uuid.UUID, in PartnerInput) error {
	signed, err := optDay(in.AgreementSignedOn)
	if err != nil {
		return fmt.Errorf("%w: agreement_signed_on must be YYYY-MM-DD", ErrInvalidInput)
	}
	return s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		tag, e := tx.Exec(ctx, `
			UPDATE teaming_partners SET
			    partner_role        = coalesce(nullif($2,''), partner_role),
			    status              = coalesce(nullif($3,''), status),
			    uei                 = coalesce(nullif($4,''), uei),
			    cage_code           = coalesce(nullif($5,''), cage_code),
			    scope_summary       = coalesce(nullif($6,''), scope_summary),
			    agreement_signed_on = coalesce($7, agreement_signed_on),
			    updated_at          = now()
			 WHERE id = $1`, id, in.PartnerRole, in.Status, in.UEI, in.CageCode, in.ScopeSummary, signed)
		if e != nil {
			return translateTeaming(e)
		}
		if tag.RowsAffected() == 0 {
			return ErrNotFound
		}
		return s.auditTeaming(ctx, tx, "capture.partner.updated", id.String())
	})
}

// commitmentListLimit bounds both commitment lists.
//
// IT WAS UNBOUNDED AND THAT IS A DEFECT, NOT A MISSING NICETY. A stress run put twenty thousand commitments
// on one position and the position view returned a NINE MEGABYTE response in 1.4 seconds, to a panel that
// shows a handful of names. It is the same failure the alerts feed had at 7.9 megabytes, found the same way,
// which is the argument for bounding every list at the moment it is written rather than when somebody
// notices. A key personnel panel showing more than a few hundred names is already telling a story nobody can
// read.
const commitmentListLimit = 500

// ListCommitments returns the key personnel commitments on one pursuit, across every requirement it carries.
func (s *Service) ListCommitments(ctx context.Context, oppID uuid.UUID) ([]KeyPersonnelCommitment, error) {
	out := make([]KeyPersonnelCommitment, 0, 16)
	now := time.Now().UTC().Truncate(24 * time.Hour)
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		rows, e := tx.Query(ctx, `
			SELECT k.id::text, k.requirement_id::text, coalesce(r.title,''),
			       coalesce(k.candidate_id::text,''), coalesce(c.full_name,''),
			       coalesce(k.teaming_partner_id::text,''), coalesce(p.company_name,''),
			       k.partner_person_name, k.role_title, k.commitment_type, k.status,
			       k.signed_on, k.expires_on, k.notes
			  FROM key_personnel_commitments k
			  JOIN requirements r ON r.organization_id = k.organization_id AND r.id = k.requirement_id
			  LEFT JOIN candidates c ON c.organization_id = k.organization_id AND c.id = k.candidate_id
			  LEFT JOIN teaming_partners p ON p.organization_id = k.organization_id AND p.id = k.teaming_partner_id
			 WHERE EXISTS (
			       SELECT 1
			         FROM proposal_staffing_links psl
			         JOIN proposals pr ON pr.organization_id = psl.organization_id AND pr.id = psl.proposal_id
			        WHERE psl.organization_id = k.organization_id
			          AND psl.requirement_id  = k.requirement_id
			          AND pr.opportunity_id   = $1)
			 ORDER BY r.title ASC, k.created_at ASC
			 LIMIT `+strconv.Itoa(commitmentListLimit), oppID)
		if e != nil {
			return e
		}
		defer rows.Close()
		for rows.Next() {
			var k KeyPersonnelCommitment
			var signed, expires *time.Time
			if e := rows.Scan(&k.ID, &k.RequirementID, &k.RequirementTitle, &k.CandidateID, &k.CandidateName,
				&k.TeamingPartnerID, &k.PartnerCompany, &k.PartnerPersonName, &k.RoleTitle,
				&k.CommitmentType, &k.Status, &signed, &expires, &k.Notes); e != nil {
				return e
			}
			k.SignedOn, k.ExpiresOn = dayStr(signed), dayStr(expires)
			if expires != nil {
				d := int(expires.UTC().Truncate(24*time.Hour).Sub(now).Hours() / 24)
				k.DaysUntilExpiry = &d
				// LAPSED IS COMPUTED, NEVER STORED. A stored flag would be correct the day it was written and
				// wrong every day after, and the whole value of this field is that it changes on its own.
				k.Lapsed = d < 0 && (k.Status == "proposed" || k.Status == "committed")
			}
			out = append(out, k)
		}
		return rows.Err()
	})
	return out, err
}

// ListCommitmentsForRequirement returns the commitments on ONE position.
//
// IT EXISTS SO A COMMITMENT IS ALWAYS VISIBLE SOMEWHERE. The pursuit view joins through the proposal and its
// staffing links, which is the correct relationship and is also empty until a proposal exists and the
// position is staffed onto it. Without this route a commitment recorded early in capture would be written,
// return an identifier, and appear on no screen at all, which is the failure the first version of this
// feature actually shipped with.
func (s *Service) ListCommitmentsForRequirement(ctx context.Context, reqID uuid.UUID) ([]KeyPersonnelCommitment, error) {
	out := make([]KeyPersonnelCommitment, 0, 8)
	now := time.Now().UTC().Truncate(24 * time.Hour)
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		rows, e := tx.Query(ctx, `
			SELECT k.id::text, k.requirement_id::text, coalesce(r.title,''),
			       coalesce(k.candidate_id::text,''), coalesce(c.full_name,''),
			       coalesce(k.teaming_partner_id::text,''), coalesce(p.company_name,''),
			       k.partner_person_name, k.role_title, k.commitment_type, k.status,
			       k.signed_on, k.expires_on, k.notes
			  FROM key_personnel_commitments k
			  JOIN requirements r ON r.organization_id = k.organization_id AND r.id = k.requirement_id
			  LEFT JOIN candidates c ON c.organization_id = k.organization_id AND c.id = k.candidate_id
			  LEFT JOIN teaming_partners p ON p.organization_id = k.organization_id AND p.id = k.teaming_partner_id
			 WHERE k.requirement_id = $1
			 ORDER BY k.created_at ASC
			 LIMIT `+strconv.Itoa(commitmentListLimit), reqID)
		if e != nil {
			return e
		}
		defer rows.Close()
		for rows.Next() {
			var k KeyPersonnelCommitment
			var signed, expires *time.Time
			if e := rows.Scan(&k.ID, &k.RequirementID, &k.RequirementTitle, &k.CandidateID, &k.CandidateName,
				&k.TeamingPartnerID, &k.PartnerCompany, &k.PartnerPersonName, &k.RoleTitle,
				&k.CommitmentType, &k.Status, &signed, &expires, &k.Notes); e != nil {
				return e
			}
			k.SignedOn, k.ExpiresOn = dayStr(signed), dayStr(expires)
			if expires != nil {
				d := int(expires.UTC().Truncate(24*time.Hour).Sub(now).Hours() / 24)
				k.DaysUntilExpiry = &d
				k.Lapsed = d < 0 && (k.Status == "proposed" || k.Status == "committed")
			}
			out = append(out, k)
		}
		return rows.Err()
	})
	return out, err
}

// AddCommitment records a person held to an evaluated position.
func (s *Service) AddCommitment(ctx context.Context, in CommitmentInput) (string, error) {
	reqID, err := uuid.Parse(strings.TrimSpace(in.RequirementID))
	if err != nil {
		return "", fmt.Errorf("%w: requirement_id is required", ErrInvalidInput)
	}
	signed, err := optDay(in.SignedOn)
	if err != nil {
		return "", fmt.Errorf("%w: signed_on must be YYYY-MM-DD", ErrInvalidInput)
	}
	expires, err := optDay(in.ExpiresOn)
	if err != nil {
		return "", fmt.Errorf("%w: expires_on must be YYYY-MM-DD", ErrInvalidInput)
	}
	var cand, partner *uuid.UUID
	if v := strings.TrimSpace(in.CandidateID); v != "" {
		id, perr := uuid.Parse(v)
		if perr != nil {
			return "", fmt.Errorf("%w: candidate_id is not an identifier", ErrInvalidInput)
		}
		cand = &id
	}
	if v := strings.TrimSpace(in.TeamingPartnerID); v != "" {
		id, perr := uuid.Parse(v)
		if perr != nil {
			return "", fmt.Errorf("%w: teaming_partner_id is not an identifier", ErrInvalidInput)
		}
		partner = &id
	}

	var id string
	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		// THE REQUIREMENT MUST EXIST, and that is all this can check.
		//
		// The first version of this guard tested requirements.opportunity_id, on the assumption that it
		// pointed at the tracked opportunity the partners hang off. It does not. There are two opportunity
		// tables: requirements reference `opportunities`, from migration 0002, while proposals and teaming
		// partners reference `tracked_opportunities`, the capture spine from 0070. The bridge between a
		// pursuit and a requirement runs through the proposal and its staffing links, which is why the list
		// below joins that way. Guarding on the wrong column refused every legitimate commitment.
		var exists bool
		if e := tx.QueryRow(ctx,
			`SELECT true FROM requirements WHERE id = $1 AND deleted_at IS NULL`, reqID).Scan(&exists); e != nil {
			if errors.Is(e, pgx.ErrNoRows) {
				return ErrRequirementMissing
			}
			return e
		}
		e := tx.QueryRow(ctx, `
			INSERT INTO key_personnel_commitments (organization_id, requirement_id, candidate_id,
			        teaming_partner_id, partner_person_name, role_title, commitment_type, status,
			        signed_on, expires_on, notes, created_by)
			VALUES (NULLIF(current_setting('app.current_org_id', true), '')::uuid, $1, $2, $3, $4, $5,
			        coalesce(nullif($6,''),'letter_of_intent'), coalesce(nullif($7,''),'proposed'),
			        $8, $9, $10, $11)
			RETURNING id::text`,
			reqID, cand, partner, strings.TrimSpace(in.PartnerPersonName), in.RoleTitle,
			in.CommitmentType, in.Status, signed, expires, in.Notes, actorPtr(ctx)).Scan(&id)
		if e != nil {
			return translateTeaming(e)
		}
		return s.auditTeaming(ctx, tx, "capture.commitment.added", id)
	})
	return id, err
}

// UpdateCommitment changes the status or the dates on an existing commitment.
func (s *Service) UpdateCommitment(ctx context.Context, id uuid.UUID, in CommitmentInput) error {
	signed, err := optDay(in.SignedOn)
	if err != nil {
		return fmt.Errorf("%w: signed_on must be YYYY-MM-DD", ErrInvalidInput)
	}
	expires, err := optDay(in.ExpiresOn)
	if err != nil {
		return fmt.Errorf("%w: expires_on must be YYYY-MM-DD", ErrInvalidInput)
	}
	return s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		tag, e := tx.Exec(ctx, `
			UPDATE key_personnel_commitments SET
			    commitment_type = coalesce(nullif($2,''), commitment_type),
			    status          = coalesce(nullif($3,''), status),
			    role_title      = coalesce(nullif($4,''), role_title),
			    signed_on       = coalesce($5, signed_on),
			    expires_on      = coalesce($6, expires_on),
			    notes           = coalesce(nullif($7,''), notes),
			    updated_at      = now()
			 WHERE id = $1`, id, in.CommitmentType, in.Status, in.RoleTitle, signed, expires, in.Notes)
		if e != nil {
			return translateTeaming(e)
		}
		if tag.RowsAffected() == 0 {
			return ErrNotFound
		}
		return s.auditTeaming(ctx, tx, "capture.commitment.updated", id.String())
	})
}

// translateTeaming turns the database's own refusals into sentences a capture manager can act on. The CHECK
// constraints encode real rules, and surfacing them as raw constraint violations would waste the fact that
// the rule was written down.
func translateTeaming(e error) error {
	msg := e.Error()
	switch {
	case strings.Contains(msg, "uq_teaming_partner_per_pursuit"):
		return ErrPartnerExists
	case strings.Contains(msg, "ck_kpc_names_somebody"):
		return ErrCommitmentNeedsPerson
	case strings.Contains(msg, "ck_teaming_signed_dated"), strings.Contains(msg, "ck_kpc_committed_dated"):
		return ErrSignedNeedsDate
	case strings.Contains(msg, "ck_kpc_expiry_after_signing"):
		return fmt.Errorf("%w: a commitment cannot expire before it was signed", ErrInvalidInput)
	}
	return e
}

func (s *Service) auditTeaming(ctx context.Context, tx pgx.Tx, action, entityID string) error {
	if s.aud == nil {
		return nil
	}
	ev := audit.Event{Action: action, EntityType: "teaming", EntityID: entityID}
	if p, ok := tenant.UserFromContext(ctx); ok {
		if uid, err := uuid.Parse(p.UserID); err == nil {
			ev.ActorUserID = &uid
		}
	}
	return s.aud.OnTx(ctx, tx, ev)
}

func optDay(v string) (*time.Time, error) {
	v = strings.TrimSpace(v)
	if v == "" {
		return nil, nil
	}
	t, err := time.Parse("2006-01-02", v)
	if err != nil {
		return nil, err
	}
	return &t, nil
}

func dayStr(t *time.Time) string {
	if t == nil {
		return ""
	}
	return t.UTC().Format("2006-01-02")
}

func tsStr(t *time.Time) string {
	if t == nil {
		return ""
	}
	return t.UTC().Format(time.RFC3339)
}