-- OnboardX 契约锁公司级业务分类与印章配置
-- 适用数据库：MySQL 8.0+
-- 可重复执行；仅增加字段，不覆盖任何公司已有配置。

SET NAMES utf8mb4;

DROP PROCEDURE IF EXISTS `ob_qys_company_add_column_if_missing`;
DELIMITER $$
CREATE PROCEDURE `ob_qys_company_add_column_if_missing`(
    IN p_column_name VARCHAR(128),
    IN p_column_definition VARCHAR(1000)
)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'ob_company'
          AND COLUMN_NAME = p_column_name
    ) THEN
        SET @ddl = CONCAT(
            'ALTER TABLE `ob_company` ADD COLUMN `',
            p_column_name, '` ', p_column_definition
        );
        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$
DELIMITER ;

CALL `ob_qys_company_add_column_if_missing`(
    'QiyuesuoCategoryId',
    'VARCHAR(100) NULL COMMENT ''契约锁默认业务分类Id（按公司独立配置）'' AFTER `ContactPhone`'
);
CALL `ob_qys_company_add_column_if_missing`(
    'QiyuesuoSealId',
    'VARCHAR(100) NULL COMMENT ''契约锁企业印章Id（按公司独立配置）'' AFTER `QiyuesuoCategoryId`'
);

DROP PROCEDURE IF EXISTS `ob_qys_company_add_column_if_missing`;

SELECT
    Id,
    CompanyCode,
    CompanyName,
    LegalName,
    QiyuesuoCategoryId,
    QiyuesuoSealId,
    CASE
        WHEN NULLIF(TRIM(QiyuesuoCategoryId), '') IS NOT NULL
         AND NULLIF(TRIM(QiyuesuoSealId), '') IS NOT NULL THEN '已配置'
        ELSE '待配置'
    END AS QiyuesuoConfigStatus
FROM ob_company
WHERE IsDelete = 0 OR IsDelete IS NULL
ORDER BY CompanyName;