-- 增加指标分类字段, 默认0
ALTER TABLE public.evaluation_item_used
    ADD indicator_type int4 NOT NULL DEFAULT 0;

COMMENT ON COLUMN public.evaluation_item_used.indicator_type IS '指标类型';

-- 增加项目表-任务负责人ID
ALTER TABLE public.evaluation_project
    ADD principal_id text NULL;

COMMENT ON COLUMN public.evaluation_project.principal_id IS '任务负责人ID';

-- 增加评估Cache数据缓存表-任务里程碑JSON
ALTER TABLE public.staff_assess_cache
    ADD assess_task_stages jsonb NULL;

COMMENT ON COLUMN public.staff_assess_cache.assess_task_stages IS '任务里程碑内容';

-- 添加表 task
CREATE TABLE public.task(
    id bigserial NOT NULL,
    created_at timestamptz NULL,
    updated_at timestamptz NULL,
    deleted_at timestamptz NULL,
    "name" text NULL,
    alias text NULL,
    company_id int8 NULL,
    leader jsonb NULL,
    status int8 NULL,
    "level" int8 NULL,
    level_name text NULL,
    related_user jsonb NULL,
    run_at int8 NULL,
    stop_at int8 NULL,
    anomaly int8 NULL DEFAULT 0,
    warn_flag int4 NULL,
    current_stage jsonb NULL,
    last_stage jsonb NULL,
    CONSTRAINT task_pkey PRIMARY KEY (id)
);

-- 添加表 task_ignore
CREATE TABLE public.task_ignore(
    id bigserial NOT NULL,
    task_id int8 NULL,
    user_id int8 NULL,
    created_at timestamptz NULL
);

-- 添加表 task_level
CREATE TABLE public.task_level(
    id bigserial NOT NULL,
    level_name text NULL,
    company_id int8 NULL,
    CONSTRAINT task_level_pkey PRIMARY KEY (id)
);

-- 添加表 task_record
CREATE TABLE public.task_record(
    id bigserial NOT NULL,
    company_id int8 NULL,
    staff_assess_id int8 NULL,
    task_id int8 NULL,
    task_category text NULL,
    task_name text NULL,
    task_alias text NULL,
    task_leader jsonb NULL,
    assist_level int8 NULL,
    assist_content text NULL,
    task_stages jsonb NULL,
    task_stage_check jsonb NULL,
    created_at timestamptz NULL,
    updated_at timestamptz NULL,
    deleted_at timestamptz NULL,
    anomaly_state int8 NULL DEFAULT 0,
    CONSTRAINT task_record_pkey PRIMARY KEY (id)
);

-- 添加表 task_stage
CREATE TABLE public.task_stage(
    id bigserial NOT NULL,
    task_id int8 NULL,
    created_at timestamptz NULL,
    updated_at timestamptz NULL,
    deleted_at timestamptz NULL,
    "name" text NULL,
    sort_by int8 NULL,
    plan_completed_at int8 NULL,
    real_completed_at int8 NULL,
    CONSTRAINT task_stage_pkey PRIMARY KEY (id)
);

-- 公司添加超级管理员（不存在时插入新数据）
INSERT INTO public."role"("name", "type", description, company_id, created_at, updated_at, deleted_at)
SELECT
    '超级管理员',
    2,
    '隐藏角色，拥有部分权限',
    1,
    now(),
    now(),
    NULL
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            public."role"
        WHERE
            "role".company_id = 1
            AND "role"."type" = 2
            AND "role".deleted_at ISNULL);

--	-------------------------------------------------------------------------
--	超级管理员关联用户（不存在时插入新数据）
WITH temp_role AS (
    SELECT
        id
    FROM
        public."role"
    WHERE
        "role".company_id = 1
        AND "role"."type" = 2)
INSERT INTO public."role_user"(role_id, user_id, company_id, created_at, updated_at, deleted_at)
SELECT
    temp_role."id",
    3242583253399296,
    1,
    now(),
    now(),
    NULL
FROM
    temp_role
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            public."role_user"
        WHERE
            "role_user".company_id = 1
            AND "role_user".user_id = 3242583253399296
            AND "role_user".role_id = temp_role."id"
            AND "role_user".deleted_at ISNULL);