# 檔案儲存 Schema 設計（files / file_usages）

> 狀態：**並行新建已落地（DDL + Model 層）**；scanner 與 live 上傳/resolver 接線尚未做。
> 與現有 `uploaded_files` 並行，不影響 live 上傳/resolver。
> 單站一庫，故不設 `comp_id`；PK 一律 `id`（Snowflake，`Lib\Hash->id()`）。
>
> **已產出**：
> - DDL：[`docs/ai-agents/project-init/file-storage-tables.sql`](./file-storage-tables.sql)（可直接匯入；尚未對任何站台執行）
> - Model：`PHP71/pubs/apps/Models/Files/{Files,Usages}.php` ＋ `Files/DB/{Files,Usages}.php`
>
> **慣例對齊現況**：JSON 欄位用 `longtext utf8mb4_bin` + PHP json_encode/decode（與 uploaded_files 一致，非原生 JSON 型別）；ENGINE=InnoDB、預設 utf8mb3。

---

## 核心原則

- **內嵌 file_id 為權威**：渲染一律以內嵌資料為準
  - 結構化：`albums` 陣列、`meta.poster`
  - 富文本：內文 `<img data-file-id>`
- **`file_usages` 是衍生索引**：由 scanner 掃內嵌資料重建，只服務
  - GC（孤兒檔回收）
  - R2 backfill / 遷移
  - 用途查詢（「這檔被用在哪」）
  - 可隨時 `TRUNCATE` 重建，不影響渲染。
- **R2 遷移靠 `disk`+`host`+`path`**：切換儲存只改這幾欄，內文 file_id 不動。

---

## 表一：`files`（中央檔案表）

```sql
CREATE TABLE files (
    id            BIGINT       NOT NULL,            -- Snowflake PK
    type          VARCHAR(16)  NOT NULL DEFAULT 'other', -- image/video/audio/document/archive/other

    -- 儲存位置（R2 遷移核心）
    disk          VARCHAR(16)  NOT NULL DEFAULT 'local', -- local / r2
    host          VARCHAR(128) NOT NULL DEFAULT '',  -- ''=本機相對；R2→網域
    path          VARCHAR(255) NOT NULL,             -- upload/<date>/<id>.ext

    -- 共同 metadata（所有類型都有）
    original_name VARCHAR(255) NOT NULL DEFAULT '',
    ext           VARCHAR(16)  NOT NULL DEFAULT '',
    mime          VARCHAR(128) NOT NULL DEFAULT '',
    size          BIGINT       NOT NULL DEFAULT 0,   -- bytes
    hash          CHAR(64)     NOT NULL DEFAULT '',  -- sha256，去重用

    -- 兩個 JSON bag（界線見下）
    meta          JSON         NULL,                 -- 檔案本身屬性（隨 type 而異）
    content       JSON         NULL,                 -- 業務文案

    -- 生命週期
    status        TINYINT      NOT NULL DEFAULT 1,   -- 1啟用 0軟刪（GC 先標再實刪）
    creator       BIGINT       NOT NULL DEFAULT 0,
    created_at    INT          NOT NULL DEFAULT 0,
    updated_at    INT          NOT NULL DEFAULT 0,

    PRIMARY KEY (id),
    KEY idx_hash (hash),
    KEY idx_type (type)
);
```

### 固定欄位 vs JSON 的切法
- **固定欄位** = 不分類型、每個檔都有、且要拿來查/排序/GC：
  `type / disk / host / path / original_name / ext / mime / size / hash / status / 時間`
- **`meta` / `content`** = 隨類型而異或業務性、不需獨立索引的，收進 JSON。

### `meta`：檔案本身的屬性（到哪用都一樣，描述「它是什麼」）
| type | meta 範例 |
|---|---|
| `image` | `{ "width":1920, "height":1080, "alt":"上市櫃公司大樓外觀" }` |
| `video` | `{ "width":1920, "height":1080, "duration":120, "codec":"h264", "poster":<files.id> }` |
| `audio` | `{ "duration":180, "bitrate":320 }` |
| `document` | `{ "pages":12 }` |

- `alt` 歸 `meta`：它是圖片**內在的**替代文字（無障礙／SEO），跟著圖走、到處一樣。
- `poster`（影片封面）存**另一張 image 的 `files.id`**，見下「檔案互相引用」。

### `content`：業務文案（編輯填、跟用途有關）
```json
{ "title":"...", "caption":"...", "desc":"..." }
```

---

## 表二：`file_usages`（使用紀錄 / 衍生索引）

```sql
CREATE TABLE file_usages (
    id          BIGINT       NOT NULL,            -- Snowflake PK
    file_id     BIGINT       NOT NULL,            -- → files.id
    entity_type VARCHAR(32)  NOT NULL,            -- 用它的東西是什麼
    entity_id   BIGINT       NOT NULL,            -- postID / catID / 影片 id...
    purpose     VARCHAR(32)  NOT NULL DEFAULT '', -- 這檔的功用
    lng_id      VARCHAR(10)  NOT NULL DEFAULT '', -- 語系；不分語系留 ''
    sort        INT          NOT NULL DEFAULT 0,  -- 同 purpose 內順序（相簿有序）
    created_at  INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_usage (file_id, entity_type, entity_id, purpose, lng_id),
    KEY idx_file (file_id),
    KEY idx_entity (entity_type, entity_id)
);
```

### `entity_type` 合法值
`article / product / category / page / banner / file`
- `file`：檔案互相引用（如 video→poster、未來 PDF→縮圖）。

### `purpose` 合法值（這檔的功用）
`cover / album / attached / slideshow / content / banner / poster`
- `cover`＝封面圖、`poster`＝影片封面、`album`＝相簿、`content`＝內文插圖。

### 兩個 type 不要搞混
| 欄位 | 表 | 回答 | 例 |
|---|---|---|---|
| `files.type` | files | 這**檔案是什麼** | image / video |
| `file_usages.entity_type` | file_usages | 這檔**被什麼東西用** | article / banner |

兩者正交：一張圖（`files.type='image'`）被文章當封面 → `entity_type='article'`、`purpose='cover'`。

---

## 影片封面圖的處理（檔案互相引用）

封面圖本身也是一列 `files`（`type='image'`）。

1. **權威**：video 的 `meta.poster = <封面圖 files.id>`。渲染 `<video poster="resolver(meta.poster)">`。
2. **衍生**：scanner 掃到 `meta.poster` 時補一筆
   ```
   file_id=封面圖id, entity_type='file', entity_id=影片id, purpose='poster'
   ```
   讓 GC 不把封面圖誤判成孤兒。
3. **級聯 GC 自動成立**：影片變孤兒 → 下次重掃時封面圖唯一引用消失 → 封面圖跟著可回收，免寫級聯邏輯。

---

## 去重（dedup）

- 上傳時算 `sha256` → 查 `hash` 命中就重用既有 `files.id`，不重複存檔。
- 配 `file_usages` 完美：1 個實體檔、N 筆使用。
- ⚠ 需求：上傳流程要改成「先算 hash → 查重 → 命中則跳過寫檔」。

---

## GC 查詢

```sql
-- 孤兒檔（沒被任何實體引用）
SELECT f.id, f.path
FROM files f
LEFT JOIN file_usages u ON u.file_id = f.id
WHERE u.file_id IS NULL;
```

安全做法：孤兒先標 `status=0`，確認一段時間沒事再實刪 + 刪 R2 物件。

---

## 進度

- [x] **DDL 定稿**：`file-storage-tables.sql`（尚未對任何站台執行匯入）。
- [x] **Model 層**：`Files`（CRUD + `findByHash` dedup + `disable` 軟刪）、`Usages`（`sync`/`byFile`/`byEntity`/`clear`）。
- [x] **JSON 儲存方式**：用 `longtext utf8mb4_bin`，沿用 uploaded_files 慣例（不依賴原生 JSON 型別）。
- [x] **`files.type` 保留**：好篩好索引。

## Scanner（已落地，待測試站驗證）

`file_usages` 重建器，唯讀掃內容模組儲存表，抽內嵌 file_id，每實體 `Usages->sync`（先刪後增，idempotent 可重跑）。

- [`Files/Scanner.php`](../../../PHP71/pubs/apps/Models/Files/Scanner.php) — **純邏輯抽取引擎（已 CLI 驗證）**：
  - `extract($row)`：pathFields（albums→cover/album/slideshow，子鍵定 purpose）+ htmlFields（content/description 抓 `<img data-file-id>`），去重 + per-purpose sort。
  - `extractEntity($neutral,$langs)`：組合語系中性 + 各語系，標 `lng_id`。
  - `extractPoster($meta)`：取 video `meta.poster`。
- [`Files/Scan.php`](../../../PHP71/pubs/apps/Models/Files/Scan.php) — 編排：`run()` 掃白名單模組、`scanModule($base,$type)`。目前白名單只開 `articles`（schema 已確認）；products/services 待確認後開啟。
- [`Files/DB/Scan.php`](../../../PHP71/pubs/apps/Models/Files/DB/Scan.php) — 唯讀讀取：`binds`（`{base}_bind.albums/content` 中性）、`langs`（`{base}.content/description` 各語系）。

掃描面對齊 live 讀取路徑（`articles as a` JOIN `articles_bind as b`，`a.*` 蓋 `b.*`）：`albums` 從 bind（lng_id=''）、`content/description` 從各語系 `articles`（lng_id=lngID）。

**驗證步驟（測試站 admin71，免守門）**：
1. 匯入 `file-storage-tables.sql` 建表（`files` / `file_usages` 空表）。
2. 匯入 `file-storage-migrate.sql` 把 `uploaded_files` 搬進 `files`（保留 id；GC 要靠 `files` 有資料才測得出孤兒）。
3. FTP 部署：`PHP71/pubs/apps/Models/Files/*`＋`hosts/admin71/apps/conf/routes/conf.route.main.php`。
4. `GET /admin/debug/files/scan` → 重建 file_usages，回傳各模組掃描筆數。
5. `GET /admin/debug/files/gc` → 回傳 files/usages 總數、孤兒數量＋樣本。
6. 重跑 step 4 應得相同結果（`sync` 先刪後增，idempotent）。
> 觸發點在 `conf.route.main.php` 標記 `[暫時]`，驗證完整段可移除。
> migration 搬不過來的：`hash`（先 ''）、`meta.width/height`（先 null）——dedup/補掃時再回填。

## 待辦（下一步，依風險由低到高）

- [ ] **測試站驗證 scanner**：建表 → 跑 `Scan->run()` → 比對 `file_usages` 與 GC 查詢。
- [ ] **擴充模組**：確認 products/services 的 `_bind`/各語系 schema 後加入白名單；banner 用 `banners_files`（結構不同）另寫 driver。
- [ ] **poster 掛點**：video 模組（若有）掃 `meta.poster` 寫 entity_type='file'。
- [ ] **dedup 接線**：上傳流程算 sha256 → `Files->findByHash` 命中則重用、不重複寫檔。會動 live 上傳，後做。
- [ ] **banner desc 等情境文案放哪**：檔案層級放 `files.content`；情境層級需在 `file_usages` 加 `caption` 欄。
- [ ] **live 切換**：上傳寫 `files`、resolver 讀 `files`、舊 `uploaded_files` 遷移——風險最高，最後做。