# MOMO Pro System - 資料庫結構

> 最後更新：2026-01-29

---

## 📊 ERD 關聯圖

```mermaid
erDiagram
    %% 核心商品模組
    categories ||--o{ products : has
    products ||--o{ price_records : has

    %% 用戶模組
    users ||--o{ login_history : has
    users ||--o{ user_permissions : has
    roles ||--o{ role_permissions : has
    permissions ||--o{ role_permissions : has
    permissions ||--o{ user_permissions : has

    %% 廠商模組
    vendor_list ||--o{ vendor_emails : has
    vendor_list ||--o{ email_send_log : has
    vendor_stockout ||--o{ email_send_log : references

    %% AI 模組
    users ||--o{ ai_generation_history : creates
    users ||--o{ ai_usage_tracking : creates
    users ||--o{ ai_prompt_templates : creates

    %% 匯入模組
    import_jobs }o--|| import_config : uses

    %% 通知模組
    notification_templates ||--o{ notification_logs : uses

    %% 核心表定義
    categories {
        int id PK
        string name UK
    }

    products {
        int id PK
        string i_code UK
        string name
        string url
        text image_url
        string category
        string status
        int category_id FK
        datetime created_at
        datetime updated_at
    }

    price_records {
        int id PK
        int product_id FK
        float price
        datetime timestamp
    }

    %% 用戶表
    users {
        int id PK
        string username UK
        string email UK
        string password_hash
        string role
        string display_name
        bool is_active
        datetime password_changed_at
        datetime created_at
        datetime updated_at
    }

    login_history {
        int id PK
        int user_id FK
        string username_attempted
        datetime login_time
        string ip_address
        string user_agent
        string status
        string failure_reason
    }

    %% 廠商表
    vendor_stockout {
        int id PK
        string batch_id
        date import_date
        string department
        string section
        string pm_name
        string product_code
        string product_name
        string vendor_code
        string vendor_name
        int monthly_sales_qty
        int current_stock
        string status
        bool is_duplicate
        datetime sent_date
    }

    vendor_list {
        int id PK
        string vendor_code UK
        string vendor_name
        bool is_active
    }

    vendor_emails {
        int id PK
        int vendor_id FK
        string email
        string contact_name
        string email_type
        bool is_active
    }

    email_send_log {
        int id PK
        int vendor_id FK
        int stockout_id FK
        string batch_id
        string sender_email
        string recipient_email
        string subject
        int product_count
        string status
        text error_message
        datetime sent_at
    }

    %% AI 表
    ai_generation_history {
        int id PK
        string generation_type
        string product_name
        text input_keywords
        string input_style
        text output_content
        string model_name
        float generation_duration
        string ai_provider
        int input_tokens
        int output_tokens
        int rating
        bool is_favorite
        bool is_used
        int created_by FK
        datetime created_at
    }

    ai_usage_tracking {
        int id PK
        string provider
        string model_name
        string usage_type
        int input_tokens
        int output_tokens
        float total_cost
        date request_date
        int created_by FK
    }

    ai_prompt_templates {
        int id PK
        string name UK
        string description
        string template_type
        text system_prompt
        text user_prompt_template
        float default_temperature
        bool is_active
        bool is_system
    }

    %% 匯入表
    import_jobs {
        int id PK
        string job_type
        string status
        string drive_file_id
        string drive_file_name
        float progress_percent
        int total_rows
        int processed_rows
        datetime created_at
        datetime completed_at
    }

    %% 月份總表
    monthly_summary_analysis {
        int id PK
        int year
        int month
        string department
        string division
        string pm_name
        string brand_name
        int vendor_id
        int sales_amt_curr
        int sales_amt_prev
        int profit_amt_curr
        float conv_rate
    }
```

---

## 📋 資料表清單

### 核心模組 (4 表)

| 資料表 | 說明 | 記錄數 (估計) |
|--------|------|--------------|
| `categories` | 商品分類 | ~20 |
| `products` | 商品主檔 | ~6,000 |
| `price_records` | 價格歷史記錄 | ~500,000 |
| `monthly_summary_analysis` | 月份業績總表 | ~50,000 |

---

### 用戶模組 (2 表)

| 資料表 | 說明 |
|--------|------|
| `users` | 用戶帳號 |
| `login_history` | 登入歷史記錄 |

#### users 欄位詳情

| 欄位 | 類型 | 說明 |
|------|------|------|
| id | INTEGER PK | 用戶 ID |
| username | VARCHAR(50) UK | 帳號名稱 |
| email | VARCHAR(120) UK | 電子郵件 |
| password_hash | VARCHAR(256) | bcrypt 雜湊密碼 |
| role | VARCHAR(20) | 角色 (admin/manager/user) |
| display_name | VARCHAR(100) | 顯示名稱 |
| is_active | BOOLEAN | 是否啟用 |
| password_changed_at | DATETIME | 密碼變更時間 |
| created_at | DATETIME | 建立時間 |
| updated_at | DATETIME | 更新時間 |

---

### 廠商模組 (4 表)

| 資料表 | 說明 |
|--------|------|
| `vendor_stockout` | 廠商缺貨記錄 |
| `vendor_list` | 廠商清單 |
| `vendor_emails` | 廠商聯絡郵件 |
| `email_send_log` | 郵件發送記錄 |

#### vendor_stockout 欄位詳情

| 欄位 | 類型 | 說明 |
|------|------|------|
| id | INTEGER PK | 記錄 ID |
| batch_id | VARCHAR(50) | 批次編號 (YYYYMMDD_HHMMSS) |
| import_date | DATE | 匯入日期 |
| department | VARCHAR(100) | 部別 |
| section | VARCHAR(100) | 課別 |
| pm_name | VARCHAR(100) | PM 姓名 |
| product_code | VARCHAR(100) | 商品料號 |
| product_name | VARCHAR(500) | 商品名稱 |
| vendor_code | VARCHAR(100) | 廠商代碼 |
| vendor_name | VARCHAR(200) | 廠商名稱 |
| monthly_sales_qty | INTEGER | 全月銷量 |
| current_stock | INTEGER | 現有庫存 |
| stockout_days | INTEGER | 缺貨天數 |
| status | VARCHAR(20) | 狀態 (pending/sent/failed) |
| is_duplicate | BOOLEAN | 是否重複 |

---

### AI 模組 (3 表)

| 資料表 | 說明 |
|--------|------|
| `ai_generation_history` | AI 生成歷史 |
| `ai_usage_tracking` | AI 用量追蹤 |
| `ai_prompt_templates` | AI 提示模板 |

#### ai_generation_history 欄位詳情

| 欄位 | 類型 | 說明 |
|------|------|------|
| id | INTEGER PK | 記錄 ID |
| generation_type | VARCHAR(50) | 類型 (copy/recommend/weather_analysis) |
| product_name | VARCHAR(255) | 商品名稱 |
| input_keywords | TEXT (JSON) | 輸入關鍵字 |
| input_style | VARCHAR(50) | 文案風格 |
| output_content | TEXT | 生成內容 |
| model_name | VARCHAR(100) | 模型名稱 |
| generation_duration | FLOAT | 生成耗時 (秒) |
| ai_provider | VARCHAR(20) | AI 提供者 (ollama/gemini) |
| input_tokens | INTEGER | 輸入 Token 數 |
| output_tokens | INTEGER | 輸出 Token 數 |
| rating | INTEGER | 用戶評分 (1-5) |
| is_favorite | BOOLEAN | 是否收藏 |
| is_used | BOOLEAN | 是否已使用 |
| created_by | INTEGER FK | 建立者 |
| created_at | DATETIME | 建立時間 |

---

### 匯入模組 (2 表)

| 資料表 | 說明 |
|--------|------|
| `import_jobs` | 匯入任務 |
| `import_config` | 匯入配置 |

---

### 權限模組 (3 表)

| 資料表 | 說明 |
|--------|------|
| `permissions` | 權限定義 |
| `role_permissions` | 角色權限關聯 |
| `user_permissions` | 用戶權限關聯 |

---

### 通知模組 (2 表)

| 資料表 | 說明 |
|--------|------|
| `notification_templates` | 通知模板 |
| `notification_logs` | 通知發送記錄 |

---

### 趨勢模組 (3 表)

| 資料表 | 說明 |
|--------|------|
| `trend_products` | 趨勢商品 |
| `trend_categories` | 趨勢分類 |
| `trend_keywords` | 趨勢關鍵字 |

---

## 🔑 索引設計

### 高頻查詢索引

```sql
-- 商品查詢
CREATE INDEX idx_products_i_code ON products(i_code);
CREATE INDEX idx_products_category ON products(category);
CREATE INDEX idx_products_status ON products(status);

-- 價格記錄查詢
CREATE INDEX idx_price_records_product_id ON price_records(product_id);
CREATE INDEX idx_price_records_timestamp ON price_records(timestamp);

-- 用戶查詢
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_role ON users(role);

-- 廠商缺貨查詢
CREATE INDEX idx_vendor_stockout_batch_id ON vendor_stockout(batch_id);
CREATE INDEX idx_vendor_stockout_vendor_code ON vendor_stockout(vendor_code);
CREATE INDEX idx_vendor_stockout_status ON vendor_stockout(status);

-- AI 歷史查詢
CREATE INDEX idx_ai_history_type_created ON ai_generation_history(generation_type, created_at);
CREATE INDEX idx_ai_history_favorite ON ai_generation_history(is_favorite, created_at);
```

---

## 📈 資料成長預估

| 資料表 | 日增長量 | 月增長量 | 保留策略 |
|--------|----------|----------|----------|
| price_records | ~6,000 | ~180,000 | 保留 1 年 |
| login_history | ~100 | ~3,000 | 保留 90 天 |
| email_send_log | ~50 | ~1,500 | 永久保留 |
| ai_generation_history | ~200 | ~6,000 | 永久保留 |
| ai_usage_tracking | ~200 | ~6,000 | 永久保留 |

---

## 🔄 資料庫遷移

### 使用 Alembic 管理

```bash
# 建立遷移
alembic revision --autogenerate -m "description"

# 執行遷移
alembic upgrade head

# 回滾遷移
alembic downgrade -1
```