# Database Design

## Project
Advanced API-Based Payment Gateway System

## Purpose
Ye database clients, admin users, gateways, transactions, webhooks, commissions, settlements, refunds, aur support ko handle karega.

## Database Name
payment_gateway

---

## 1. users
Admin, staff, support users ke liye.

```sql
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    role ENUM('super_admin','admin','staff','support') NOT NULL DEFAULT 'staff',
    status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
    last_login_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## 2. clients
Business clients/merchants ke liye.

```sql
CREATE TABLE clients (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_name VARCHAR(255) NOT NULL,
    legal_name VARCHAR(255) DEFAULT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20) DEFAULT NULL,
    website VARCHAR(255) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    city VARCHAR(100) DEFAULT NULL,
    state VARCHAR(100) DEFAULT NULL,
    country VARCHAR(100) DEFAULT 'India',
    pincode VARCHAR(10) DEFAULT NULL,
    api_key VARCHAR(255) NOT NULL UNIQUE,
    secret_key VARCHAR(255) NOT NULL UNIQUE,
    webhook_url VARCHAR(255) DEFAULT NULL,
    webhook_secret VARCHAR(255) DEFAULT NULL,
    commission_rate DECIMAL(5,2) NOT NULL DEFAULT 2.00,
    settlement_cycle ENUM('daily','weekly','monthly') NOT NULL DEFAULT 'daily',
    monthly_limit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status ENUM('pending','active','suspended','rejected') NOT NULL DEFAULT 'pending',
    kyc_status ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
    created_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_clients_status (status),
    INDEX idx_clients_email (email)
);
```

---

## 3. gateways
External payment gateways ke liye.

```sql
CREATE TABLE gateways (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    provider_type ENUM('chinese','international','local') NOT NULL DEFAULT 'local',
    api_endpoint VARCHAR(255) NOT NULL,
    api_key VARCHAR(255) DEFAULT NULL,
    api_secret VARCHAR(255) DEFAULT NULL,
    merchant_id VARCHAR(255) DEFAULT NULL,
    webhook_url VARCHAR(255) DEFAULT NULL,
    status ENUM('active','inactive','maintenance') NOT NULL DEFAULT 'inactive',
    priority INT NOT NULL DEFAULT 1,
    settings JSON DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## 4. transactions
Main payment records.

```sql
CREATE TABLE transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_uuid VARCHAR(100) NOT NULL UNIQUE,
    client_id BIGINT UNSIGNED NOT NULL,
    gateway_id BIGINT UNSIGNED DEFAULT NULL,
    order_id VARCHAR(100) NOT NULL,
    payment_ref VARCHAR(100) DEFAULT NULL,
    customer_name VARCHAR(255) DEFAULT NULL,
    customer_email VARCHAR(255) DEFAULT NULL,
    customer_phone VARCHAR(20) DEFAULT NULL,
    amount DECIMAL(15,2) NOT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'INR',
    commission_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    net_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status ENUM('pending','processing','success','failed','cancelled','refunded','disputed') NOT NULL DEFAULT 'pending',
    payment_method VARCHAR(50) DEFAULT NULL,
    gateway_response JSON DEFAULT NULL,
    request_payload JSON DEFAULT NULL,
    response_payload JSON DEFAULT NULL,
    callback_status ENUM('pending','received','failed') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tx_client_id (client_id),
    INDEX idx_tx_order_id (order_id),
    INDEX idx_tx_status (status)
);
```

---

## 5. transaction_logs
Transaction ka full audit trail.

```sql
CREATE TABLE transaction_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id BIGINT UNSIGNED NOT NULL,
    action VARCHAR(100) NOT NULL,
    message TEXT DEFAULT NULL,
    request_data JSON DEFAULT NULL,
    response_data JSON DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
```

---

## 6. api_keys
Multiple API keys support.

```sql
CREATE TABLE api_keys (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    api_key VARCHAR(255) NOT NULL UNIQUE,
    secret_key VARCHAR(255) NOT NULL UNIQUE,
    environment ENUM('sandbox','live') NOT NULL DEFAULT 'sandbox',
    last_used_at DATETIME DEFAULT NULL,
    status ENUM('active','revoked') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## 7. webhooks
Webhook deliveries track karne ke liye.

```sql
CREATE TABLE webhooks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id BIGINT UNSIGNED NOT NULL,
    transaction_id BIGINT UNSIGNED DEFAULT NULL,
    event_type VARCHAR(100) NOT NULL,
    endpoint_url VARCHAR(255) NOT NULL,
    request_headers JSON DEFAULT NULL,
    request_body JSON DEFAULT NULL,
    response_code INT DEFAULT NULL,
    response_body JSON DEFAULT NULL,
    delivery_status ENUM('pending','success','failed','retrying') NOT NULL DEFAULT 'pending',
    retry_count INT NOT NULL DEFAULT 0,
    next_retry_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## 8. commission_rules
Commission configuration.

```sql
CREATE TABLE commission_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id BIGINT UNSIGNED DEFAULT NULL,
    gateway_id BIGINT UNSIGNED DEFAULT NULL,
    rule_name VARCHAR(150) NOT NULL,
    commission_type ENUM('flat','percentage') NOT NULL DEFAULT 'percentage',
    commission_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    min_amount DECIMAL(15,2) DEFAULT NULL,
    max_amount DECIMAL(15,2) DEFAULT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## 9. settlements
Client settlement records.

```sql
CREATE TABLE settlements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id BIGINT UNSIGNED NOT NULL,
    settlement_date DATE NOT NULL,
    gross_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    commission_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    net_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status ENUM('pending','processed','paid','failed') NOT NULL DEFAULT 'pending',
    reference_no VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## 10. refunds
Refund records.

```sql
CREATE TABLE refunds (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id BIGINT UNSIGNED NOT NULL,
    refund_uuid VARCHAR(100) NOT NULL UNIQUE,
    amount DECIMAL(15,2) NOT NULL,
    reason TEXT DEFAULT NULL,
    status ENUM('pending','approved','rejected','processed','failed') NOT NULL DEFAULT 'pending',
    gateway_refund_id VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## 11. support_tickets
Client support ke liye.

```sql
CREATE TABLE support_tickets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id BIGINT UNSIGNED DEFAULT NULL,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
    status ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
    assigned_to BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## 12. audit_logs
Admin actions ke liye.

```sql
CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED DEFAULT NULL,
    action VARCHAR(150) NOT NULL,
    module_name VARCHAR(100) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    ip_address VARCHAR(100) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    metadata JSON DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
```

---

## 13. notifications
System notifications.

```sql
CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id BIGINT UNSIGNED DEFAULT NULL,
    user_id BIGINT UNSIGNED DEFAULT NULL,
    type VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
```

---

## 14. settings
Global settings.

```sql
CREATE TABLE settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(150) NOT NULL UNIQUE,
    setting_value TEXT DEFAULT NULL,
    setting_type ENUM('text','number','boolean','json') NOT NULL DEFAULT 'text',
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## 15. payment_methods
Supported payment methods.

```sql
CREATE TABLE payment_methods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## Relationships

- clients.id → transactions.client_id
- gateways.id → transactions.gateway_id
- transactions.id → transaction_logs.transaction_id
- clients.id → api_keys.client_id
- clients.id → webhooks.client_id
- transactions.id → webhooks.transaction_id
- transactions.id → refunds.transaction_id
- clients.id → settlements.client_id
- users.id → audit_logs.user_id

---

## Indexing Suggestions

- clients.email
- clients.status
- transactions.order_id
- transactions.status
- transactions.client_id
- webhooks.delivery_status
- support_tickets.status

---

## Important Notes

- Card data should not be stored directly.
- API secrets must be stored encrypted.
- Webhook requests should be signed.
- Transaction logs should keep request/response history.
- All important tables should use UTC timestamps.