// apps/api/internal/models/tenant_backup.go
package models
 
import (
    "time"
    "github.com/google/uuid"
    "gorm.io/gorm"
)
 
type TenantBackup struct {
    ID string `gorm:"primarykey;size:36" json:"id"`
 
    // Scoping. On single-tenant apps, drop this or set it to
    // an installation ID.
    BusinessID string `gorm:"size:36;not null;index" json:"business_id"`
 
    // Kind: WEEKLY_AUTO (cron) | MANUAL (operator) | FULL_DATABASE (admin)
    Kind string `gorm:"size:20;not null;index" json:"kind"`
 
    // Lifecycle: RUNNING → READY / FAILED, then PURGED by retention.
    Status string `gorm:"size:20;not null;index" json:"status"`
 
    GeneratedByUserID *string `gorm:"size:36"`
 
    // R2 key — unique per backup so a concurrent generate can't
    // clobber a fresh file mid-write.
    R2Key     string `gorm:"size:255"`
    SizeBytes int64  `gorm:"default:0"`
    RowCounts string `gorm:"type:text"` // JSON array
 
    StartedAt   time.Time  `gorm:"index"`
    CompletedAt *time.Time
    Error       string `gorm:"type:text"`
 
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index"`
}
 
func (t *TenantBackup) BeforeCreate(tx *gorm.DB) error {
    if t.ID == "" {
        t.ID = uuid.New().String()
    }
    if t.StartedAt.IsZero() {
        t.StartedAt = time.Now()
    }
    if t.Status == "" {
        t.Status = "RUNNING"
    }
    return nil
}

var backupTables = []string{
    // Configuration / master data — parents first
    "business_categories", "businesses", "branches", "users",
    "units", "product_categories", "accounts",
    "products", "product_variants", "product_lots",
    "customers", "suppliers",
 
    // Stock
    "stock_batches", "stock_movements",
 
    // Transactions — children last
    "purchases", "purchase_items",
    "sales", "sale_items",
 
    // Money
    "account_transactions", "debtor_transactions",
    "creditor_transactions", "expenses",
}

func (s *Service) dumpTable(
    ctx context.Context, table, businessID string,
    csvOut io.Writer, sqlOut io.Writer,
) (int64, error) {
    // Two exceptions in our schema:
    //   business_categories is a global lookup — export all rows.
    //   businesses is the tenant row itself — filter by id.
    var whereClause string
    var args []interface{}
    switch table {
    case "business_categories":
        whereClause = ""
    case "businesses":
        whereClause = " WHERE id = $1"
        args = []interface{}{businessID}
    default:
        whereClause = " WHERE business_id = $1"
        args = []interface{}{businessID}
    }
    // Include soft-deleted rows — the backup is the tenant's
    // insurance file, so keep anything they might want later.
    query := fmt.Sprintf("SELECT * FROM %s%s", table, whereClause)
 
    sqlDB, _ := s.DB.WithContext(ctx).DB()
    rows, err := sqlDB.QueryContext(ctx, query, args...)
    if err != nil {
        return 0, fmt.Errorf("query %s: %w", table, err)
    }
    defer rows.Close()
 
    columns, _ := rows.Columns()
    colTypes, _ := rows.ColumnTypes()
 
    cw := csv.NewWriter(csvOut)
    cw.Write(columns)
    fmt.Fprintf(sqlOut, "\n-- Table: %s\n", table)
 
    var count int64
    for rows.Next() {
        values := make([]interface{}, len(columns))
        ptrs := make([]interface{}, len(columns))
        for i := range values {
            ptrs[i] = &values[i]
        }
        rows.Scan(ptrs...)
 
        cells := make([]string, len(columns))
        for i, v := range values {
            cells[i] = csvFormat(v)
        }
        cw.Write(cells)
 
        lits := make([]string, len(columns))
        for i, v := range values {
            lits[i] = sqlFormat(v, colTypes[i])
        }
        fmt.Fprintf(sqlOut, "INSERT INTO %s (%s) VALUES (%s);\n",
            table,
            strings.Join(columns, ", "),
            strings.Join(lits, ", "),
        )
        count++
    }
    cw.Flush()
    return count, cw.Error()
}

// csvFormat renders a scanned value for a CSV cell.
func csvFormat(v interface{}) string {
    switch val := v.(type) {
    case nil:
        return ""
    case []byte:
        return string(val)
    case time.Time:
        return val.UTC().Format(time.RFC3339)
    case bool:
        if val {
            return "true"
        }
        return "false"
    default:
        return fmt.Sprintf("%v", val)
    }
}
 
// sqlFormat renders a Postgres-safe literal for an INSERT.
func sqlFormat(v interface{}, colType *sql.ColumnType) string {
    if v == nil {
        return "NULL"
    }
    switch val := v.(type) {
    case []byte:
        // Postgres JSONB / TEXT / BYTEA all come through as []byte.
        // Distinguish by column type name.
        tn := strings.ToLower(colType.DatabaseTypeName())
        if strings.Contains(tn, "json") {
            return sqlQuote(string(val))
        }
        if strings.Contains(tn, "bytea") {
            return fmt.Sprintf("'\\x%x'::bytea", val)
        }
        return sqlQuote(string(val))
    case string:
        return sqlQuote(val)
    case time.Time:
        return sqlQuote(val.UTC().Format(time.RFC3339))
    case bool:
        if val {
            return "TRUE"
        }
        return "FALSE"
    case int, int64, float64:
        return fmt.Sprintf("%v", val)
    default:
        return sqlQuote(fmt.Sprintf("%v", val))
    }
}
 
func sqlQuote(s string) string {
    return "'" + strings.ReplaceAll(s, "'", "''") + "'"
}

func (s *Service) produceAndUpload(
    ctx context.Context, businessID string,
    biz *models.Business, row *models.TenantBackup,
) (int64, []TableRowCount, error) {
    var buf bytes.Buffer
    zw := zip.NewWriter(&buf)
 
    // README first — plain-text overview so the tenant knows
    // what they're looking at without reading the manifest.
    readme, _ := zw.Create("README.txt")
    fmt.Fprintf(readme, "Business: %s\n", biz.Name)
    fmt.Fprintf(readme, "Generated: %s UTC\n\n",
        row.StartedAt.UTC().Format(time.RFC3339))
    fmt.Fprintf(readme, "Contents:\n")
    fmt.Fprintf(readme, "  csv/<table>.csv   One CSV per table\n")
    fmt.Fprintf(readme, "  dump.sql          Postgres INSERT statements\n")
    fmt.Fprintf(readme, "  metadata.json     Row counts + manifest\n")
 
    sqlBuf := &bytes.Buffer{}
    fmt.Fprintf(sqlBuf, "BEGIN;\n\n")
    counts := make([]TableRowCount, 0, len(backupTables))
 
    for _, table := range backupTables {
        csvBuf := &bytes.Buffer{}
        n, err := s.dumpTable(ctx, table, businessID, csvBuf, sqlBuf)
        if err != nil {
            // Missing table on this deployment isn't fatal.
            counts = append(counts, TableRowCount{
                Table: table, Rows: 0, Error: err.Error(),
            })
            continue
        }
        counts = append(counts, TableRowCount{Table: table, Rows: n})
 
        w, _ := zw.Create(fmt.Sprintf("csv/%s.csv", table))
        io.Copy(w, csvBuf)
    }
 
    fmt.Fprintf(sqlBuf, "COMMIT;\n")
    sqlFile, _ := zw.Create("dump.sql")
    io.Copy(sqlFile, sqlBuf)
 
    metaFile, _ := zw.Create("metadata.json")
    json.NewEncoder(metaFile).Encode(struct {
        BusinessID  string          `json:"business_id"`
        GeneratedAt time.Time       `json:"generated_at"`
        Tables      []TableRowCount `json:"tables"`
    }{biz.ID, row.StartedAt.UTC(), counts})
 
    if err := zw.Close(); err != nil {
        return 0, nil, err
    }
 
    key := fmt.Sprintf("tenant-backups/%s/%s-%s.zip",
        businessID,
        row.StartedAt.UTC().Format("2006-01-02"),
        row.ID[:8],
    )
    row.R2Key = key
    s.DB.Model(row).UpdateColumn("r2_key", key)
 
    if err := s.Storage.Upload(ctx, key,
        bytes.NewReader(buf.Bytes()), "application/zip"); err != nil {
        return 0, nil, fmt.Errorf("upload: %w", err)
    }
    return int64(buf.Len()), counts, nil
}

func (s *Storage) Upload(ctx context.Context, key string,
    r io.Reader, contentType string) error {
    _, err := s.client.PutObject(ctx, &s3.PutObjectInput{
        Bucket: &s.bucketName, Key: &key,
        Body: r, ContentType: &contentType,
    })
    return err
}
 
func (s *Storage) GetSignedURL(ctx context.Context,
    key string, ttl time.Duration) (string, error) {
    presigner := s3.NewPresignClient(s.client)
    req, err := presigner.PresignGetObject(ctx, &s3.GetObjectInput{
        Bucket: &s.bucketName, Key: &key,
    }, s3.WithPresignExpires(ttl))
    if err != nil {
        return "", err
    }
    return req.URL, nil
}

tenantWrite.GET("/backups", h.List)
tenantWrite.POST("/backups/generate", h.Generate)
tenantWrite.GET("/backups/:id/download", h.Download)

func (s *Service) Generate(ctx context.Context,
    businessID, userID, kind string) (*TenantBackup, error) {
    if s.Storage == nil {
        return nil, ErrStorageUnavailable // → 503
    }
 
    // Rate-limit MANUAL only. Cron-driven WEEKLY_AUTO bypasses.
    if kind == "MANUAL" {
        var recent int64
        since := time.Now().Add(-24 * time.Hour)
        s.DB.Model(&TenantBackup{}).
            Where("business_id = ? AND kind = ? AND started_at >= ? AND status IN ?",
                businessID, "MANUAL", since,
                []string{"READY", "RUNNING"}).
            Count(&recent)
        if recent > 0 {
            return nil, ErrRateLimit // → 429
        }
    }
 
    // Create RUNNING row upfront. On failure it stays visible
    // as FAILED instead of silently no-op'ing.
    row := &TenantBackup{
        BusinessID: businessID, Kind: kind, Status: "RUNNING",
    }
    if userID != "" {
        row.GeneratedByUserID = &userID
    }
    s.DB.Create(row)
 
    size, counts, err := s.produceAndUpload(ctx, businessID, biz, row)
    if err != nil {
        now := time.Now()
        row.Status, row.CompletedAt, row.Error = "FAILED", &now, err.Error()
        s.DB.Save(row)
        return row, err
    }
 
    now := time.Now()
    row.Status, row.CompletedAt, row.SizeBytes = "READY", &now, size
    countsJSON, _ := json.Marshal(counts)
    row.RowCounts = string(countsJSON)
    s.DB.Save(row)
 
    // Rolling cleanup after success. A failed cleanup does NOT
    // fail the backup — the operator already has their file.
    s.rollingCleanup(ctx, businessID)
    return row, nil
}

func (h *Handler) Download(c *gin.Context) {
    tc, _ := tenant.FromGin(c)
    businessID, _ := tc.MustBusinessID()
 
    var row models.TenantBackup
    if err := h.DB.
        Where("id = ? AND business_id = ?", c.Param("id"), businessID).
        First(&row).Error; err != nil {
        c.JSON(404, gin.H{"error": gin.H{"code": "NOT_FOUND"}})
        return
    }
    if row.Status != "READY" {
        c.JSON(409, gin.H{"error": gin.H{"code": "NOT_READY"}})
        return
    }
 
    url, _ := h.Storage.GetSignedURL(c.Request.Context(),
        row.R2Key, 15*time.Minute)
 
    c.JSON(200, gin.H{"data": gin.H{
        "url":                url,
        "filename":           defaultName(row),
        "expires_in_seconds": 15 * 60,
    }})
}

func (s *Service) rollingCleanup(ctx context.Context, businessID string) {
    const retain = 4 // Four most recent per tenant.
 
    var stale []models.TenantBackup
    s.DB.
        Where("business_id = ? AND status = ?", businessID, "READY").
        Order("started_at DESC").
        Offset(retain).
        Find(&stale)
 
    for _, b := range stale {
        if b.R2Key == "" {
            continue
        }
        if err := s.Storage.Delete(ctx, b.R2Key); err != nil {
            continue // Best-effort. Retry next tick.
        }
        s.DB.Model(&b).Updates(map[string]interface{}{
            "status": "PURGED",
            "r2_key": "",
        })
    }
}

_, err = scheduler.Register(
    "0 2 * * 0", // Sunday 02:00 UTC
    asynq.NewTask("backup:weekly", nil),
    asynq.Unique(20*time.Hour), // Guards against failover dupes.
)

func runWeeklyTenantBackup(ctx context.Context,
    db *gorm.DB, store *storage.Storage) {
    if store == nil {
        log.Println("R2 not configured, skipping")
        return
    }
 
    var tenants []models.Business
    db.Where("subscription_status = ? AND is_demo = ?",
        "ACTIVE", false).Find(&tenants)
 
    svc := services.NewTenantBackupService(db, store)
    ok, failed := 0, 0
    for _, biz := range tenants {
        // Timeout per tenant so a stuck upload can't hang the job.
        tenantCtx, cancel := context.WithTimeout(ctx, 5*time.Minute)
        row, err := svc.Generate(tenantCtx, biz.ID, "", "WEEKLY_AUTO")
        cancel()
        if err != nil || row.Status != "READY" {
            failed++
            continue
        }
        ok++
    }
    log.Printf("weekly backup: %d ok, %d failed", ok, failed)
}

var got bool
conn.QueryRowContext(ctx,
    "SELECT pg_try_advisory_lock($1)", cronLockID,
).Scan(&got)
if !got {
    log.Println("Another instance is leader; standing by")
    return nil
}
// This replica owns the lock — run the scheduler.
go func() { s.scheduler.Run() }()

export function useTenantBackups() {
  return useQuery<TenantBackup[]>({
    queryKey: ["tenant-backups"],
    queryFn: async () => (await apiClient.get("/backups")).data.data,
    // Poll every 3s while any row is RUNNING. Otherwise idle.
    refetchInterval: (query) => {
      const rows = query.state.data as TenantBackup[] | undefined;
      return rows?.some((r) => r.status === "RUNNING") ? 3000 : false;
    },
    staleTime: 15_000,
  });
}

async function onDownload(backupID: string) {
  const { url } = await downloadURL.mutateAsync({ backupID });
  window.location.assign(url); // Browser handles the R2 download.
}

func (s *Service) dumpTableFullDB(ctx context.Context,
    table string, csvOut, sqlOut io.Writer) (int64, error) {
    query := fmt.Sprintf("SELECT * FROM %s", table) // No WHERE.
    // ... rest identical to dumpTable.
}