-- 招聘信息允许招聘项目编号重复
-- 1. 移除 ccdi_staff_recruitment.recruit_id 唯一索引，保留普通查询索引。
-- 2. ccdi_staff_recruitment_work 增加 recruitment_id，改为关联招聘信息自增主键。

SET @schema_name = DATABASE();

SET @drop_unique_sql = (
    SELECT IF(
        COUNT(1) > 0,
        'ALTER TABLE `ccdi_staff_recruitment` DROP INDEX `uk_staff_recruitment_recruit_id`',
        'SELECT 1'
    )
    FROM information_schema.statistics
    WHERE table_schema = @schema_name
      AND table_name = 'ccdi_staff_recruitment'
      AND index_name = 'uk_staff_recruitment_recruit_id'
);
PREPARE stmt FROM @drop_unique_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @add_recruit_id_index_sql = (
    SELECT IF(
        COUNT(1) = 0,
        'ALTER TABLE `ccdi_staff_recruitment` ADD KEY `idx_staff_recruitment_recruit_id` (`recruit_id`)',
        'SELECT 1'
    )
    FROM information_schema.statistics
    WHERE table_schema = @schema_name
      AND table_name = 'ccdi_staff_recruitment'
      AND index_name = 'idx_staff_recruitment_recruit_id'
);
PREPARE stmt FROM @add_recruit_id_index_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @add_work_recruitment_id_sql = (
    SELECT IF(
        COUNT(1) = 0,
        'ALTER TABLE `ccdi_staff_recruitment_work` ADD COLUMN `recruitment_id` BIGINT NULL COMMENT ''关联招聘信息主键ID'' AFTER `id`',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = @schema_name
      AND table_name = 'ccdi_staff_recruitment_work'
      AND column_name = 'recruitment_id'
);
PREPARE stmt FROM @add_work_recruitment_id_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `ccdi_staff_recruitment_work` w
JOIN `ccdi_staff_recruitment` r
  ON w.`recruit_id` COLLATE utf8mb4_general_ci = r.`recruit_id` COLLATE utf8mb4_general_ci
SET w.`recruitment_id` = r.`id`
WHERE w.`recruitment_id` IS NULL;

ALTER TABLE `ccdi_staff_recruitment_work`
    MODIFY COLUMN `recruitment_id` BIGINT NOT NULL COMMENT '关联招聘信息主键ID';

SET @add_work_recruitment_id_index_sql = (
    SELECT IF(
        COUNT(1) = 0,
        'ALTER TABLE `ccdi_staff_recruitment_work` ADD KEY `idx_recruitment_id` (`recruitment_id`)',
        'SELECT 1'
    )
    FROM information_schema.statistics
    WHERE table_schema = @schema_name
      AND table_name = 'ccdi_staff_recruitment_work'
      AND index_name = 'idx_recruitment_id'
);
PREPARE stmt FROM @add_work_recruitment_id_index_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @add_work_recruitment_sort_index_sql = (
    SELECT IF(
        COUNT(1) = 0,
        'ALTER TABLE `ccdi_staff_recruitment_work` ADD KEY `idx_recruitment_id_sort_order` (`recruitment_id`, `sort_order`)',
        'SELECT 1'
    )
    FROM information_schema.statistics
    WHERE table_schema = @schema_name
      AND table_name = 'ccdi_staff_recruitment_work'
      AND index_name = 'idx_recruitment_id_sort_order'
);
PREPARE stmt FROM @add_work_recruitment_sort_index_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;