package console

import (
	"context"
	"errors"

	"github.com/carlosframework/platform/internal/hostops"
	"github.com/carlosframework/platform/internal/release"
)

// The mail tables (mail_sender_counts, mail_events, mail_event_hooks,
// mail_event_deliveries) key an app by its account SQID, not the account id
// the generated adapters take, so they get this one adapter instead. Every
// write to them goes through it, scoped to exactly the app it writes:
//
//   - Inside a context that already holds a permit for the app (a door's
//     release.WithAppMutation / Server.claimAppMutation, or a mail
//     admission's context), RunSQLIngest joins it: one operation for the
//     whole door, never one per statement.
//   - Otherwise (the SES ingest, the push worker) it opens that app's own
//     shared SQL ingest registration around the write. The non-expiring
//     mail delivery operation itself is unchanged.
//
// A deployment-wide statement (the retention prunes) is split per app from a
// fresh read of the rows it would touch, so each app's rows go under that
// app's permit and no write is ever unscoped.
//
// Flag-off (no SQL gate) and inside an already bound view, call runs on s
// unchanged, exactly as before.
func (s *Store) withMailAppSQL(ctx context.Context, sqid, app, action string, call func(*Store) error) error {
	if s.gate == nil || s.bound != nil {
		return call(s)
	}
	if s.outerInsideView() {
		return errOuterStoreInView
	}
	if !release.ValidAccount(sqid) || !release.ValidApp(app) {
		return hostops.ErrIdentity
	}
	acct, err := s.AccountBySqid(sqid)
	if err != nil {
		return err
	}
	if acct == nil {
		return hostops.ErrIdentity
	}
	return s.withAutoAppSQL(ctx, acct.ID, app, action, call)
}

type mailAppKey struct{ Account, App string }

// mailAppsWith lists the distinct apps with rows matching where in table: the
// affected app set of a deployment-wide statement. The rows are closed
// before any write starts (the gated store has one connection).
func (s *Store) mailAppsWith(ctx context.Context, table, where string, args ...any) ([]mailAppKey, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT DISTINCT account, app FROM `+table+` WHERE `+where+` ORDER BY account, app`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []mailAppKey
	for rows.Next() {
		var k mailAppKey
		if err := rows.Scan(&k.Account, &k.App); err != nil {
			return nil, err
		}
		out = append(out, k)
	}
	return out, rows.Err()
}

// pruneMailAppRows deletes table's rows with col < cutoff one app at a time,
// each under that app's own mutation. A refused app (an operation holds it,
// or its account is gone) keeps its rows until a later pass; the others
// still go.
func (s *Store) pruneMailAppRows(ctx context.Context, table, col string, cutoff int64, action string) error {
	apps, err := s.mailAppsWith(ctx, table, col+` < ?`, cutoff)
	if err != nil {
		return err
	}
	var errs []error
	for _, k := range apps {
		k := k
		errs = append(errs, s.withMailAppSQL(ctx, k.Account, k.App, action, func(v *Store) error {
			_, err := v.db.ExecContext(ctx, `DELETE FROM `+table+` WHERE account = ? AND app = ? AND `+col+` < ?`, k.Account, k.App, cutoff)
			return err
		}))
	}
	return errors.Join(errs...)
}