# Web Admin - Laporan Penghasilan Shopee & TikTok Shop

## 1. Overview Sistem

Web admin berbasis PHP (Laravel) untuk menghitung penghasilan toko online dengan fitur:
- **Multi-platform**: Shopee + TikTok Shop
- **Impor Laporan XLS**: Upload laporan penjualan per bulan
- **Dashboard**: Ringkasan penghasilan (laba kotor, jumlah order, produk terlaris)
- **Kelola Produk**: CRUD produk dengan HPP (Harga Pokok Penjualan)
- **Analisis Profit**: Perhitungan otomatis laba kotor per produk/order

## 2. Tech Stack

| Component | Technology |
|-----------|------------|
| Backend | PHP 8.x + Laravel 11 |
| Database | MySQL 8.x (existing product DB + new tables) |
| Frontend | Blade + TailwindCSS |
| Excel Processing | PhpSpreadsheet |
| Deployment | VPS (Apache/Nginx) |

## 3. Arsitektur Database

### 3.1 Tabel Baru yang Dibuat

```sql
-- Laporan penjualan dari Shopee/TikTok
CREATE TABLE shopee_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(100) UNIQUE NOT NULL,
    platform ENUM('shopee', 'tiktok') NOT NULL,
    order_date DATE NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    sku VARCHAR(100),
    quantity INT NOT NULL,
    selling_price DECIMAL(12,2) NOT NULL,
    shipping_fee DECIMAL(10,2) DEFAULT 0,
    transaction_fee DECIMAL(10,2) DEFAULT 0,
    total_revenue DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_platform (platform),
    INDEX idx_order_date (order_date)
);

-- Ringkasan bulanan per platform
CREATE TABLE monthly_summary (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    platform ENUM('shopee', 'tiktok') NOT NULL,
    year_month VARCHAR(7) NOT NULL, -- format: YYYY-MM
    total_revenue DECIMAL(14,2) DEFAULT 0,
    total_cost DECIMAL(14,2) DEFAULT 0,
    total_profit DECIMAL(14,2) DEFAULT 0,
    total_orders INT DEFAULT 0,
    total_products_sold INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_platform_month (platform, year_month)
);

-- Relasi produk (linking SKU dari Shopee ke produk internal)
CREATE TABLE product_mappings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    platform ENUM('shopee', 'tiktok') NOT NULL,
    platform_sku VARCHAR(100) NOT NULL,
    internal_product_id BIGINT UNSIGNED, -- FK ke tabel produk existing
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_platform_sku (platform, platform_sku),
    FOREIGN KEY (internal_product_id) REFERENCES products(id) ON DELETE SET NULL
);

-- Upload history
CREATE TABLE import_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    platform ENUM('shopee', 'tiktok') NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    year_month VARCHAR(7) NOT NULL,
    total_rows INT DEFAULT 0,
    imported_rows INT DEFAULT 0,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

### 3.2 Catatan Koneksi Database

- **Database existing** digunakan untuk tabel `products` (yang sudah ada HPP-nya)
- **Tabel baru** dibuat dalam **database yang sama** atau database terpisah (opsional)
- Asumsi kolom `products`: `id`, `name`, `sku`, `purchase_price` (HPP), `selling_price`, dll

## 4. Struktur Halaman Admin

```
/admin
├── /dashboard
│   └── Halaman utama dengan ringkasan:
│       - Total revenue, cost, profit (filter by month/year/platform)
│       - Chart garis tren keuntungan
│       - Top 10 produk terlaris
│       - Recent orders table
│
├── /import
│   └── Halaman impor XLS:
│       - Upload form (drag & drop)
│       - Pilih platform (Shopee/TikTok)
│       - Preview data sebelum import
│       - Import history table
│
├── /products
│   └── CRUD produk:
│       - List produk dengan search/filter
│       - Create/Edit/Delete produk
│       - Bulk import dari Excel
│       - Product mapping (SKU Shopee → internal product)
│
├── /orders
│   └── Daftar transaksi:
│       - Filter by platform, date range, product
│       - View order detail
│       - Profit calculation per order
│
└── /reports
    └── Laporan detail:
        - Summary per bulan
        - Breakdown per produk
        - Export ke Excel/PDF
```

## 5. Alur Kerja Impor XLS

```
┌─────────────┐     ┌──────────────┐     ┌─────────────────┐
│  Upload XLS │────▶│ Parse Excel  │────▶│  Preview Data   │
└─────────────┘     └──────────────┘     └────────┬────────┘
                                                 │
                     ┌──────────────────────────┘
                     ▼
┌─────────────┐     ┌──────────────┐     ┌─────────────────┐
│ Match SKU   │◀────│ Validate SKU │◀────│ Show Validation │
│ (auto/manual)     │ & Pricing    │     │   Errors        │
└─────────────┘     └──────────────┘     └─────────────────┘
         │
         ▼
┌─────────────┐     ┌──────────────┐     ┌─────────────────┐
│   Insert    │────▶│ Update       │────▶│   Complete!     │
│   to DB     │     │ monthly_sum  │     │   Show summary  │
└─────────────┘     └──────────────┘     └─────────────────┘
```

## 6. Perhitungan Profit

```
Revenue = Selling Price × Quantity
Cost (HPP) = purchase_price × Quantity
Shipping Fee = dari laporan (bearer cost)
Transaction Fee = dari laporan (platform fee)

Gross Profit = Revenue - Cost - Shipping Fee - Transaction Fee
Net Profit = Gross Profit (belum termasuk biaya operasional lain)
```

## 7. API Endpoints ( untuk AJAX )

| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | `/api/dashboard/summary` | Get dashboard summary data |
| GET | `/api/orders` | List orders with filters |
| GET | `/api/products` | List products |
| POST | `/api/import/preview` | Preview uploaded Excel |
| POST | `/api/import/execute` | Execute import |
| GET | `/api/reports/monthly` | Monthly report data |
| GET | `/api/products/mapping` | Get product mappings |
| POST | `/api/products/mapping` | Save product mapping |

## 8. Fitur Tambahan (Future)

- Notifikasi Telegram/Email saat profit below threshold
- Integrasi dengan laporan Tiktok Shop
- Multiple store support
- Export laporan ke PDF
- Kalkulator biaya operasional (promo, ads, dll)

## 9. Langkah Implementasi

1. Setup Laravel project + TailwindCSS
2. Buat database migration untuk tabel baru
3. Buat Model + Seeder
4. Buat Controller & Routes
5. Build halaman Admin (Blade views)
6. Implementasi fitur Impor Excel
7. Build Dashboard dengan Chart
8. Testing & Bug fixing
9. Deployment ke VPS