-- new user table
CREATE TABLE IF NOT EXISTS users
(
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    username       text NOT NULL UNIQUE,
    password       text NOT NULL,
    role           text NOT NULL, -- admin user
    display_name   text NOT NULL,
    account_status text NOT NULL    DEFAULT 'active',
    last_login_at  timestamptz,
    created_at     timestamptz      DEFAULT now()
);

-- add user reference to data_assets
ALTER TABLE data_assets
    ADD COLUMN IF NOT EXISTS user_id uuid REFERENCES users (id);

-- add agent columns to data_assets
ALTER TABLE data_assets
    ADD COLUMN IF NOT EXISTS agent_asset_summary text;
COMMENT ON COLUMN data_assets.agent_asset_summary IS '智能助手对当前资产的简短画像总结';

ALTER TABLE data_assets
    ADD COLUMN IF NOT EXISTS agent_recommended_tasks jsonb;
COMMENT ON COLUMN data_assets.agent_recommended_tasks IS '推荐的适用任务列表';

ALTER TABLE data_assets
    ADD COLUMN IF NOT EXISTS agent_risk_per_mission_advice text;
COMMENT ON COLUMN data_assets.agent_risk_per_mission_advice IS '当前资产更适合的权限与风险建议';

ALTER TABLE data_assets
    ADD COLUMN IF NOT EXISTS agent_asset_explanation text;
COMMENT ON COLUMN data_assets.agent_asset_explanation IS '为什么该资产当前基础价值较高/中/低';

-- add user reference to buyer_requests
ALTER TABLE buyer_requests
    ADD COLUMN IF NOT EXISTS user_id uuid REFERENCES users (id);

-- add agent to pricing_results
ALTER TABLE pricing_results
    ADD COLUMN IF NOT EXISTS agent_task_match_explanation text;
COMMENT ON COLUMN pricing_results.agent_task_match_explanation IS '解释当前数据与任务的匹配关系';

ALTER TABLE pricing_results
    ADD COLUMN IF NOT EXISTS agent_risk_advice text;
COMMENT ON COLUMN pricing_results.agent_risk_advice IS '解释当前隐私风险与权限建议';

ALTER TABLE pricing_results
    ADD COLUMN IF NOT EXISTS agent_budget_advice text;
COMMENT ON COLUMN pricing_results.agent_budget_advice IS '根据预算给出的建议';

ALTER TABLE pricing_results
    ADD COLUMN IF NOT EXISTS agent_next_action text;
COMMENT ON COLUMN pricing_results.agent_next_action IS '建议接下来做什么';

-- add agent to
ALTER TABLE validations
    ADD COLUMN IF NOT EXISTS agent_validation_explanation text;
COMMENT ON COLUMN validations.agent_validation_explanation IS '对当前验证结果的自然语言解释';

ALTER TABLE validations
    ADD COLUMN IF NOT EXISTS agent_continue_trade_advice text;
COMMENT ON COLUMN validations.agent_continue_trade_advice IS '对是否继续成交的建议';

ALTER TABLE validations
    ADD COLUMN IF NOT EXISTS agent_delivery_advice text;
COMMENT ON COLUMN validations.agent_delivery_advice IS '对交付方式的建议';

ALTER TABLE validations
    ADD COLUMN IF NOT EXISTS agent_risk_notice text;
COMMENT ON COLUMN validations.agent_risk_notice IS '对后续交易的风险提示';

-- add user to order
ALTER TABLE orders
    ADD COLUMN IF NOT EXISTS user_id uuid REFERENCES users (id);