-- iBilik Capital Database v1.3 - 中文 / English 雙語資料欄位升級
-- Compatible: MySQL 5.7.44
-- 原則：固定介面文字使用 PHP 語言包；需要由資料庫維護或顯示的業務名稱/說明使用 *_zh + *_en。

ALTER TABLE permission_catalog
  ADD COLUMN name_en VARCHAR(120) NULL COMMENT '權限英文名稱；英文介面顯示使用，空白時回退中文' AFTER name_zh,
  ADD COLUMN description_en VARCHAR(255) NULL COMMENT '權限英文用途說明；英文介面顯示使用，空白時回退中文' AFTER description_zh;

ALTER TABLE report_definitions
  ADD COLUMN name_en VARCHAR(160) NULL COMMENT '報表英文名稱；英文介面顯示使用，空白時回退中文' AFTER name_zh,
  ADD COLUMN description_en VARCHAR(500) NULL COMMENT '報表英文用途說明；英文介面顯示使用，空白時回退中文' AFTER description_zh;

ALTER TABLE approval_authorities ADD COLUMN name_en VARCHAR(160) NULL COMMENT '審批權限英文名稱；空白時回退中文' AFTER name_zh;
ALTER TABLE calculation_outputs ADD COLUMN label_en VARCHAR(160) NULL COMMENT '計算輸出項目英文標籤；空白時回退中文' AFTER label_zh;
-- products 已於 v1.0 具備 name_en，不重複新增。
ALTER TABLE capital_pools ADD COLUMN name_en VARCHAR(160) NULL COMMENT '資金池英文名稱；空白時回退中文' AFTER name_zh;
ALTER TABLE cash_accounts ADD COLUMN name_en VARCHAR(160) NULL COMMENT '現金帳戶英文名稱；空白時回退中文' AFTER name_zh;
ALTER TABLE control_alerts ADD COLUMN message_en VARCHAR(500) NULL COMMENT '控制警報英文訊息；空白時回退中文' AFTER message_zh;
ALTER TABLE forecast_scenarios ADD COLUMN name_en VARCHAR(160) NULL COMMENT '預測情境英文名稱；空白時回退中文' AFTER name_zh;
ALTER TABLE formula_parameters ADD COLUMN label_en VARCHAR(160) NULL COMMENT '公式參數英文標籤；空白時回退中文' AFTER label_zh;
ALTER TABLE gl_accounts ADD COLUMN name_en VARCHAR(160) NULL COMMENT '會計科目英文名稱；空白時回退中文' AFTER name_zh;
ALTER TABLE hard_gate_results ADD COLUMN gate_name_en VARCHAR(160) NULL COMMENT '硬性閘門英文名稱；空白時回退中文' AFTER gate_name_zh;
ALTER TABLE journal_entries ADD COLUMN description_en VARCHAR(500) NULL COMMENT '會計傳票英文說明；空白時回退中文' AFTER description_zh;
ALTER TABLE journal_lines ADD COLUMN description_en VARCHAR(500) NULL COMMENT '會計分錄英文說明；空白時回退中文' AFTER description_zh;
ALTER TABLE pool_maturity_alerts ADD COLUMN message_en VARCHAR(500) NULL COMMENT 'Pool 到期風險英文訊息；空白時回退中文' AFTER message_zh;
ALTER TABLE role_templates ADD COLUMN name_en VARCHAR(160) NULL COMMENT '角色模板英文名稱；空白時回退中文' AFTER name_zh,
  ADD COLUMN description_en VARCHAR(500) NULL COMMENT '角色模板英文說明；空白時回退中文' AFTER description_zh;
ALTER TABLE scoring_criteria ADD COLUMN name_en VARCHAR(160) NULL COMMENT '評分準則英文名稱；空白時回退中文' AFTER name_zh;
ALTER TABLE scoring_templates ADD COLUMN name_en VARCHAR(160) NULL COMMENT '評分模板英文名稱；空白時回退中文' AFTER name_zh;
ALTER TABLE sensitivity_runs ADD COLUMN scenario_name_en VARCHAR(160) NULL COMMENT '敏感度情境英文名稱；空白時回退中文' AFTER scenario_name_zh;
ALTER TABLE stress_tests ADD COLUMN scenario_name_en VARCHAR(160) NULL COMMENT '壓力測試情境英文名稱；空白時回退中文' AFTER scenario_name_zh;
ALTER TABLE watchlist_cases ADD COLUMN reason_en VARCHAR(500) NULL COMMENT 'Watchlist 英文原因；空白時回退中文' AFTER reason_zh;

UPDATE permission_catalog SET name_en='View Dashboard', description_en='Allows access to dashboard information within the user authorized scope.' WHERE permission_code='DASHBOARD_VIEW';
UPDATE permission_catalog SET name_en='View Users', description_en='Allows viewing user accounts and account status.' WHERE permission_code='USER_VIEW';
UPDATE permission_catalog SET name_en='Manage User Permissions', description_en='Allows user function and report permissions to be adjusted; operation remains restricted to Super Admin.' WHERE permission_code='USER_PERMISSION_EDIT';
UPDATE permission_catalog SET name_en='View Deals', description_en='Allows viewing Deal master data and authorized financial information.' WHERE permission_code='DEAL_VIEW';
UPDATE permission_catalog SET name_en='Create Deal', description_en='Allows creation of a new Deal.' WHERE permission_code='DEAL_CREATE';
UPDATE permission_catalog SET name_en='Edit Deal', description_en='Allows editing an unlocked Deal within workflow rules.' WHERE permission_code='DEAL_EDIT';
UPDATE permission_catalog SET name_en='Run Financial Model', description_en='Allows preview or recalculation using an approved formula version.' WHERE permission_code='CALCULATION_RUN';
UPDATE permission_catalog SET name_en='Override Editable Parameters', description_en='Allows modification of parameters defined as editable by the formula.' WHERE permission_code='CALCULATION_OVERRIDE';
UPDATE permission_catalog SET name_en='View Investors', description_en='Allows viewing investors and their capital lots.' WHERE permission_code='INVESTOR_VIEW';
UPDATE permission_catalog SET name_en='Manage Investors', description_en='Allows creating and maintaining investor records.' WHERE permission_code='INVESTOR_EDIT';
UPDATE permission_catalog SET name_en='Post Actual Collection', description_en='Allows posting and allocating actual collections.' WHERE permission_code='COLLECTION_POST';
UPDATE permission_catalog SET name_en='Request Disbursement', description_en='Allows creation of disbursement requests.' WHERE permission_code='DISBURSEMENT_REQUEST';
UPDATE permission_catalog SET name_en='Approve Disbursement', description_en='Allows approval of disbursements within the assigned authority level.' WHERE permission_code='DISBURSEMENT_APPROVE';
UPDATE permission_catalog SET name_en='View Recycled Capital Pool', description_en='Allows viewing Pools, Source Lots, redeployment loans and secondary returns.' WHERE permission_code='POOL_VIEW';
UPDATE permission_catalog SET name_en='Transfer Recovered Principal to Pool', description_en='Allows eligible recovered principal to be transferred into a Pool.' WHERE permission_code='POOL_TRANSFER';
UPDATE permission_catalog SET name_en='Deploy Pool Capital', description_en='Allows eligible Source Lots that are not risk-blocked to be redeployed.' WHERE permission_code='POOL_DEPLOY';
UPDATE permission_catalog SET name_en='Post Pool Collection', description_en='Allows posting secondary loan principal, interest and fee collections.' WHERE permission_code='POOL_COLLECTION';
UPDATE permission_catalog SET name_en='View Accounting', description_en='Allows viewing journals and general ledger accounts.' WHERE permission_code='ACCOUNTING_VIEW';
UPDATE permission_catalog SET name_en='Post Journal', description_en='Allows posting balanced journal entries.' WHERE permission_code='ACCOUNTING_POST';
UPDATE permission_catalog SET name_en='Investor Report', description_en='View investor principal, returns, maturity and repayment information.' WHERE permission_code='REPORT_INVESTOR';
UPDATE permission_catalog SET name_en='Capital Fund Report', description_en='View Capital fund sources, uses and ledger movements.' WHERE permission_code='REPORT_CAPITAL';
UPDATE permission_catalog SET name_en='Pool Capital Lineage Report', description_en='View Source Lot origin, redeployment, secondary returns and maturity risk.' WHERE permission_code='REPORT_POOL';
UPDATE permission_catalog SET name_en='Liquidity Forecast Report', description_en='View cash balances, future obligations and cash flow forecasts.' WHERE permission_code='REPORT_LIQUIDITY';
UPDATE permission_catalog SET name_en='Audit Report', description_en='View login, permission and sensitive-operation audit records.' WHERE permission_code='REPORT_AUDIT';

UPDATE report_definitions SET name_en='Investor Position & Maturity Report', description_en='Principal, paid returns, outstanding principal and maturity date for each investor lot.' WHERE report_code='RPT_INVESTOR_POSITION';
UPDATE report_definitions SET name_en='Capital Lineage Report', description_en='Trace investor or funder capital through Deal, collection, Pool and final repayment.' WHERE report_code='RPT_CAPITAL_LINEAGE';
UPDATE report_definitions SET name_en='Recycled Capital Pool Report', description_en='Shows each Source Lot origin, collection month, redeployment, interest attribution and time remaining to investor maturity.' WHERE report_code='RPT_POOL_LINEAGE';
UPDATE report_definitions SET name_en='Liquidity & Maturity Forecast', description_en='Shows cash, future investor/funder obligations and 30-day, 13-week and monthly forecasts.' WHERE report_code='RPT_LIQUIDITY_FORECAST';
UPDATE report_definitions SET name_en='Account Security & Audit Report', description_en='Shows failed logins, IP locks, permission changes and sensitive operations.' WHERE report_code='RPT_SECURITY_AUDIT';
