package console

import (
	"database/sql"
	"encoding/json"
	"errors"
	"time"

	"github.com/carlosframework/platform/internal/plans"
)

// The sources an account's plan row can have. "default" rows are the ones
// EnsureDefaultPlans inserts for accounts nobody has assigned.
const (
	PlanSourceDefault   = "default"
	PlanSourceOperator  = "operator"
	PlanSourceSponsored = "sponsored"
	PlanSourceBilling   = "billing"
)

// ErrAccountGone refuses a plan write for an account that does not exist or
// has been deleted.
var ErrAccountGone = errors.New("account deleted")

// planDefaultActor is who a default row names as its writer.
const planDefaultActor = "plans"

// AccountPlanRow is one account's plan assignment.
type AccountPlanRow struct {
	AccountID         int64
	Sqid              string
	Plan              string
	Packs             map[string]int
	Source            string
	Sponsored         *plans.Limits
	Reason            string
	Revision          int64
	PublishedRevision int64
	PublishedCatalog  string
	UpdatedBy         string
	UpdatedAt         time.Time
}

const accountPlanColumns = `p.account_id, a.sqid, p.plan, p.packs, p.source, p.sponsored, p.reason,
 p.revision, p.published_revision, p.published_catalog, p.updated_by, p.updated_at`

func scanAccountPlan(sc interface{ Scan(...any) error }) (*AccountPlanRow, error) {
	var r AccountPlanRow
	var packs, sponsored string
	var updated int64
	if err := sc.Scan(&r.AccountID, &r.Sqid, &r.Plan, &packs, &r.Source, &sponsored, &r.Reason,
		&r.Revision, &r.PublishedRevision, &r.PublishedCatalog, &r.UpdatedBy, &updated); err != nil {
		return nil, err
	}
	if err := json.Unmarshal([]byte(packs), &r.Packs); err != nil {
		return nil, err
	}
	if sponsored != "" {
		r.Sponsored = &plans.Limits{}
		if err := json.Unmarshal([]byte(sponsored), r.Sponsored); err != nil {
			return nil, err
		}
	}
	r.UpdatedAt = time.Unix(updated, 0)
	return &r, nil
}

// AccountPlan is the account's plan row, or nil when it has none.
func (s *Store) AccountPlan(accountID int64) (*AccountPlanRow, error) {
	r, err := scanAccountPlan(s.db.QueryRow(`SELECT `+accountPlanColumns+`
 FROM account_plans p JOIN accounts a ON a.id = p.account_id WHERE p.account_id = ?`, accountID))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	return r, err
}

// SetAccountPlan writes the account's plan and returns its new revision.
// It validates nothing: callers check the plan against the catalog first.
func (s *Store) SetAccountPlan(accountID int64, plan string, packs map[string]int, source string,
	sponsored *plans.Limits, reason, by string, now time.Time) (int64, error) {
	if packs == nil {
		packs = map[string]int{}
	}
	pj, err := json.Marshal(packs)
	if err != nil {
		return 0, err
	}
	sj := ""
	if sponsored != nil {
		b, err := json.Marshal(sponsored)
		if err != nil {
			return 0, err
		}
		sj = string(b)
	}
	var rev int64
	err = s.db.QueryRow(`INSERT INTO account_plans
 (account_id, plan, packs, source, sponsored, reason, revision, updated_by, updated_at)
 SELECT ?, ?, ?, ?, ?, ?, 1, ?, ? WHERE EXISTS (SELECT 1 FROM accounts WHERE id = ? AND deleted_at = 0)
 ON CONFLICT(account_id) DO UPDATE SET plan = excluded.plan, packs = excluded.packs,
  source = excluded.source, sponsored = excluded.sponsored, reason = excluded.reason,
  updated_by = excluded.updated_by, updated_at = excluded.updated_at, revision = revision + 1
 RETURNING revision`,
		accountID, plan, string(pj), source, sj, reason, by, now.Unix(), accountID).Scan(&rev)
	if errors.Is(err, sql.ErrNoRows) {
		return 0, ErrAccountGone
	}
	return rev, err
}

// EnsureDefaultPlans gives every live account without a plan row the
// default plan, and reports how many rows it inserted.
func (s *Store) EnsureDefaultPlans(defaultPlan string, now time.Time) (int, error) {
	res, err := s.db.Exec(`INSERT INTO account_plans
 (account_id, plan, packs, source, revision, updated_by, updated_at)
 SELECT id, ?, '{}', ?, 1, ?, ? FROM accounts
 WHERE deleted_at = 0 AND id NOT IN (SELECT account_id FROM account_plans)`,
		defaultPlan, PlanSourceDefault, planDefaultActor, now.Unix())
	if err != nil {
		return 0, err
	}
	n, err := res.RowsAffected()
	return int(n), err
}

// PlansToPublish lists live accounts whose bucket record is behind their
// row (an unpublished revision, or a record from another catalog), in
// account order after afterAccountID, so a caller can page past rows it
// cannot publish.
func (s *Store) PlansToPublish(catalogID string, afterAccountID int64, limit int) ([]AccountPlanRow, error) {
	rows, err := s.db.Query(`SELECT `+accountPlanColumns+`
 FROM account_plans p JOIN accounts a ON a.id = p.account_id
 WHERE a.deleted_at = 0 AND p.account_id > ? AND (p.published_revision < p.revision OR p.published_catalog != ?)
 ORDER BY p.account_id LIMIT ?`, afterAccountID, catalogID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []AccountPlanRow
	for rows.Next() {
		r, err := scanAccountPlan(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, *r)
	}
	return out, rows.Err()
}

// MarkPlanPublished records that the bucket carries revision under
// catalogID. A revision that is no longer current marks nothing: the newer
// one still needs publishing.
func (s *Store) MarkPlanPublished(accountID, revision int64, catalogID string) error {
	_, err := s.db.Exec(`UPDATE account_plans SET published_revision = ?, published_catalog = ?
 WHERE account_id = ? AND revision = ?`, revision, catalogID, accountID, revision)
	return err
}

// LiveAppCount counts the account's running apps for the creation caps:
// live rows and rows being restored (temporary apps included).
func (s *Store) LiveAppCount(accountID int64) (int, error) {
	var n int
	err := s.db.QueryRow(`SELECT COUNT(*) FROM apps WHERE account_id = ? AND (deleted_at = 0 OR restoring = 1)`, accountID).Scan(&n)
	return n, err
}

// RestoringAppsIn names the account's apps that are mid-restore.
func (s *Store) RestoringAppsIn(accountID int64) ([]string, error) {
	rows, err := s.db.Query(`SELECT app FROM apps WHERE account_id = ? AND deleted_at != 0 AND restoring = 1 ORDER BY app`, accountID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var a string
		if err := rows.Scan(&a); err != nil {
			return nil, err
		}
		out = append(out, a)
	}
	return out, rows.Err()
}