pd_backend/sql/init_pd_schema.sql

-- =====================================================
-- 信息工程院日常绩效分配管理系统 - 数据库初始化脚本
-- Schema: pd
-- =====================================================

-- 1. 项目表
CREATE TABLE IF NOT EXISTS pd.pd_project (
    project_id BIGSERIAL PRIMARY KEY,
    project_name VARCHAR(200) NOT NULL,
    project_type VARCHAR(50),
    contract_amount DECIMAL(18,4) DEFAULT 0,
    estimated_output DECIMAL(18,4) DEFAULT 0,
    external_cost DECIMAL(18,4) DEFAULT 0,
    actual_output DECIMAL(18,4) DEFAULT 0,
    total_output DECIMAL(18,4) DEFAULT 0,
    project_status VARCHAR(20) DEFAULT '前期策划但未开工',
    risk_level VARCHAR(10),
    success_rate DECIMAL(5,2),
    lead_dept_id BIGINT,
    start_date DATE,
    end_date DATE,
    is_reviewed CHAR(1) DEFAULT '0',
    review_status VARCHAR(20),
    del_flag CHAR(1) DEFAULT '0',
    remark VARCHAR(500),
    create_by VARCHAR(64),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE pd.pd_project IS '项目信息表';
COMMENT ON COLUMN pd.pd_project.project_id IS '项目ID';
COMMENT ON COLUMN pd.pd_project.project_name IS '项目名称（唯一）';
COMMENT ON COLUMN pd.pd_project.project_type IS '项目类型';
COMMENT ON COLUMN pd.pd_project.contract_amount IS '合同额（万元，4位小数）';
COMMENT ON COLUMN pd.pd_project.estimated_output IS '预估产值（万元）';
COMMENT ON COLUMN pd.pd_project.external_cost IS '外协成本（万元）';
COMMENT ON COLUMN pd.pd_project.actual_output IS '实际产值（合同额-外协成本）';
COMMENT ON COLUMN pd.pd_project.total_output IS '项目总产值';
COMMENT ON COLUMN pd.pd_project.project_status IS '项目状态：前期策划但未开工/前期策划并且同步开工/已完成招投标在建/完工/作废';
COMMENT ON COLUMN pd.pd_project.risk_level IS '风险等级：高/中/低';
COMMENT ON COLUMN pd.pd_project.success_rate IS '立项成功率(%)';
COMMENT ON COLUMN pd.pd_project.lead_dept_id IS '牵头部门ID';
COMMENT ON COLUMN pd.pd_project.start_date IS '立项时间';
COMMENT ON COLUMN pd.pd_project.end_date IS '完工时间';
COMMENT ON COLUMN pd.pd_project.is_reviewed IS '是否已审核确认（0否 1是）';
COMMENT ON COLUMN pd.pd_project.review_status IS '审核状态';
COMMENT ON COLUMN pd.pd_project.del_flag IS '删除标志（0正常 1作废）';
COMMENT ON COLUMN pd.pd_project.remark IS '备注（立项风险说明等）';

-- 2. 项目协作部门关联表
CREATE TABLE IF NOT EXISTS pd.pd_project_collaboration (
    collaboration_id BIGSERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL,
    dept_id BIGINT NOT NULL,
    is_confirmed CHAR(1) DEFAULT '0',
    confirm_by VARCHAR(64),
    confirm_time TIMESTAMP,
    create_by VARCHAR(64),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE pd.pd_project_collaboration IS '项目协作部门关联表';
COMMENT ON COLUMN pd.pd_project_collaboration.project_id IS '关联项目ID';
COMMENT ON COLUMN pd.pd_project_collaboration.dept_id IS '协作部门ID';
COMMENT ON COLUMN pd.pd_project_collaboration.is_confirmed IS '协作部门是否确认（0否 1是）';
COMMENT ON COLUMN pd.pd_project_collaboration.confirm_by IS '确认人';
COMMENT ON COLUMN pd.pd_project_collaboration.confirm_time IS '确认时间';

-- 3. 月度产值填报记录表
CREATE TABLE IF NOT EXISTS pd.pd_output_value (
    output_id BIGSERIAL PRIMARY KEY,
    project_id BIGINT NOT NULL,
    dept_id BIGINT NOT NULL,
    output_month VARCHAR(7) NOT NULL,
    actual_progress DECIMAL(5,1) DEFAULT 0,
    output_value DECIMAL(18,4) DEFAULT 0,
    is_collaboration CHAR(1) DEFAULT '0',
    collaboration_id BIGINT,
    progress_note VARCHAR(500),
    submit_status VARCHAR(20) DEFAULT 'draft',
    review_status VARCHAR(20) DEFAULT 'pending',
    review_comment VARCHAR(500),
    reviewed_by VARCHAR(64),
    reviewed_time TIMESTAMP,
    create_by VARCHAR(64),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE pd.pd_output_value IS '月度产值填报记录表';
COMMENT ON COLUMN pd.pd_output_value.project_id IS '关联项目ID';
COMMENT ON COLUMN pd.pd_output_value.dept_id IS '填报部门ID';
COMMENT ON COLUMN pd.pd_output_value.output_month IS '产值月份（YYYY-MM）';
COMMENT ON COLUMN pd.pd_output_value.actual_progress IS '当月实际进度(%)';
COMMENT ON COLUMN pd.pd_output_value.output_value IS '当月产值（万元）';
COMMENT ON COLUMN pd.pd_output_value.is_collaboration IS '是否协作部分产值（0否 1是）';
COMMENT ON COLUMN pd.pd_output_value.collaboration_id IS '协作申请ID';
COMMENT ON COLUMN pd.pd_output_value.progress_note IS '进度说明';
COMMENT ON COLUMN pd.pd_output_value.submit_status IS '提交状态：draft/提交/已确认/逾期';
COMMENT ON COLUMN pd.pd_output_value.review_status IS '审核状态：pending/通过/不通过';
COMMENT ON COLUMN pd.pd_output_value.review_comment IS '审核意见';
COMMENT ON COLUMN pd.pd_output_value.reviewed_by IS '审核人';
COMMENT ON COLUMN pd.pd_output_value.reviewed_time IS '审核时间';

-- 4. 绩效核算表
CREATE TABLE IF NOT EXISTS pd.pd_performance (
    performance_id BIGSERIAL PRIMARY KEY,
    dept_id BIGINT NOT NULL,
    perf_month VARCHAR(7) NOT NULL,
    total_output_value DECIMAL(18,4) DEFAULT 0,
    performance_amount DECIMAL(18,4) DEFAULT 0,
    calculation_ratio DECIMAL(5,4) DEFAULT 0.2000,
    create_by VARCHAR(64),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_by VARCHAR(64),
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE pd.pd_performance IS '绩效核算表';
COMMENT ON COLUMN pd.pd_performance.dept_id IS '部门ID';
COMMENT ON COLUMN pd.pd_performance.perf_month IS '绩效月份（YYYY-MM）';
COMMENT ON COLUMN pd.pd_performance.total_output_value IS '当月审核通过产值总和（万元）';
COMMENT ON COLUMN pd.pd_performance.performance_amount IS '绩效总额（万元）';
COMMENT ON COLUMN pd.pd_performance.calculation_ratio IS '核算比例（默认0.20即20%）';

-- 5. 通知消息表
CREATE TABLE IF NOT EXISTS pd.pd_notification (
    notification_id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    notify_type VARCHAR(50),
    target_user_id BIGINT,
    is_read CHAR(1) DEFAULT '0',
    read_time TIMESTAMP,
    send_status VARCHAR(20) DEFAULT 'pending',
    create_by VARCHAR(64),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE pd.pd_notification IS '通知消息表';
COMMENT ON COLUMN pd.pd_notification.title IS '通知标题';
COMMENT ON COLUMN pd.pd_notification.content IS '通知内容';
COMMENT ON COLUMN pd.pd_notification.notify_type IS '通知类型：填报提醒/审核提醒/逾期提醒/协作提醒/结果反馈';
COMMENT ON COLUMN pd.pd_notification.target_user_id IS '接收用户ID';
COMMENT ON COLUMN pd.pd_notification.is_read IS '是否已读（0未读 1已读）';
COMMENT ON COLUMN pd.pd_notification.read_time IS '阅读时间';
COMMENT ON COLUMN pd.pd_notification.send_status IS '发送状态：pending/已发送/失败';

-- 创建索引
CREATE INDEX IF NOT EXISTS idx_project_lead_dept ON pd.pd_project(lead_dept_id);
CREATE INDEX IF NOT EXISTS idx_project_status ON pd.pd_project(project_status);
CREATE INDEX IF NOT EXISTS idx_project_del_flag ON pd.pd_project(del_flag);
CREATE INDEX IF NOT EXISTS idx_collaboration_project ON pd.pd_project_collaboration(project_id);
CREATE INDEX IF NOT EXISTS idx_collaboration_dept ON pd.pd_project_collaboration(dept_id);
CREATE INDEX IF NOT EXISTS idx_output_project ON pd.pd_output_value(project_id);
CREATE INDEX IF NOT EXISTS idx_output_dept ON pd.pd_output_value(dept_id);
CREATE INDEX IF NOT EXISTS idx_output_month ON pd.pd_output_value(output_month);
CREATE INDEX IF NOT EXISTS idx_output_submit_status ON pd.pd_output_value(submit_status);
CREATE INDEX IF NOT EXISTS idx_output_review_status ON pd.pd_output_value(review_status);
CREATE INDEX IF NOT EXISTS idx_performance_dept ON pd.pd_performance(dept_id);
CREATE INDEX IF NOT EXISTS idx_performance_month ON pd.pd_performance(perf_month);
CREATE INDEX IF NOT EXISTS idx_notification_user ON pd.pd_notification(target_user_id);
CREATE INDEX IF NOT EXISTS idx_notification_type ON pd.pd_notification(notify_type);
CREATE INDEX IF NOT EXISTS idx_notification_read ON pd.pd_notification(is_read);

-- 插入初始系统配置数据（使用 RuoYi 已有的 sys_config 表，放在 ry schema）
INSERT INTO ry.sys_config (config_name, config_key, config_value, config_type, create_by, create_time)
VALUES
    ('绩效核算比例(%)', 'perf.calculation_ratio', '20', 'Y', 'admin', CURRENT_TIMESTAMP),
    ('月度填报开始日', 'perf.fill_start_day', '1', 'Y', 'admin', CURRENT_TIMESTAMP),
    ('月度填报截止日', 'perf.fill_end_day', '12', 'Y', 'admin', CURRENT_TIMESTAMP),
    ('月度审核开始日', 'perf.review_start_day', '13', 'Y', 'admin', CURRENT_TIMESTAMP),
    ('月度审核截止日', 'perf.review_end_day', '16', 'Y', 'admin', CURRENT_TIMESTAMP)
ON CONFLICT DO NOTHING;

-- 插入默认6个部门数据
INSERT INTO ry.sys_dept (parent_id, ancestors, dept_name, order_num, leader, status, del_flag, create_by, create_time)
VALUES
    (0, '0', '数智房地事业部', 1, '', '0', '0', 'admin', CURRENT_TIMESTAMP),
    (0, '0', '数字孪生事业部', 2, '', '0', '0', 'admin', CURRENT_TIMESTAMP),
    (0, '0', '策划研发运维部', 3, '', '0', '0', 'admin', CURRENT_TIMESTAMP),
    (0, '0', '综合战略事业部', 4, '', '0', '0', 'admin', CURRENT_TIMESTAMP),
    (0, '0', '商务拓展运营部', 5, '', '0', '0', 'admin', CURRENT_TIMESTAMP),
    (0, '0', '国土空间数据部', 6, '', '0', '0', 'admin', CURRENT_TIMESTAMP)
ON CONFLICT DO NOTHING;