# Esquema do Banco de Dados

O STA Connector usa SQLite para desenvolvimento/instancia unica ou PostgreSQL para implantacoes de producao/multiplas instancias.

## Visao Geral do Esquema

```
+---------------------------------------------------------------------+
|                         Esquema do Banco de Dados                    |
+----------------------------------------------------------------------+
|                                                                      |
|  +-------------------------+      +-------------------------+        |
|  |  outbound_files         |      |  processed_protocols    |        |
|  +-------------------------+      +-------------------------+        |
|  | id (PK)                 |      | protocol (PK)           |        |
|  | protocol                |      | system                  |        |
|  | file_name               |      | file_type               |        |
|  | file_type               |      | processed_at            |        |
|  | file_size               |      +-------------------------+        |
|  | checksum                |                                         |
|  | source_system           |      +-------------------------+        |
|  | status                  |      |  retry_queue            |        |
|  | sta_state (JSON)        |      +-------------------------+        |
|  | retry_count             |      | id (PK)                 |        |
|  | last_error              |      | item_type               |        |
|  | created_at              |      | reference_id            |        |
|  | updated_at              |      | payload                 |        |
|  | completed_at            |      | retry_count             |        |
|  +-------------------------+      | max_retries             |        |
|                                   | next_retry_at           |        |
|  +-------------------------+      | last_error              |        |
|  |  config_overrides       |      | created_at              |        |
|  +-------------------------+      | updated_at              |        |
|  | key (PK)                |      +-------------------------+        |
|  | value                   |                                         |
|  | updated_at              |                                         |
|  +-------------------------+                                         |
|                                                                      |
+----------------------------------------------------------------------+
```

## Tabelas

### outbound_files

Rastreia arquivos enviados para upload ao BCB STA.

```sql
CREATE TABLE outbound_files (
    id TEXT PRIMARY KEY,
    protocol TEXT NOT NULL,
    file_name TEXT NOT NULL,
    file_type TEXT NOT NULL,
    file_size INTEGER NOT NULL,
    checksum TEXT NOT NULL,
    source_system TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    sta_state TEXT,  -- JSON
    retry_count INTEGER DEFAULT 0,
    last_error TEXT,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    completed_at DATETIME
);

CREATE INDEX idx_outbound_files_protocol ON outbound_files(protocol);
CREATE INDEX idx_outbound_files_status ON outbound_files(status);
CREATE INDEX idx_outbound_files_source_system ON outbound_files(source_system);
CREATE INDEX idx_outbound_files_created_at ON outbound_files(created_at);
```

#### Colunas

| Coluna | Tipo | Descricao |
|--------|------|-----------|
| `id` | TEXT | Chave primaria UUID |
| `protocol` | TEXT | Numero de protocolo BCB |
| `file_name` | TEXT | Nome do arquivo enviado |
| `file_type` | TEXT | Codigo do tipo de arquivo BCB |
| `file_size` | INTEGER | Tamanho do arquivo em bytes |
| `checksum` | TEXT | Checksum SHA-256 |
| `source_system` | TEXT | Codigo do sistema BCB |
| `status` | TEXT | Status de processamento |
| `sta_state` | TEXT | Estado STA codificado em JSON |
| `retry_count` | INTEGER | Numero de tentativas de retry |
| `last_error` | TEXT | Ultima mensagem de erro |
| `created_at` | DATETIME | Hora de criacao do registro |
| `updated_at` | DATETIME | Hora da ultima atualizacao |
| `completed_at` | DATETIME | Hora de conclusao |

#### Valores de Status

| Status | Descricao |
|--------|-----------|
| `pending` | Enfileirado para processamento |
| `uploading` | Upload em progresso |
| `uploaded` | Enviado ao BCB |
| `processing` | BCB esta processando |
| `completed` | Processado com sucesso |
| `failed` | Processamento falhou |
| `retrying` | Agendado para retry |

### processed_protocols

Rastreia numeros de protocolo BCB que foram processados (previne duplicatas).

```sql
CREATE TABLE processed_protocols (
    protocol TEXT PRIMARY KEY,
    system TEXT NOT NULL,
    file_type TEXT,
    processed_at DATETIME NOT NULL
);

CREATE INDEX idx_processed_protocols_system ON processed_protocols(system);
CREATE INDEX idx_processed_protocols_processed_at ON processed_protocols(processed_at);
```

#### Colunas

| Coluna | Tipo | Descricao |
|--------|------|-----------|
| `protocol` | TEXT | Numero de protocolo BCB (PK) |
| `system` | TEXT | Codigo do sistema BCB |
| `file_type` | TEXT | Tipo de arquivo BCB |
| `processed_at` | DATETIME | Timestamp do processamento |

### retry_queue

Gerencia tentativas de retry para operacoes com falha.

```sql
CREATE TABLE retry_queue (
    id TEXT PRIMARY KEY,
    item_type TEXT NOT NULL,
    reference_id TEXT NOT NULL,
    payload TEXT NOT NULL,  -- JSON
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    next_retry_at DATETIME NOT NULL,
    last_error TEXT,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
);

CREATE INDEX idx_retry_queue_next_retry_at ON retry_queue(next_retry_at);
CREATE INDEX idx_retry_queue_item_type ON retry_queue(item_type);
CREATE INDEX idx_retry_queue_reference_id ON retry_queue(reference_id);
```

#### Colunas

| Coluna | Tipo | Descricao |
|--------|------|-----------|
| `id` | TEXT | Chave primaria UUID |
| `item_type` | TEXT | Tipo de retry (upload, callback, webhook) |
| `reference_id` | TEXT | Referencia ao item original |
| `payload` | TEXT | Dados de retry codificados em JSON |
| `retry_count` | INTEGER | Tentativa atual de retry |
| `max_retries` | INTEGER | Maximo de tentativas de retry |
| `next_retry_at` | DATETIME | Hora agendada para retry |
| `last_error` | TEXT | Erro da ultima tentativa |
| `created_at` | DATETIME | Hora de criacao do registro |
| `updated_at` | DATETIME | Hora da ultima atualizacao |

#### Tipos de Item

| Tipo | Descricao |
|------|-----------|
| `upload` | Upload de arquivo para o STA |
| `callback` | Entrega de callback |
| `webhook` | Notificacao de webhook |

### config_overrides

Armazena sobrescricoes de configuracao em tempo de execucao.

```sql
CREATE TABLE config_overrides (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at DATETIME NOT NULL
);
```

#### Colunas

| Coluna | Tipo | Descricao |
|--------|------|-----------|
| `key` | TEXT | Chave de configuracao |
| `value` | TEXT | Valor de configuracao |
| `updated_at` | DATETIME | Hora da ultima atualizacao |

## Modelos de Dados Go

### OutboundFile

```go
// internal/store/models.go
type OutboundFile struct {
    ID            string     `json:"id"`
    Protocol      string     `json:"protocol"`
    FileName      string     `json:"file_name"`
    FileType      string     `json:"file_type"`
    FileSize      int64      `json:"file_size"`
    Checksum      string     `json:"checksum"`
    SourceSystem  string     `json:"source_system"`
    Status        string     `json:"status"`
    STAState      *STAState  `json:"sta_state,omitempty"`
    RetryCount    int        `json:"retry_count"`
    LastError     string     `json:"last_error,omitempty"`
    CreatedAt     time.Time  `json:"created_at"`
    UpdatedAt     time.Time  `json:"updated_at"`
    CompletedAt   *time.Time `json:"completed_at,omitempty"`
}

type STAState struct {
    TransferID     string     `json:"transfer_id,omitempty"`
    ExternalID     string     `json:"external_id,omitempty"`
    STAStatus      string     `json:"sta_status,omitempty"`
    STAMessage     string     `json:"sta_message,omitempty"`
    STATimestamp   *time.Time `json:"sta_timestamp,omitempty"`
    AcknowledgedAt *time.Time `json:"acknowledged_at,omitempty"`
    DeliveredAt    *time.Time `json:"delivered_at,omitempty"`
}

// Constantes de Status
const (
    StatusPending    = "pending"
    StatusUploading  = "uploading"
    StatusUploaded   = "uploaded"
    StatusProcessing = "processing"
    StatusCompleted  = "completed"
    StatusFailed     = "failed"
    StatusRetrying   = "retrying"
)
```

### ProcessedProtocol

```go
type ProcessedProtocol struct {
    Protocol    string    `json:"protocol"`
    System      string    `json:"system"`
    FileType    string    `json:"file_type"`
    ProcessedAt time.Time `json:"processed_at"`
}
```

### RetryItem

```go
type RetryItem struct {
    ID           string    `json:"id"`
    ItemType     string    `json:"item_type"`
    ReferenceID  string    `json:"reference_id"`
    Payload      string    `json:"payload"`
    RetryCount   int       `json:"retry_count"`
    MaxRetries   int       `json:"max_retries"`
    NextRetryAt  time.Time `json:"next_retry_at"`
    LastError    string    `json:"last_error,omitempty"`
    CreatedAt    time.Time `json:"created_at"`
    UpdatedAt    time.Time `json:"updated_at"`
}

const (
    RetryTypeUpload   = "upload"
    RetryTypeCallback = "callback"
    RetryTypeWebhook  = "webhook"
)
```

## Interface Store

```go
// internal/store/store.go
type Store interface {
    // Arquivos de saida
    SaveOutboundFile(file *OutboundFile) error
    GetOutboundFile(id string) (*OutboundFile, error)
    UpdateOutboundFileStatus(id, status string, staState *STAState) error
    ListOutboundFiles(status string, limit, offset int) ([]*OutboundFile, error)

    // Protocolos processados (entrada)
    IsProcessed(protocol string) bool
    MarkProcessed(protocol, system string) error
    GetProcessedCount(system string) (int, error)

    // Fila de retry
    AddToRetryQueue(item *RetryItem) error
    GetNextRetryItems(limit int) ([]*RetryItem, error)
    UpdateRetryItem(id string, retryCount int, nextRetryAt time.Time, lastError string) error
    RemoveFromRetryQueue(id string) error

    // Sobrescricoes de config
    GetConfigOverride(key string) (string, error)
    SetConfigOverride(key, value string) error
    DeleteConfigOverride(key string) error

    // Ciclo de vida
    Close() error
}

// Erros
var (
    ErrNotFound = errors.New("registro nao encontrado")
)
```

## Implementacao SQLite

```go
// internal/store/sqlite.go
type SQLiteStore struct {
    db     *sql.DB
    logger *slog.Logger
}

func NewSQLiteStore(path string) (*SQLiteStore, error) {
    db, err := sql.Open("sqlite3", path+"?_journal_mode=WAL&_busy_timeout=5000")
    if err != nil {
        return nil, fmt.Errorf("abrir banco de dados: %w", err)
    }

    store := &SQLiteStore{
        db:     db,
        logger: slog.Default(),
    }

    if err := store.migrate(); err != nil {
        return nil, fmt.Errorf("executar migracoes: %w", err)
    }

    return store, nil
}

func (s *SQLiteStore) SaveOutboundFile(file *OutboundFile) error {
    staStateJSON, _ := json.Marshal(file.STAState)

    _, err := s.db.Exec(`
        INSERT INTO outbound_files (
            id, protocol, file_name, file_type, file_size, checksum,
            source_system, status, sta_state, retry_count, last_error,
            created_at, updated_at, completed_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET
            status = excluded.status,
            sta_state = excluded.sta_state,
            retry_count = excluded.retry_count,
            last_error = excluded.last_error,
            updated_at = excluded.updated_at,
            completed_at = excluded.completed_at
    `,
        file.ID, file.Protocol, file.FileName, file.FileType,
        file.FileSize, file.Checksum, file.SourceSystem, file.Status,
        string(staStateJSON), file.RetryCount, file.LastError,
        file.CreatedAt, file.UpdatedAt, file.CompletedAt,
    )

    return err
}

func (s *SQLiteStore) GetOutboundFile(id string) (*OutboundFile, error) {
    row := s.db.QueryRow(`
        SELECT id, protocol, file_name, file_type, file_size, checksum,
               source_system, status, sta_state, retry_count, last_error,
               created_at, updated_at, completed_at
        FROM outbound_files
        WHERE id = ?
    `, id)

    file := &OutboundFile{}
    var staStateJSON sql.NullString
    var completedAt sql.NullTime

    err := row.Scan(
        &file.ID, &file.Protocol, &file.FileName, &file.FileType,
        &file.FileSize, &file.Checksum, &file.SourceSystem, &file.Status,
        &staStateJSON, &file.RetryCount, &file.LastError,
        &file.CreatedAt, &file.UpdatedAt, &completedAt,
    )

    if err == sql.ErrNoRows {
        return nil, ErrNotFound
    }
    if err != nil {
        return nil, err
    }

    if staStateJSON.Valid {
        json.Unmarshal([]byte(staStateJSON.String), &file.STAState)
    }
    if completedAt.Valid {
        file.CompletedAt = &completedAt.Time
    }

    return file, nil
}

func (s *SQLiteStore) IsProcessed(protocol string) bool {
    var count int
    err := s.db.QueryRow(
        "SELECT COUNT(*) FROM processed_protocols WHERE protocol = ?",
        protocol,
    ).Scan(&count)

    return err == nil && count > 0
}

func (s *SQLiteStore) MarkProcessed(protocol, system string) error {
    _, err := s.db.Exec(`
        INSERT OR IGNORE INTO processed_protocols (protocol, system, processed_at)
        VALUES (?, ?, ?)
    `, protocol, system, time.Now())

    return err
}
```

## Configuracao PostgreSQL

Para producao com multiplas instancias, use PostgreSQL:

```yaml
# config.yaml
state_store:
  type: "postgres"
  postgres:
    host: "postgres"
    port: 5432
    database: "sta_connector"
    username: "connector"
    password: "${DB_PASSWORD}"
    ssl_mode: "require"
    max_connections: 25
    max_idle_connections: 5
```

PostgreSQL oferece:

- **Pool de conexoes** para multiplas instancias
- **Bloqueio a nivel de linha** para acesso concorrente
- **Melhor desempenho** sob alta carga
- **Suporte a replicacao** para alta disponibilidade

## Padroes de Query

### Queries Comuns

```go
// Listar arquivos pendentes
func (s *SQLiteStore) ListPendingFiles(limit int) ([]*OutboundFile, error) {
    return s.ListOutboundFiles(StatusPending, limit, 0)
}

// Contar arquivos por status
func (s *SQLiteStore) CountByStatus() (map[string]int, error) {
    rows, err := s.db.Query(`
        SELECT status, COUNT(*) FROM outbound_files GROUP BY status
    `)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    counts := make(map[string]int)
    for rows.Next() {
        var status string
        var count int
        if err := rows.Scan(&status, &count); err != nil {
            return nil, err
        }
        counts[status] = count
    }

    return counts, nil
}

// Obter arquivos para retry
func (s *SQLiteStore) GetFilesForRetry(limit int) ([]*OutboundFile, error) {
    rows, err := s.db.Query(`
        SELECT id, protocol, file_name, file_type, file_size, checksum,
               source_system, status, sta_state, retry_count, last_error,
               created_at, updated_at, completed_at
        FROM outbound_files
        WHERE status = ? AND retry_count < 3
        ORDER BY updated_at ASC
        LIMIT ?
    `, StatusFailed, limit)

    // ... escanear linhas
}
```

### Queries de Metricas

```go
// Obter estatisticas de processamento
func (s *SQLiteStore) GetStats(since time.Time) (*Stats, error) {
    row := s.db.QueryRow(`
        SELECT
            COUNT(*) FILTER (WHERE status = 'completed') as completed,
            COUNT(*) FILTER (WHERE status = 'failed') as failed,
            COUNT(*) FILTER (WHERE status = 'pending') as pending,
            COUNT(*) FILTER (WHERE status IN ('uploading', 'uploaded', 'processing')) as processing
        FROM outbound_files
        WHERE created_at >= ?
    `, since)

    stats := &Stats{}
    err := row.Scan(&stats.Completed, &stats.Failed, &stats.Pending, &stats.Processing)
    return stats, err
}
```

## Boas Praticas

### 1. Use Prepared Statements

```go
stmt, err := db.Prepare("SELECT * FROM outbound_files WHERE id = ?")
defer stmt.Close()
```

### 2. Gerenciamento de Transacoes

```go
tx, err := db.Begin()
if err != nil {
    return err
}
defer tx.Rollback()

// Executar operacoes...

return tx.Commit()
```

### 3. Configuracoes de Pool de Conexoes

```go
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(5 * time.Minute)
```

### 4. Indexar Colunas Importantes

```sql
CREATE INDEX idx_outbound_files_status ON outbound_files(status);
CREATE INDEX idx_outbound_files_created_at ON outbound_files(created_at);
```

### 5. Usar Modo WAL para SQLite

```go
db, err := sql.Open("sqlite3", "state.db?_journal_mode=WAL")
```

## Proximos Passos

- [Design de Concorrencia](/pt/architecture/concurrency) - Padroes de concorrencia Go
- [Visao Geral da Arquitetura](/pt/architecture/) - Componentes do sistema
- [Referencia da API](/pt/api/) - Documentacao da API REST