package event

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

	"github.com/jmoiron/sqlx"
)

type Store struct {
	db *sqlx.DB
}

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

func (s *Store) Insert(ctx context.Context, e *Event) error {
	const q = `
		INSERT INTO event (
			receipt_id, instance_id, group_key, fingerprint, status,
			observation_type, attrs, starts_at, ends_at, received_at
		)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`
	if e.ReceivedAt.IsZero() {
		e.ReceivedAt = time.Now().UTC().Round(time.Millisecond)
	}
	res, err := s.db.ExecContext(ctx, q,
		e.ReceiptID, e.InstanceID, e.GroupKey, e.Fingerprint, e.Status,
		e.ObservationType, e.Attrs, e.StartsAt, e.EndsAt, e.ReceivedAt,
	)
	if err != nil {
		return fmt.Errorf("insert event: %w", err)
	}
	e.ID, err = res.LastInsertId()
	if err != nil {
		return fmt.Errorf("get inserted event id: %w", err)
	}
	return nil
}

// InsertDedup inserts the event unless an identical notification (same
// fingerprint, status, starts_at) was already received within the window.
// This collapses webhook redeliveries (5xx retries, HA alertmanager
// double-sends, all within minutes) into one row, while repeat_interval
// re-notifications (hours apart) still get their own rows — each of those
// is a real notification, which future subscription features rely on.
// Returns whether a row was inserted.
func (s *Store) InsertDedup(ctx context.Context, e *Event, window time.Duration) (bool, error) {
	// Round to ms to match the TIMESTAMP(3) column, so the equality
	// check below compares like with like.
	e.StartsAt = e.StartsAt.Round(time.Millisecond)

	const qFind = `
		SELECT id, received_at FROM event
		WHERE fingerprint = ? AND status = ? AND starts_at = ?
		  AND received_at > DATE_SUB(NOW(3), INTERVAL ? SECOND)
		LIMIT 1
	`
	var found struct {
		ID         int64     `db:"id"`
		ReceivedAt time.Time `db:"received_at"`
	}
	err := s.db.GetContext(ctx, &found, qFind, e.Fingerprint, e.Status, e.StartsAt, int(window.Seconds()))
	if err == nil {
		e.ID = found.ID
		e.ReceivedAt = found.ReceivedAt
		return false, nil // duplicate within window
	}
	if !errors.Is(err, sql.ErrNoRows) {
		return false, fmt.Errorf("check duplicate event: %w", err)
	}

	if err := s.Insert(ctx, e); err != nil {
		return false, err
	}
	return true, nil
}

// ListByAlertID returns all observations belonging to occurrences bound to an
// alert. A single occurrence may be bound to more than one human alert.
func (s *Store) ListByAlertID(ctx context.Context, alertID string) ([]Event, error) {
	const q = `
		SELECT e.*
		FROM event e
		JOIN alert_instance_binding b ON b.instance_id = e.instance_id
		WHERE b.alert_id = ?
		ORDER BY e.received_at, e.id
	`
	var events []Event
	if err := s.db.SelectContext(ctx, &events, q, alertID); err != nil {
		return nil, fmt.Errorf("list alert events: %w", err)
	}
	return events, nil
}

func (s *Store) ListReceiptInfoByAlertID(ctx context.Context, alertID string) ([]ReceiptInfo, error) {
	const q = `
		SELECT DISTINCT
			r.id, r.receiver, r.group_key, r.truncated_alerts,
			r.notification_reason, r.received_at
		FROM am_webhook_receipt r
		JOIN event e ON e.receipt_id = r.id
		JOIN alert_instance_binding b ON b.instance_id = e.instance_id
		WHERE b.alert_id = ?
		ORDER BY r.received_at, r.id
	`
	var receipts []ReceiptInfo
	if err := s.db.SelectContext(ctx, &receipts, q, alertID); err != nil {
		return nil, fmt.Errorf("list alert receipt info: %w", err)
	}
	return receipts, nil
}

type ListFilter struct {
	Alertname string
	// Classifications and Statuses are OR-within/AND-across sets: an event
	// matches when its classification is one of the listed ones AND its status
	// is one of the listed ones. Each empty slice means "any", so the event
	// list's multi-select controls narrow only the facets a user picked.
	Classifications []string
	ClusterID       string
	TenantID        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
	Statuses         []string
	ObservationTypes []string
	Limit            int
	Offset           int
}

// appendInFilter adds a `col IN (?, ...)` clause for the members of vals,
// leaving q/args untouched when vals is empty. col is spliced in literally, so
// it must be a trusted column name (never user input) — only the values are
// parameterised. Lets the multi-value filters build their clauses uniformly.
func appendInFilter(q string, args []interface{}, col string, vals []string) (string, []interface{}) {
	if len(vals) == 0 {
		return q, args
	}
	placeholders := make([]string, len(vals))
	for i, v := range vals {
		placeholders[i] = "?"
		args = append(args, v)
	}
	return q + " AND " + col + " IN (" + strings.Join(placeholders, ", ") + ")", args
}

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

	if f.Alertname != "" {
		q += " AND alertname = ?"
		args = append(args, f.Alertname)
	}
	q, args = appendInFilter(q, args, "classification", f.Classifications)
	if f.ClusterID != "" {
		q += " AND cluster_id = ?"
		args = append(args, f.ClusterID)
	}
	if f.TenantID != "" {
		q += " AND tenant_id = ?"
		args = append(args, f.TenantID)
	}
	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)
	}
	q, args = appendInFilter(q, args, "status", f.Statuses)
	q, args = appendInFilter(q, args, "observation_type", f.ObservationTypes)

	q += " ORDER BY received_at DESC"

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

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

func (s *Store) GetByID(ctx context.Context, id int64) (*Event, error) {
	const q = `SELECT * FROM event WHERE id = ?`
	var e Event
	if err := s.db.GetContext(ctx, &e, q, id); err != nil {
		return nil, fmt.Errorf("get event: %w", err)
	}
	return &e, nil
}

// InsertFromWebhook creates an Event from raw alertmanager data and inserts it.
func (s *Store) InsertFromWebhook(ctx context.Context, groupKey, fingerprint, status string, attrs *EventAttrs, startsAt, endsAt *string) (*Event, error) {
	attrsJSON, err := json.Marshal(attrs)
	if err != nil {
		return nil, fmt.Errorf("marshal attrs: %w", err)
	}

	e := &Event{
		GroupKey:    groupKey,
		Fingerprint: fingerprint,
		Status:      status,
		Attrs:       attrsJSON,
	}

	if err := s.Insert(ctx, e); err != nil {
		return nil, err
	}
	return e, nil
}