# Design Documents

## Table of Contents

- [Design Documents](#design-documents)
  - [Table of Contents](#table-of-contents)
  - [Introduction](#introduction)
  - [Motivation or Background](#motivation-or-background)
  - [Detailed Design](#detailed-design)
    - [Goals and Non-goals](#goals-and-non-goals)
    - [Assumptions](#assumptions)
    - [High-level Flow](#high-level-flow)
    - [Component Changes](#component-changes)
      - [1) New Auto Credit Service](#1-new-auto-credit-service)
      - [2) Google Sheet Reader](#2-google-sheet-reader)
      - [3) Tenant Lookup](#3-tenant-lookup)
      - [4) Billing Client Extension](#4-billing-client-extension)
      - [5) Credit Creation Policy](#5-credit-creation-policy)
    - [Idempotency Strategy](#idempotency-strategy)
    - [Configuration Design](#configuration-design)
    - [Error Handling and Retry](#error-handling-and-retry)
    - [Operational Notes](#operational-notes)
  - [Test Design](#test-design)
    - [Functional Tests](#functional-tests)
    - [Scenario Tests](#scenario-tests)
    - [Compatibility Tests](#compatibility-tests)
  - [Impacts \& Risks](#impacts--risks)
  - [Unresolved Questions](#unresolved-questions)

## Introduction

This document describes a temporary automation in `devops-work` that periodically scans a Google Sheet, looks up tenants by email, checks whether a specific auto-credit has already been granted, and creates a new credit package when needed.

The current agreed implementation assumes the email in the sheet is the tenant/org email, not a member user email.

## Motivation or Background

The business team maintains a Google Sheet containing target emails. The backend needs to periodically process that sheet and automatically grant a fixed amount of credit to matching tenants.

The implementation should fit the current repository patterns:

1. Run as a backend cron job.
2. Reuse existing tenant and billing integrations.
3. Avoid introducing new database tables because this is a temporary feature.
4. Use existing credit history as the source of truth for idempotency.

## Detailed Design

### Goals and Non-goals

Goals:

1. Periodically read a configured Google Sheet range.
2. Normalize and de-duplicate emails from the sheet.
3. Find matching tenants by tenant/org email.
4. Check historical credits for a tenant and skip if the designated auto-credit already exists.
5. Create a fixed-value credit package automatically when needed.
6. Support `dryRun` for safe rollout.

Non-goals:

1. No new persistent state table in `devops-work`.
2. No support for member user email lookup in the first implementation.
3. No sheet write-back in the first implementation.
4. No generic workflow engine for future campaigns.

### Assumptions

This design is based on the following assumptions:

1. The email column in the Google Sheet contains tenant/org email.
2. Tenant lookup can use the existing tenant repository search path because tenant search already matches `lower(email)`.
3. Historical credit list in billing includes the `note` field.
4. A specific fixed `note` value or note prefix is enough to determine whether the tenant has already been credited by this automation.
5. The cron job runs in a single active executor, or duplicate execution risk is acceptable for this temporary feature.

If any of these assumptions become false, this design should be revised instead of patched ad hoc.

### High-level Flow

For each cron run:

1. Read emails from Google Sheet.
2. Normalize emails with `trim + lowercase`.
3. Remove empty values and duplicates.
4. For each email:
5. Query tenant by email.
6. If no tenant matches, log and skip.
7. If multiple tenants match, log and skip.
8. Query billing config for the tenant and select the active config.
9. Query historical credits under that billing config.
10. If a credit with the configured note marker already exists, skip.
11. Otherwise create a new credit package with configured amount and metadata.

Pseudo-code:

```go
func Run(ctx context.Context) error {
    emails := sheetReader.ReadEmails(ctx)
    for _, email := range normalizeAndDedupe(emails) {
        tenant, found, err := tenantFinder.FindByEmail(ctx, email)
        if err != nil || !found {
            continue
        }

        billingConfig, found, err := billingCli.FindActiveBillingConfig(ctx, tenant.ID)
        if err != nil || !found {
            continue
        }

        credited, err := billingCli.HasCreditNote(ctx, tenant.ID, billingConfig.ID, noteFor(email))
        if err != nil {
            continue
        }
        if credited {
            continue
        }

        if conf.DryRun {
            log.Info("dry run create credit", "tenant_id", tenant.ID, "email", email)
            continue
        }

        err = billingCli.CreateCreditsWithBillingConfig(ctx, tenant.ID, billingConfig.ID, buildReq(email))
        if err != nil {
            continue
        }
    }
    return nil
}
```

### Component Changes

#### 1) New Auto Credit Service

Add a new service under `pkg/service/autocredit`.

Suggested files:

```text
pkg/service/autocredit/service.go
pkg/service/autocredit/sheet_reader.go
pkg/service/autocredit/types.go
pkg/service/autocredit/service_test.go
```

The service should follow the existing `JobService` pattern and register a cron job with `service.MustNewCronJob(...)`.

The service is responsible for:

1. Scheduling.
2. Per-run mutex protection inside the process.
3. Orchestrating sheet read, tenant lookup, billing lookup, idempotency check, and credit creation.
4. Logging summary metrics for each run.

#### 2) Google Sheet Reader

Extend the existing Google client usage with a small Sheets reader.

Current code already loads Google credentials from AWS Secret Manager:

- `pkg/infra/clients/googlecli/google_cli.go`

The implementation can add a read-only Sheets helper under the same client package, for example:

```text
pkg/infra/clients/googlecli/sheets_cli.go
```

Responsibilities:

1. Read a configured `sheetID` and `range`.
2. Return raw rows or a simple `[]string` email list.
3. Keep parsing rules minimal: use one configured column or the first column in the selected range.

This feature should use read-only Sheets scope.

Current temporary defaults:

1. `sheetID = 1XWsXQ9tRdm7jHur945XyS3sKC1lDbky83RF7I9gIlGs`
2. `sheetRange = Sheet1!A:A`

#### 3) Tenant Lookup

Use the existing tenant search capability, with exact post-filtering on normalized email.

Current tenant search already matches tenant email in repo search:

- `pkg/service/tenant/repo/option.go`

Implementation approach:

1. Call `tenantSvc.ListTenants(...)` or directly query tenant repo with `Search(email)`.
2. From returned candidates, keep only tenants where `strings.EqualFold(tenant.Email, email)`.
3. Require exactly one exact match.
4. If zero or multiple exact matches exist, skip and log.

This keeps the implementation small and avoids introducing a new repo API for a temporary feature.

#### 4) Billing Client Extension

The current `billingcli` only covers the existing tenant credit operation path and tenant plan lookup. The automation needs the billing-config-based APIs used by the portal credit page.

Extend `pkg/infra/clients/billingcli/client.go` with methods similar to:

```go
ListBillingConfigs(ctx context.Context, tenantID uint64) ([]*BillingConfig, error)
ListCredits(ctx context.Context, tenantID uint64, billingConfigID uint64) ([]*CreditsPackage, error)
CreateCreditsWithBillingConfig(ctx context.Context, tenantID uint64, billingConfigID uint64, req *CreateCreditsWithBillingConfigReq) error
```

Target backend paths:

1. `GET /api/v1/billing/tenants/{tenant_id}/billing_configs`
2. `GET /api/v1/billing/tenants/{tenant_id}/billing_config_id/{billing_config_id}/credits`
3. `POST /api/v1/billing/tenants/{tenant_id}/billing_config_id/{billing_config_id}/credits`

The new request type should match the billing credit API actually used by the frontend.

#### 5) Credit Creation Policy

The auto-created credit request should be mostly configuration-driven.

Expected request fields:

1. `usage`
2. `bind_types`
3. `provider`
4. `discount_enabled`
5. `total_credits`
6. `start_date`
7. `expired_date`
8. `note`

Recommended defaults:

1. `total_credits`: fixed `100`
2. `start_date`: cron execution time in UTC
3. `expired_date`: configured duration from start date, default 30 days
4. `note`: stable marker containing campaign identity and email

Example note:

```text
AUTO_CREDIT_SHEET_20260417:tenant_email=foo@example.com
```

### Idempotency Strategy

No new table will be introduced.

Idempotency is defined by billing credit history:

1. Before creating credit, list historical credits for the selected billing config.
2. Compare the `note` field against the configured marker.
3. If any record already contains the exact marker for this email and campaign, skip creation.

Recommended rule:

1. Use an exact note value instead of fuzzy substring matching when possible.
2. Include campaign identity in note so that multiple temporary campaigns do not conflict.

This strategy is acceptable for a temporary feature, but it is weaker than DB-backed idempotency because it depends on billing read-after-write visibility and single-runner execution assumptions.

### Configuration Design

Add a dedicated config block under `services`.

Example:

```yaml
services:
  autoCredit:
    cronDisable: false
    cronSpec: "0 */6 * * *"
    sheetID: "1XWsXQ9tRdm7jHur945XyS3sKC1lDbky83RF7I9gIlGs"
    sheetRange: "Sheet1!A:A"
    creditAmount: "100"
    usage: "startup_program"
    provider: "aws"
    discountEnabled: true
    notePrefix: "AUTO_CREDIT_SHEET_20260417"
    expiredAfter: "720h"
    dryRun: true
```

Suggested config fields:

1. `cronDisable`
2. `cronSpec`
3. `sheetID`
4. `sheetRange`
5. `creditAmount`
6. `usage`
7. `provider`
8. `discountEnabled`
9. `notePrefix`
10. `expiredAfter`
11. `dryRun`

If billing requires `bind_types`, they can be provided as static configured type IDs for this temporary campaign.

### Error Handling and Retry

Per-email processing should be isolated. One failure must not fail the whole batch.

Rules:

1. Sheet read failure: fail the cron run and rely on next scheduled retry.
2. Tenant lookup failure for one email: log and continue.
3. No tenant match: log and continue.
4. Multiple tenant matches: log and continue.
5. No active billing config: log and continue.
6. Billing list/create failure: log and continue.

The cron summary log should include:

1. total rows read
2. valid emails
3. tenants matched
4. skipped because already credited
5. skipped because no tenant
6. skipped because no billing config
7. created count
8. failed count

### Operational Notes

Recommended rollout:

1. Deploy with `dryRun: true`.
2. Run for several cycles and verify logs manually.
3. Confirm tenant matches and note marker format.
4. Turn off `dryRun` for actual credit creation.

To reduce duplicate-credit risk:

1. Prefer a single cron executor.
2. Keep `note` deterministic.
3. Avoid running multiple copies of the same job concurrently.

## Test Design

### Functional Tests

1. Normalize and de-duplicate sheet emails.
2. Exact-match tenant selection by email.
3. Active billing config selection.
4. Existing note detection from credit history.
5. Credit request generation.

### Scenario Tests

1. Sheet contains duplicates with different case.
2. Sheet contains empty rows.
3. Tenant exists and already has matching note.
4. Tenant exists but no active billing config.
5. Billing create request succeeds.
6. Billing create request fails for one email while others continue.
7. `dryRun` mode logs intended writes without calling create API.

### Compatibility Tests

1. Verify no existing tenant controller API behavior changes.
2. Verify no existing billing client methods regress.
3. Verify service startup and cron registration continue to work with other services.
4. Verify config loading remains backward compatible when `autoCredit` block is absent or disabled.

## Impacts & Risks

Impacts:

1. Reduces manual credit operations for the temporary campaign.
2. Reuses current backend scheduling and integration patterns.
3. Keeps implementation cost low by avoiding new schema work.

Risks:

1. If multiple service replicas execute the cron concurrently, duplicate credits are possible.
2. If tenant email data is stale relative to account source, matching may be delayed or missed.
3. If note format changes manually, idempotency checks may fail.
4. If billing credit list is large, per-tenant list-and-scan cost may grow.
5. If sheet format changes without config update, processing may silently skip valid rows.

## Unresolved Questions

1. Which exact billing `usage`, `provider`, and `bind_types` should be used for this campaign?
2. What credit expiry policy should be used?
3. Is the cron guaranteed to run on only one active replica?
4. Should unmatched emails be reported somewhere other than logs?
5. Should the note be exact-match only, or note prefix plus normalized email?