-- 科技成果转化系统 数据库设计 (PostgreSQL) - 完整版

-- 1. 部门表
CREATE TABLE IF NOT EXISTS departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. 用户表
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL, -- 手机号
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('super_admin', 'admin', 'user', 'maintainer', 'senior_user', 'intermediate_user')),
    real_name VARCHAR(100),
    department VARCHAR(100), -- 所属部门名称
    dept_status VARCHAR(20) DEFAULT 'verified', -- 部门审核状态
    token_version INTEGER DEFAULT 0, -- Token 版本号 (用于单点登录)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 3. 成果基础信息表 (所有类型共用)
CREATE TABLE IF NOT EXISTS achievements (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    type VARCHAR(50) NOT NULL, -- 'paper', 'project', 'award', 'standard', 'monograph', 'report', 'plan', 'patent', 'transformation', 'software'
    name TEXT NOT NULL, -- 成果名称
    main_contributors TEXT, -- 主要完成人 (字符串格式，兼容旧数据)
    all_contributors TEXT, -- 完成人 (字符串格式，兼容旧数据)
    contributor_phones TEXT, -- 完成人手机号 (字符串格式)
    contributors JSONB DEFAULT '[]', -- 结构化完成人列表 [{name, phone, isMain}]
    assigned_departments TEXT[] DEFAULT '{}', -- 归属部门数组 (自动计算)
    achievement_date DATE, -- 时间
    remarks TEXT, -- 备注
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
    audit_comment TEXT, -- 审核意见
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 4. 论文详情表
CREATE TABLE IF NOT EXISTS achievement_paper (
    id SERIAL PRIMARY KEY,
    achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
    paper_type TEXT,
    first_unit TEXT,
    journal_name TEXT,
    publish_date DATE,
    doi VARCHAR(100)
);

-- 5. 项目详情表
CREATE TABLE IF NOT EXISTS achievement_project (
    id SERIAL PRIMARY KEY,
    achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
    project_category TEXT,
    source TEXT
);

-- 6. 奖励详情表
CREATE TABLE IF NOT EXISTS achievement_award (
    id SERIAL PRIMARY KEY,
    achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
    award_type TEXT,
    award_level TEXT,
    award_unit TEXT
);

-- 7. 标准详情表
CREATE TABLE IF NOT EXISTS achievement_standard (
    id SERIAL PRIMARY KEY,
    achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
    standard_type TEXT,
    standard_no VARCHAR(100) NOT NULL,
    implement_date DATE
);

-- 8. 专著详情表
CREATE TABLE IF NOT EXISTS achievement_monograph (
    id SERIAL PRIMARY KEY,
    achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
    publisher TEXT,
    isbn VARCHAR(50)
);

-- 9. 技术报告详情表
CREATE TABLE IF NOT EXISTS achievement_report (
    id SERIAL PRIMARY KEY,
    achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
    report_type TEXT,
    recipient TEXT,
    approver_id INTEGER REFERENCES users(id), -- 签批院领导ID
    approver_name VARCHAR(100) -- 签批院领导姓名 (冗余存储，方便显示)
);

-- 10. 规划详情表
CREATE TABLE IF NOT EXISTS achievement_plan (
    id SERIAL PRIMARY KEY,
    achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE
);

-- 11. 专利详情表
CREATE TABLE IF NOT EXISTS achievement_patent (
    id SERIAL PRIMARY KEY,
    achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
    patent_no VARCHAR(100),
    patent_type TEXT,
    assignee TEXT
);

-- 12. 成果转化详情表
CREATE TABLE IF NOT EXISTS achievement_transformation (
    id SERIAL PRIMARY KEY,
    achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
    trans_method TEXT,
    trans_amount NUMERIC(15, 2),
    project_category TEXT
);

-- 13. 软著详情表
CREATE TABLE IF NOT EXISTS achievement_software (
    id SERIAL PRIMARY KEY,
    achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
    reg_no VARCHAR(100),
    acquisition_method TEXT,
    scope TEXT,
    owner_unit TEXT
);

-- 14. 附件表
CREATE TABLE IF NOT EXISTS achievement_attachments (
    id SERIAL PRIMARY KEY,
    achievement_id INTEGER REFERENCES achievements(id) ON DELETE CASCADE,
    file_name TEXT NOT NULL,
    file_path TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 15. 字典表 (由超级管理员管理)

-- 单位名称表
CREATE TABLE IF NOT EXISTS dict_organizations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 奖励种类表
CREATE TABLE IF NOT EXISTS dict_award_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 奖励等级表
CREATE TABLE IF NOT EXISTS dict_award_levels (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 论文种类表
CREATE TABLE IF NOT EXISTS dict_paper_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 标准种类表
CREATE TABLE IF NOT EXISTS dict_standard_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 项目分类表
CREATE TABLE IF NOT EXISTS dict_project_categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 领导层部门表 (用于筛选签批领导)
CREATE TABLE IF NOT EXISTS dict_leader_departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 16. 审计日志表
CREATE TABLE IF NOT EXISTS audit_logs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    username VARCHAR(50),
    real_name VARCHAR(50),
    ip_address VARCHAR(50),
    method VARCHAR(10),
    url TEXT,
    description VARCHAR(255),
    status INTEGER,
    duration INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 17. 系统公告表
CREATE TABLE IF NOT EXISTS notifications (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    publisher_id INTEGER REFERENCES users(id),
    publisher_name VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_top BOOLEAN DEFAULT FALSE,
    status VARCHAR(20) DEFAULT 'published' -- published, draft, archived
);

-- 18. 系统公告附件表
CREATE TABLE IF NOT EXISTS notification_attachments (
    id SERIAL PRIMARY KEY,
    notification_id INTEGER REFERENCES notifications(id) ON DELETE CASCADE,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 索引优化
CREATE INDEX IF NOT EXISTS idx_achievements_user ON achievements(user_id);
CREATE INDEX IF NOT EXISTS idx_achievements_type ON achievements(type);
CREATE INDEX IF NOT EXISTS idx_achievements_status ON achievements(status);
CREATE INDEX IF NOT EXISTS idx_achievements_assigned_depts ON achievements USING GIN (assigned_departments);
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON audit_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_logs_username ON audit_logs(username);
CREATE INDEX IF NOT EXISTS idx_notifications_created_at ON notifications(created_at DESC);