%% Database Schema (ER Diagram) — 離線 AI Agent 系統
erDiagram
    users {
        uuid id PK "gen_random_uuid()"
        varchar username UK "VARCHAR(100)"
        varchar password_hash "VARCHAR(255)"
        varchar role "admin | user | viewer"
        boolean is_active "DEFAULT TRUE"
        timestamptz created_at
        timestamptz updated_at
    }

    api_tokens {
        uuid id PK "gen_random_uuid()"
        uuid user_id FK "REFERENCES users"
        varchar token_hash "VARCHAR(255)"
        varchar name "VARCHAR(100)"
        timestamptz expires_at
        timestamptz created_at
    }

    documents {
        uuid id PK "gen_random_uuid()"
        varchar filename "VARCHAR(500)"
        text original_path
        varchar file_type "pdf | image | zip"
        bigint file_size_bytes
        varchar file_hash "SHA-256 去重"
        varchar source "upload | km_system"
        uuid uploaded_by FK "REFERENCES users"
        varchar status "pending | processing | completed | failed"
        text error_message
        jsonb metadata
        timestamptz created_at
        timestamptz updated_at
    }

    document_chunks {
        uuid id PK "gen_random_uuid()"
        uuid document_id FK "REFERENCES documents"
        integer chunk_index "原文件中的順序"
        text content "文字內容"
        varchar content_type "text | image | table | mixed"
        vector embedding "vector(768) 文字embedding"
        vector image_embedding "vector(512) 圖像embedding"
        integer token_count
        jsonb metadata "頁碼/段落索引/座標"
        timestamptz created_at
    }

    upload_tasks {
        uuid id PK "gen_random_uuid()"
        uuid user_id FK "REFERENCES users"
        integer total_files
        integer processed_files
        integer failed_files
        varchar status "pending | processing | completed | partial | failed"
        jsonb error_log
        timestamptz created_at
        timestamptz completed_at
    }

    upload_task_files {
        uuid task_id FK "REFERENCES upload_tasks"
        uuid document_id FK "REFERENCES documents"
    }

    conversations {
        uuid id PK "gen_random_uuid()"
        uuid user_id FK "REFERENCES users"
        varchar title "VARCHAR(500)"
        timestamptz created_at
        timestamptz updated_at
    }

    messages {
        uuid id PK "gen_random_uuid()"
        uuid conversation_id FK "REFERENCES conversations"
        varchar role "user | assistant | system"
        text content
        integer token_count
        jsonb sources "引用的chunk IDs與置信度"
        varchar skill_used
        varchar model_used
        integer latency_ms
        timestamptz created_at
    }

    skills {
        uuid id PK "gen_random_uuid()"
        varchar name UK "VARCHAR(100)"
        text description
        varchar version
        boolean is_active "DEFAULT TRUE"
        jsonb config
        timestamptz created_at
    }

    audit_logs {
        bigserial id PK
        uuid user_id FK "REFERENCES users"
        varchar action "query | upload | login | skill_exec"
        varchar resource
        jsonb details
        inet ip_address
        timestamptz created_at
    }

    %% 關聯
    users ||--o{ api_tokens : "擁有"
    users ||--o{ documents : "上傳"
    users ||--o{ upload_tasks : "發起"
    users ||--o{ conversations : "建立"
    users ||--o{ audit_logs : "產生"

    documents ||--o{ document_chunks : "包含"
    documents }o--o{ upload_task_files : "屬於"
    upload_tasks ||--o{ upload_task_files : "包含"

    conversations ||--o{ messages : "包含"