# Design Document: Work Statistics System

## Overview

工作统计系统采用前后端分离架构，后端使用Python Flask框架提供RESTful API，前端使用React构建单页应用，数据存储使用PostgreSQL（测试环境使用SQLite）。系统支持人员管理、物品管理、工作记录管理，以及Excel报表导出功能。

## Architecture

```mermaid
graph TB
    subgraph Frontend
        React[React SPA]
    end
    
    subgraph Backend
        API[Flask API Server]
        Services[Business Services]
        Models[SQLAlchemy Models]
        Export[Excel Export Service]
    end
    
    subgraph Database
        PG[(PostgreSQL/SQLite)]
    end
    
    React -->|HTTP GET/POST| API
    API --> Services
    Services --> Models
    Services --> Export
    Models --> PG
```

### Technology Stack

- **Backend**: Python 3.11+, Flask, SQLAlchemy, openpyxl, Flask-RESTX (Swagger文档)
- **Frontend**: React 18, Axios, React Router, Ant Design (简洁美观的UI组件库)
- **Database**: PostgreSQL (production), SQLite (testing)
- **Testing**: pytest, hypothesis (property-based testing)
- **API Documentation**: Swagger UI (通过 Flask-RESTX 自动生成)

## Components and Interfaces

### Backend Structure

```
backend/
├── app/
│   ├── __init__.py          # Flask app factory
│   ├── config.py             # Configuration
│   ├── models/
│   │   ├── __init__.py
│   │   ├── person.py         # Person model
│   │   ├── item.py           # Item model
│   │   └── work_record.py    # WorkRecord model
│   ├── routes/
│   │   ├── __init__.py
│   │   ├── person.py         # Person API routes
│   │   ├── item.py           # Item API routes
│   │   ├── work_record.py    # WorkRecord API routes
│   │   └── export.py         # Export API routes
│   ├── services/
│   │   ├── __init__.py
│   │   ├── person_service.py
│   │   ├── item_service.py
│   │   ├── work_record_service.py
│   │   └── export_service.py
│   └── utils/
│       ├── __init__.py
│       └── validators.py
├── tests/
│   ├── __init__.py
│   ├── conftest.py
│   ├── test_person.py
│   ├── test_item.py
│   ├── test_work_record.py
│   └── test_export.py
├── requirements.txt
└── run.py
```

### API Documentation (Swagger)

系统使用 **Flask-RESTX** 自动生成 Swagger API 文档：

- **访问地址**: `http://localhost:5000/api/docs`
- **功能**: 
  - 自动生成所有API接口文档
  - 支持在线测试API
  - 显示请求/响应模型
  - 支持导出 OpenAPI 3.0 规范

```python
# Flask-RESTX 配置示例
from flask_restx import Api, Resource, fields

api = Api(
    title='Work Statistics API',
    version='1.0',
    description='工作统计系统 API 文档',
    doc='/api/docs'
)

# 定义数据模型
person_model = api.model('Person', {
    'id': fields.Integer(description='人员ID'),
    'name': fields.String(required=True, description='人员姓名')
})
```

### Frontend Structure

```
frontend/
├── src/
│   ├── components/
│   │   ├── Layout.jsx          # 主布局（侧边栏+内容区）
│   │   ├── PersonList.jsx      # 人员列表（Table组件）
│   │   ├── PersonForm.jsx      # 人员表单（Modal+Form）
│   │   ├── ItemList.jsx        # 物品列表
│   │   ├── ItemForm.jsx        # 物品表单
│   │   ├── WorkRecordList.jsx  # 工作记录列表（带筛选）
│   │   ├── WorkRecordForm.jsx  # 工作记录表单
│   │   └── Dashboard.jsx       # 仪表盘（统计卡片+图表）
│   ├── services/
│   │   └── api.js              # API调用封装
│   ├── App.jsx
│   └── index.jsx
├── package.json
└── vite.config.js
```

### UI Design Principles

使用 **Ant Design** 组件库，遵循以下设计原则：

1. **简洁清晰** - 使用 Ant Design 的 Table、Form、Modal 等组件，保持界面整洁
2. **响应式布局** - 使用 Layout 组件实现侧边栏导航 + 内容区布局
3. **一致性** - 统一的按钮样式、表单验证、消息提示
4. **易用性** - 表格支持排序、筛选；表单有清晰的验证提示

### UI Components

| 页面 | 主要组件 | 功能 |
|------|----------|------|
| 人员管理 | Table + Modal + Form | 列表展示、新增/编辑弹窗 |
| 物品管理 | Table + Modal + Form | 列表展示、新增/编辑弹窗 |
| 工作记录 | Table + DatePicker + Select | 列表、日期筛选、人员筛选 |
| 仪表盘 | Card + Statistic | 今日统计、快捷操作 |
| 导出 | Button + DatePicker | 选择月份/年份导出Excel |

### API Endpoints

#### Person API

| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | `/api/persons` | List all persons |
| GET | `/api/persons/<id>` | Get person by ID |
| POST | `/api/persons/create` | Create new person |
| POST | `/api/persons/update` | Update person |
| POST | `/api/persons/delete` | Delete person |

#### Item API

| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | `/api/items` | List all items |
| GET | `/api/items/<id>` | Get item by ID |
| POST | `/api/items/create` | Create new item |
| POST | `/api/items/update` | Update item |
| POST | `/api/items/delete` | Delete item |

#### Work Record API

| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | `/api/work-records` | List work records (with filters) |
| GET | `/api/work-records/<id>` | Get work record by ID |
| POST | `/api/work-records/create` | Create new work record |
| POST | `/api/work-records/update` | Update work record |
| POST | `/api/work-records/delete` | Delete work record |
| GET | `/api/work-records/daily-summary` | Get daily summary |

#### Export API

| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | `/api/export/monthly?year=&month=` | Export monthly Excel |
| GET | `/api/export/yearly?year=` | Export yearly Excel |

### Response Format

All API responses follow this structure:

```json
{
  "success": true,
  "data": { ... },
  "message": "Operation successful"
}
```

Error response:

```json
{
  "success": false,
  "error": "Error message",
  "code": "VALIDATION_ERROR"
}
```

## Data Models

### Person Model

```python
class Person:
    id: int (primary key, auto-increment)
    name: str (required, non-empty)
    created_at: datetime
    updated_at: datetime
```

### Item Model

```python
class Item:
    id: int (primary key, auto-increment)
    name: str (required, non-empty)
    unit_price: float (required, positive, supports decimal values like 10.50)
    created_at: datetime
    updated_at: datetime
```

**Note**: `unit_price` 使用浮点数类型，支持小数点（如 10.50、25.75 等）。在数据库中使用 `NUMERIC(10, 2)` 或 `FLOAT` 类型存储以保证精度。

### WorkRecord Model

```python
class WorkRecord:
    id: int (primary key, auto-increment)
    person_id: int (foreign key -> Person.id)
    item_id: int (foreign key -> Item.id)
    work_date: date (required)
    quantity: int (required, positive)
    created_at: datetime
    updated_at: datetime
    
    # Computed property
    @property
    def total_price(self) -> float:
        return self.item.unit_price * self.quantity  # Returns float, e.g., 10.50 * 5 = 52.50
```

**Note**: `total_price` 是计算属性，返回浮点数，等于 `unit_price * quantity`。

### Entity Relationship Diagram

```mermaid
erDiagram
    Person ||--o{ WorkRecord : has
    Item ||--o{ WorkRecord : has
    
    Person {
        int id PK
        string name
        datetime created_at
        datetime updated_at
    }
    
    Item {
        int id PK
        string name
        decimal unit_price
        datetime created_at
        datetime updated_at
    }
    
    WorkRecord {
        int id PK
        int person_id FK
        int item_id FK
        date work_date
        int quantity
        datetime created_at
        datetime updated_at
    }
```

## Excel Export Format

### Detail Sheet (明细表)

| 人员 | 日期 | 物品 | 单价 | 数量 | 总价 |
|------|------|------|------|------|------|
| 张三 | 2024-01-01 | 物品A | 10.50 | 5 | 52.50 |
| 张三 | 2024-01-01 | 物品B | 20.75 | 3 | 62.25 |

### Monthly Summary Sheet (月度汇总)

| 人员 | 总金额 |
|------|--------|
| 张三 | 1500.00 |
| 李四 | 2000.00 |
| **合计** | **3500.00** |

### Yearly Summary Sheet (年度汇总 - 按月)

| 人员 | 1月 | 2月 | ... | 12月 | 年度合计 |
|------|-----|-----|-----|------|----------|
| 张三 | 1500 | 1800 | ... | 2000 | 20000 |
| 李四 | 2000 | 2200 | ... | 2500 | 25000 |
| **合计** | 3500 | 4000 | ... | 4500 | 45000 |



## Correctness Properties

*A property is a characteristic or behavior that should hold true across all valid executions of a system—essentially, a formal statement about what the system should do. Properties serve as the bridge between human-readable specifications and machine-verifiable correctness guarantees.*

### Property 1: Person CRUD Round-Trip

*For any* valid person name, creating a person, then retrieving it by ID, should return the same name. Updating the person's name and retrieving again should return the new name. Deleting the person should make it no longer retrievable.

**Validates: Requirements 1.1, 1.2, 1.3, 1.4**

### Property 2: Person Name Validation

*For any* string composed entirely of whitespace (including empty string), attempting to create or update a person with that name should be rejected with a validation error.

**Validates: Requirements 1.5**

### Property 3: Item CRUD Round-Trip

*For any* valid item (non-empty name, positive unit_price), creating an item, then retrieving it by ID, should return the same name and unit_price. Updating the item and retrieving again should return the updated values. Deleting the item should make it no longer retrievable.

**Validates: Requirements 2.1, 2.2, 2.3, 2.4**

### Property 4: Item Validation

*For any* item with empty name or non-positive unit_price, attempting to create or update should be rejected with a validation error.

**Validates: Requirements 2.5**

### Property 5: Work Record Total Price Calculation

*For any* work record with a valid person, item, and positive quantity, the total_price should equal item.unit_price * quantity.

**Validates: Requirements 3.1, 3.3**

### Property 6: Work Record Filter Consistency

*For any* set of work records and a filter (person_id or date_range), the filtered results should contain only records matching the filter criteria.

**Validates: Requirements 3.2**

### Property 7: Work Record Quantity Validation

*For any* quantity that is zero or negative, attempting to create or update a work record should be rejected with a validation error.

**Validates: Requirements 3.5**

### Property 8: Work Record Reference Validation

*For any* non-existent person_id or item_id, attempting to create a work record should be rejected with a reference error.

**Validates: Requirements 3.6**

### Property 9: Daily Summary Consistency

*For any* date with work records, the daily summary total for each person should equal the sum of their individual work record total_prices for that date.

**Validates: Requirements 4.1, 4.2**

### Property 10: Monthly Export Completeness

*For any* year and month with work records, the exported Excel should contain exactly all work records for that month, and the summary sheet totals should equal the sum of detail records.

**Validates: Requirements 5.1, 5.3**

### Property 11: Yearly Export Completeness

*For any* year with work records, the exported Excel should contain exactly all work records for that year, and the monthly breakdown totals should sum to the yearly total.

**Validates: Requirements 6.1, 6.3**

### Property 12: API Response Consistency

*For any* API request, the response should contain either {success: true, data: ...} or {success: false, error: ...} structure.

**Validates: Requirements 8.3**

### Property 13: Referential Integrity

*For any* person or item with associated work records, deleting that person or item should either fail with an error or cascade delete the associated work records (based on configuration).

**Validates: Requirements 7.4**

## Error Handling

### Validation Errors (HTTP 400)

- Empty or whitespace-only person name
- Empty item name
- Non-positive item unit_price
- Non-positive work record quantity
- Invalid date format
- Missing required fields

### Reference Errors (HTTP 404)

- Person not found by ID
- Item not found by ID
- Work record not found by ID
- Non-existent person_id or item_id in work record

### Server Errors (HTTP 500)

- Database connection failure
- Excel generation failure
- Unexpected exceptions

### Error Response Format

```python
{
    "success": False,
    "error": "Human-readable error message",
    "code": "ERROR_CODE",  # e.g., VALIDATION_ERROR, NOT_FOUND, REFERENCE_ERROR
    "details": {}  # Optional additional details
}
```

## Testing Strategy

### Testing Framework

- **Unit Tests**: pytest
- **Property-Based Tests**: hypothesis (Python PBT library)
- **Frontend Tests**: Jest + React Testing Library

### Unit Tests

Unit tests focus on specific examples and edge cases:

- Test each API endpoint with valid inputs
- Test validation error cases (empty names, invalid prices)
- Test reference error cases (non-existent IDs)
- Test Excel export format and content

### Property-Based Tests

Property-based tests verify universal properties across many generated inputs:

- **Minimum 100 iterations** per property test
- Each test references its design document property
- Tag format: **Feature: work-statistics-system, Property N: [property description]**

### Test Configuration

```python
# conftest.py
import pytest
from hypothesis import settings

# Configure hypothesis for minimum 100 examples
settings.register_profile("ci", max_examples=100)
settings.load_profile("ci")
```

### Test Coverage Goals

- All CRUD operations for Person, Item, WorkRecord
- All validation rules
- Total price calculation
- Filter functionality
- Daily summary aggregation
- Excel export content and format