# Database Plan — Wavora / WAGW SaaS

## Rules

- MySQL 8.
- ID: `CHAR(36)` UUID.
- `m_` prefix for master tables.
- `t_` prefix for transaction tables.
- All queries use prepared statements.
- Every tenant-scoped query must filter `tenant_id`.

## Core Tables

### Auth / BRAC

- `m_user`
- `m_group`
- `m_module`
- `m_module_action`
- `m_rule`
- `m_user_token`

### Tenant

- `m_tenant`
- `m_tenant_user`
- `m_tenant_setting`
- `m_tenant_billing_setting`
- `m_plan`
- `m_subscription`

### WA Gateway

- `m_api_key`
- `m_wa_device`
- `m_wa_gateway_node`
- `m_webhook_endpoint`
- `m_device_plan` (paket aktif per device/number)
- `t_wa_send_job` (job async send)

### Transaction

- `t_wa_queue`
- `t_wa_message`
- `t_wa_message_log`
- `t_wa_send_job`
- `t_webhook_event`
- `t_webhook_delivery`
- `t_usage_daily`
- `t_billing_invoice`
- `t_billing_payment`
- `t_billing_payment_proof`
- `t_billing_transaction`
- `t_audit_log`

## Migration Naming

```text
database/migrations/YYYY_MM_DD_HHmm_description.sql
```

Example:

```text
database/migrations/2026_05_17_1925_create_core_tables.sql
```

## Billing Tables Detail

### `m_plan`

- `id CHAR(36)`
- `code VARCHAR(50)`
- `name VARCHAR(100)`
- `price_monthly DECIMAL(14,2)`
- `price_yearly DECIMAL(14,2) NULL`
- `device_limit INT`
- `message_limit_monthly INT`
- `api_key_limit INT`
- `webhook_limit INT`
- `is_active TINYINT(1)`
- `sort_order INT`
- `created_at DATETIME`
- `updated_at DATETIME`

### `m_subscription`

- `id CHAR(36)`
- `tenant_id CHAR(36)`
- `plan_id CHAR(36)`
- `status ENUM('trial','active','past_due','suspended','cancelled','expired')`
- `billing_cycle ENUM('monthly','yearly')`
- `started_at DATETIME`
- `current_period_start DATE`
- `current_period_end DATE`
- `cancelled_at DATETIME NULL`
- `created_at DATETIME`
- `updated_at DATETIME`

### `m_tenant_billing_setting`

- `id CHAR(36)`
- `tenant_id CHAR(36)`
- `invoice_due_days INT`
- `service_fee_type ENUM('flat','percent')`
- `service_fee_value DECIMAL(14,2)`
- `admin_fee_type ENUM('flat','percent')`
- `admin_fee_value DECIMAL(14,2)`
- `doku_enabled TINYINT(1)`
- `qris_manual_enabled TINYINT(1)`
- `qris_static_payload TEXT NULL`
- `bank_transfer_enabled TINYINT(1)`
- `bank_name VARCHAR(100) NULL`
- `bank_account_number VARCHAR(50) NULL`
- `bank_account_name VARCHAR(100) NULL`
- `payment_instruction TEXT NULL`
- `created_at DATETIME`
- `updated_at DATETIME`

### `t_billing_invoice`

- `id CHAR(36)`
- `tenant_id CHAR(36)`
- `plan_id CHAR(36)`
- `subscription_id CHAR(36) NULL`
- `invoice_no VARCHAR(64)`
- `period_start DATE`
- `period_end DATE`
- `subtotal DECIMAL(14,2)`
- `service_fee DECIMAL(14,2)`
- `admin_fee DECIMAL(14,2)`
- `grand_total DECIMAL(14,2)`
- `status ENUM('draft','issued','pending_payment','paid','expired','cancelled')`
- `due_at DATETIME`
- `paid_at DATETIME NULL`
- `created_at DATETIME`
- `updated_at DATETIME`

### `t_billing_payment`

- `id CHAR(36)`
- `tenant_id CHAR(36)`
- `invoice_id CHAR(36)`
- `method ENUM('doku_hosted_checkout','qris_manual','bank_transfer_manual')`
- `channel_code VARCHAR(50) NULL`
- `amount DECIMAL(14,2)`
- `external_ref VARCHAR(191) NULL`
- `checkout_url TEXT NULL`
- `status ENUM('pending','waiting_verification','verified','rejected','expired')`
- `paid_at DATETIME NULL`
- `verified_at DATETIME NULL`
- `verified_by CHAR(36) NULL`
- `reject_reason TEXT NULL`
- `created_at DATETIME`
- `updated_at DATETIME`

### `t_billing_payment_proof`

- `id CHAR(36)`
- `payment_id CHAR(36)`
- `file_path VARCHAR(255)`
- `original_name VARCHAR(191)`
- `mime_type VARCHAR(100)`
- `file_size INT`
- `payer_name VARCHAR(100) NULL`
- `paid_amount DECIMAL(14,2)`
- `paid_at DATETIME NULL`
- `note TEXT NULL`
- `created_at DATETIME`

### `t_billing_transaction`

Raw payment event log for DOKU/manual audit.

- `id CHAR(36)`
- `tenant_id CHAR(36)`
- `payment_id CHAR(36) NULL`
- `provider VARCHAR(50)`
- `event_type VARCHAR(100)`
- `external_ref VARCHAR(191) NULL`
- `payload_json TEXT`
- `created_at DATETIME`

## Paket & Device Plan Tables

### `m_plan`

Catatan: Paket **Free** wajib ada dengan konfigurasi:
- `code = 'free'`
- `price_monthly = 0`
- `device_limit = 1` (per device)
- `message_limit_daily = 300` (per device)
- `branding_footer = '\n\n— Sent via Wavora'`
- Paket Free **tidak bisa dihapus** atau dinonaktifkan.

Field lengkap:

- `id CHAR(36)`
- `code VARCHAR(50)`
- `name VARCHAR(100)`
- `price_monthly DECIMAL(14,2)`
- `price_yearly DECIMAL(14,2) NULL`
- `device_limit INT` (jumlah device maksimal — untuk Free: 1)
- `message_limit_monthly INT` (kuota bulanan per tenant — untuk Free: 0/unlimited)
- `message_limit_daily INT` (kuota harian per device — untuk Free: 300)
- `api_key_limit INT`
- `webhook_limit INT`
- `is_branding_required TINYINT(1)` (jika 1 → footer wajib di-append otomatis)
- `branding_footer TEXT NULL` (template footer, contoh: '\n\n— Sent via Wavora')
- `is_active TINYINT(1)`
- `sort_order INT`
- `created_at DATETIME`
- `updated_at DATETIME`

### `m_device_plan`

Paket aktif per WhatsApp number/device (per device, bukan global tenant).

- `id CHAR(36)`
- `device_id CHAR(36)` → FK `m_wa_device.id`
- `plan_id CHAR(36)` → FK `m_plan.id`
- `status ENUM('active','inactive','expired','suspended')`
- `started_at DATETIME`
- `expires_at DATETIME NULL`
- `daily_message_count INT DEFAULT 0`
- `daily_reset_at DATE` (tanggal reset counter harian)
- `created_at DATETIME`
- `updated_at DATETIME`

Catatan:
- Setiap `m_wa_device` WAJIB punya tepat satu `m_device_plan` aktif.
- Saat device baru dibuat → otomatis buat `m_device_plan` dengan `plan_id = Free`.
- Upgrade/downgrade plan per device meng-update record ini.

## Job Queue Tables

### `t_wa_send_job`

Job async untuk kirim pesan (menggantikan sync call langsung ke GOWA).

- `id CHAR(36)`
- `tenant_id CHAR(36)`
- `device_id CHAR(36)` → FK `m_wa_device.id`
- `message_type VARCHAR(30)` (text/image/file/video/audio/etc)
- `recipient VARCHAR(50)` (nomor WA)
- `payload_json JSON` (payload asli yang akan dikirim ke GOWA)
- `original_payload_json JSON` (payload sebelum branding footer di-append — untuk free plan)
- `status ENUM('pending','processing','sent','failed','cancelled')`
- `gowa_message_id VARCHAR(191) NULL` (message_id dari GOWA jika berhasil)
- `attempt_count INT DEFAULT 0`
- `max_attempts INT DEFAULT 3`
- `next_attempt_at DATETIME NULL`
- `last_error TEXT NULL`
- `is_async TINYINT(1) DEFAULT 1` (1=async queue, 0=sync/live)
- `priority INT DEFAULT 0`
- `scheduled_at DATETIME NULL`
- `processed_at DATETIME NULL`
- `created_at DATETIME`
- `updated_at DATETIME`

Catatan:
- API send → insert job dengan `status=pending`, return `job_id` + `202 Accepted`.
- Worker cron mengambil job `pending`, proses, update status.
- `is_async=0` hanya untuk mode sync/admin/dev fallback.