yunhaifinance/backend/migrations/005_create_subcontractor_payment_infos.sql

-- 创建分包商收款信息表
CREATE TABLE IF NOT EXISTS subcontractor_payment_infos (
  id SERIAL PRIMARY KEY,
  subcontractor_id INTEGER NOT NULL,
  account_name TEXT NOT NULL,
  bank_account TEXT NOT NULL,
  bank_name TEXT NOT NULL,
  qr_code TEXT,
  is_primary INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (subcontractor_id) REFERENCES subcontractors(id) ON DELETE CASCADE
);

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_subcontractor_payment_infos_subcontractor_id ON subcontractor_payment_infos(subcontractor_id);
CREATE INDEX IF NOT EXISTS idx_subcontractor_payment_infos_is_primary ON subcontractor_payment_infos(is_primary);

-- 添加注释
COMMENT ON TABLE subcontractor_payment_infos IS '分包商收款信息表';
COMMENT ON COLUMN subcontractor_payment_infos.id IS '主键ID';
COMMENT ON COLUMN subcontractor_payment_infos.subcontractor_id IS '分包商ID';
COMMENT ON COLUMN subcontractor_payment_infos.account_name IS '账户名称';
COMMENT ON COLUMN subcontractor_payment_infos.bank_account IS '银行账号';
COMMENT ON COLUMN subcontractor_payment_infos.bank_name IS '银行名称';
COMMENT ON COLUMN subcontractor_payment_infos.qr_code IS '二维码图片路径';
COMMENT ON COLUMN subcontractor_payment_infos.is_primary IS '是否为主账户（0:否，1:是）';
COMMENT ON COLUMN subcontractor_payment_infos.created_at IS '创建时间';
COMMENT ON COLUMN subcontractor_payment_infos.updated_at IS '更新时间';