package alert

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

	"github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var (
	ErrNotFound = errors.New("alert not found")
	// ErrDuplicateOpenAlert means another open alert with the same
	// aggregation key already exists (lost a create race).
	ErrDuplicateOpenAlert = errors.New("open alert already exists for aggregation key")
)

type Store struct {
	db    *sqlx.DB
	idGen *IDGenerator
}

func NewStore(db *sqlx.DB) *Store {
	return &Store{
		db:    db,
		idGen: NewIDGenerator(db),
	}
}

// Create inserts a new alert and returns it with the generated ID. aggKey is
// the aggregation-key hash; it is also the initial open_slot value (the app
// maintains open_slot: set here, nulled on resolve/detach) backing the unique
// dedup index. created_at anchors the staleness window.
func (s *Store) Create(ctx context.Context, classification string, attrs *AlertAttrs, assignedTo, aggKey string, aggKeyVersion int, responseDeadline, recoveryDeadline *time.Time) (*Alert, error) {
	id, err := s.idGen.NextID(ctx, classification)
	if err != nil {
		return nil, err
	}

	attrsJSON, err := json.Marshal(attrs)
	if err != nil {
		return nil, fmt.Errorf("marshal attrs: %w", err)
	}

	const q = `
		INSERT INTO alert (id, state, attrs, assigned_to, open_fingerprint, agg_key, agg_key_version, open_slot, response_deadline, recovery_deadline)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`
	_, err = s.db.ExecContext(ctx, q, id, StateOpen, attrsJSON, assignedTo, attrs.Fingerprint, aggKey, aggKeyVersion, aggKey, responseDeadline, recoveryDeadline)
	if err != nil {
		var mysqlErr *mysql.MySQLError
		if errors.As(err, &mysqlErr) && mysqlErr.Number == 1062 && strings.Contains(mysqlErr.Message, "uniq_open_slot") {
			return nil, ErrDuplicateOpenAlert
		}
		return nil, fmt.Errorf("insert alert: %w", err)
	}

	return s.GetByID(ctx, id)
}

func (s *Store) GetByID(ctx context.Context, id string) (*Alert, error) {
	const q = `SELECT * FROM alert WHERE id = ?`
	var a Alert
	if err := s.db.GetContext(ctx, &a, q, id); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, fmt.Errorf("get alert: %w", err)
	}
	return &a, nil
}

// FindActiveByAggKey returns the active aggregate for an aggregation-key hash,
// or nil. open_slot is unique, so there is at most one active (open,
// non-detached) alert per key; resolved/detached history is excluded.
func (s *Store) FindActiveByAggKey(ctx context.Context, aggKey string) (*Alert, error) {
	const q = `SELECT * FROM alert WHERE open_slot = ?`
	var a Alert
	if err := s.db.GetContext(ctx, &a, q, aggKey); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, fmt.Errorf("find active alert by agg key: %w", err)
	}
	return &a, nil
}

// RehashActiveAggKeys recomputes the aggregation slot of every still-active
// alert left on an older recipe, so a dedup-key change (a new AggLabels set)
// does not strand open alerts under hashes that new occurrences will never
// produce, opening a duplicate alert for a group that already has one. Run once
// after deploying a recipe that bumps AggKeyVersion.
//
// Only alerts within the staleness window are touched: one created longer ago is
// detached on its next firing regardless (see isStale), so its slot is never
// reused and rehashing it would change nothing. Idempotent — the version
// predicate skips an alert already on currentVersion, and an alert whose new
// slot a freshly opened current-recipe alert already holds (the server went live
// before this ran) is detached rather than fought over, exactly as staleness
// would retire the older duplicate.
func (s *Store) RehashActiveAggKeys(ctx context.Context, currentVersion int, window time.Duration) (rehashed, detached int, err error) {
	const sel = `
		SELECT * FROM alert
		WHERE open_slot IS NOT NULL
		  AND agg_key_version < ?
		  AND created_at > ?
	`
	cutoff := time.Now().UTC().Add(-window)
	var alerts []Alert
	if err := s.db.SelectContext(ctx, &alerts, sel, currentVersion, cutoff); err != nil {
		return 0, 0, fmt.Errorf("select active alerts to rehash: %w", err)
	}

	for i := range alerts {
		a := &alerts[i]
		var attrs AlertAttrs
		if err := json.Unmarshal(a.Attrs, &attrs); err != nil {
			return rehashed, detached, fmt.Errorf("unmarshal attrs for %s: %w", a.ID, err)
		}
		newKey := AggKeyHash(AggKeyDesc(attrs.Classification, attrs.Labels))

		const upd = `UPDATE alert SET agg_key = ?, open_slot = ?, agg_key_version = ? WHERE id = ?`
		_, uerr := s.db.ExecContext(ctx, upd, newKey, newKey, currentVersion, a.ID)
		if uerr == nil {
			rehashed++
			continue
		}
		var mysqlErr *mysql.MySQLError
		if errors.As(uerr, &mysqlErr) && mysqlErr.Number == 1062 && strings.Contains(mysqlErr.Message, "uniq_open_slot") {
			// A current-recipe alert already owns this slot: an occurrence for the
			// same aggregate arrived after deploy but before this ran. It is now
			// the active aggregate, so retire this stale-recipe duplicate the way
			// a detach would — release the slot, keep the record.
			const det = `UPDATE alert SET detached = 1, open_slot = NULL, agg_key = ?, agg_key_version = ? WHERE id = ?`
			if _, derr := s.db.ExecContext(ctx, det, newKey, currentVersion, a.ID); derr != nil {
				return rehashed, detached, fmt.Errorf("detach superseded alert %s: %w", a.ID, derr)
			}
			detached++
			continue
		}
		return rehashed, detached, fmt.Errorf("rehash alert %s: %w", a.ID, uerr)
	}
	return rehashed, detached, nil
}

// Detach releases an alert's dedup slot without resolving it: the alert stays
// open (a human still closes it) but stops accreting new occurrences. Nulling
// open_slot frees the unique slot for a fresh alert of the same key.
func (s *Store) Detach(ctx context.Context, alertID string) error {
	const q = `UPDATE alert SET detached = 1, open_slot = NULL WHERE id = ?`
	_, err := s.db.ExecContext(ctx, q, alertID)
	if err != nil {
		return fmt.Errorf("detach alert: %w", err)
	}
	return nil
}

func (s *Store) UpdateState(ctx context.Context, id, state string) error {
	const q = `UPDATE alert SET state = ? WHERE id = ?`
	_, err := s.db.ExecContext(ctx, q, state, id)
	return err
}

func (s *Store) Ack(ctx context.Context, id, user, source string) error {
	const q = `UPDATE alert SET state = ?, acked_by = ?, ack_source = ?, acked_at = NOW(3) WHERE id = ?`
	_, err := s.db.ExecContext(ctx, q, StateAcked, user, source, id)
	return err
}

func (s *Store) Resolve(ctx context.Context, id, resolution, note string) error {
	// Nulling open_slot frees the dedup slot so a future firing of the same
	// aggregation group creates a new alert.
	const q = `UPDATE alert SET state = ?, resolution = ?, resolution_note = ?, resolved_at = NOW(3), open_fingerprint = NULL, open_slot = NULL WHERE id = ?`
	_, err := s.db.ExecContext(ctx, q, StateResolved, resolution, note, id)
	return err
}

// Unack returns an alert to open and clears the acknowledgement, so the record
// does not credit someone who has handed the alert on.
func (s *Store) Unack(ctx context.Context, id string) error {
	const q = `UPDATE alert SET state = ?, acked_by = '', ack_source = '', acked_at = NULL WHERE id = ?`
	_, err := s.db.ExecContext(ctx, q, StateOpen, id)
	return err
}

func (s *Store) Assign(ctx context.Context, id, assignee string) error {
	const q = `UPDATE alert SET assigned_to = ? WHERE id = ?`
	_, err := s.db.ExecContext(ctx, q, assignee, id)
	return err
}

func (s *Store) SetIMThread(ctx context.Context, id, threadID, messageID, channelID string) error {
	const q = `UPDATE alert SET im_thread_id = ?, im_message_id = ?, im_channel_id = ? WHERE id = ?`
	_, err := s.db.ExecContext(ctx, q, threadID, messageID, channelID, id)
	return err
}

func (s *Store) SetExternalIssueKey(ctx context.Context, id, issueKey string) error {
	const q = `UPDATE alert SET external_issue_key = ? WHERE id = ?`
	_, err := s.db.ExecContext(ctx, q, issueKey, id)
	return err
}

// FindByExternalIssueKey looks up an alert by its Jira key (transitional).
func (s *Store) FindByExternalIssueKey(ctx context.Context, key string) (*Alert, error) {
	const q = `SELECT * FROM alert WHERE external_issue_key = ? LIMIT 1`
	var a Alert
	if err := s.db.GetContext(ctx, &a, q, key); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, fmt.Errorf("find alert by issue key: %w", err)
	}
	return &a, nil
}

// FindByIMThreadID looks up an alert by its IM thread ID.
func (s *Store) FindByIMThreadID(ctx context.Context, threadID string) (*Alert, error) {
	const q = `SELECT * FROM alert WHERE im_thread_id = ? LIMIT 1`
	var a Alert
	if err := s.db.GetContext(ctx, &a, q, threadID); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, fmt.Errorf("find alert by thread id: %w", err)
	}
	return &a, nil
}

// ListExpirable returns IDs of alerts that are still open or acked but were
// created before cutoff, candidates for automatic expiry.
func (s *Store) ListExpirable(ctx context.Context, cutoff time.Time) ([]string, error) {
	const q = `SELECT id FROM alert WHERE state != ? AND created_at < ? ORDER BY created_at LIMIT 500`
	var ids []string
	if err := s.db.SelectContext(ctx, &ids, q, StateResolved, cutoff); err != nil {
		return nil, fmt.Errorf("list expirable alerts: %w", err)
	}
	return ids, nil
}

type ListFilter struct {
	// Classifications, States and OncallTeams are OR-within/AND-across sets: an
	// alert matches when its value is one of the listed classifications AND one
	// of the listed states AND one of the listed teams. Each empty slice means
	// "any", so the list bar's multi-select controls narrow only the facets a
	// user actually picked. The remaining fields stay single-valued exact
	// matches.
	Classifications []string
	States          []string
	Alertname       string
	Region          string
	// BizRegion is the tenant's sales region and TenantKind its
	// internal/external split, both from the DevOps ops portal. Region above is
	// the cloud region — filtering by one never means the other.
	BizRegion    string
	TenantKind   string
	CustomerType string
	AssignedTo   string
	ClusterID    string
	TenantID     string
	// OncallTeams matches the alert's oncall_team label, read out of the JSON
	// attrs column rather than a generated column — there is no oncall_team
	// column to index, so this is the one filter here that scans the expression
	// per row. Acceptable for a paged UI list.
	OncallTeams []string
	Limit       int
	Offset      int
}

func (s *Store) List(ctx context.Context, f ListFilter) ([]Alert, error) {
	q := "SELECT * FROM alert WHERE 1=1"
	args := []interface{}{}

	q, args = appendInFilter(q, args, "classification", f.Classifications)
	q, args = appendInFilter(q, args, "state", f.States)
	if f.Alertname != "" {
		q += " AND alertname = ?"
		args = append(args, f.Alertname)
	}
	if f.Region != "" {
		q += " AND region = ?"
		args = append(args, f.Region)
	}
	if f.BizRegion != "" {
		q += " AND biz_region = ?"
		args = append(args, f.BizRegion)
	}
	if f.TenantKind != "" {
		q += " AND tenant_kind = ?"
		args = append(args, f.TenantKind)
	}
	if f.CustomerType != "" {
		q += " AND customer_type = ?"
		args = append(args, f.CustomerType)
	}
	if f.AssignedTo != "" {
		q += " AND assigned_to = ?"
		args = append(args, f.AssignedTo)
	}
	if f.ClusterID != "" {
		q += " AND cluster_id = ?"
		args = append(args, f.ClusterID)
	}
	if f.TenantID != "" {
		q += " AND tenant_id = ?"
		args = append(args, f.TenantID)
	}
	q, args = appendInFilter(q, args, oncallTeamExpr, f.OncallTeams)

	q += " ORDER BY created_at DESC"

	if f.Limit <= 0 {
		f.Limit = 50
	}
	q += " LIMIT ? OFFSET ?"
	args = append(args, f.Limit, f.Offset)

	var alerts []Alert
	if err := s.db.SelectContext(ctx, &alerts, q, args...); err != nil {
		return nil, fmt.Errorf("list alerts: %w", err)
	}
	return alerts, nil
}

type AlertCounts struct {
	All      int `json:"all"      db:"all_count" validate:"required"`
	Pages    int `json:"pages"    db:"page_count" validate:"required"`
	Tickets  int `json:"tickets"  db:"ticket_count" validate:"required"`
	Open     int `json:"open"     db:"open_count" validate:"required"`
	Acked    int `json:"acked"    db:"acked_count" validate:"required"`
	Resolved int `json:"resolved" db:"resolved_count" validate:"required"`
}

func (s *Store) Counts(ctx context.Context) (*AlertCounts, error) {
	const q = `
		SELECT
			COUNT(*) AS all_count,
			SUM(classification = 'page' AND state != 'resolved') AS page_count,
			SUM(classification = 'ticket' AND state != 'resolved') AS ticket_count,
			SUM(state = 'open') AS open_count,
			SUM(state = 'acked') AS acked_count,
			SUM(state = 'resolved') AS resolved_count
		FROM alert
	`
	var c AlertCounts
	if err := s.db.GetContext(ctx, &c, q); err != nil {
		return nil, fmt.Errorf("alert counts: %w", err)
	}
	return &c, nil
}