--雪花配置
--DROP TABLE SNOWFLAKE_CONFIG;
CREATE TABLE SNOWFLAKE_CONFIG (  
  DATA_CENTER_ID NUMBER(5) NOT NULL,  
  WORKER_ID NUMBER(5) NOT NULL,  
  LAST_TIMESTAMP NUMBER(20) NOT NULL,  
  SEQUENCE NUMBER(12) DEFAULT 0 NOT NULL,  
  CONSTRAINT PK_SNOWFLAKE_CONFIG PRIMARY KEY (DATA_CENTER_ID, WORKER_ID)  
);  
COMMENT ON TABLE SNOWFLAKE_CONFIG IS '雪花配置';
COMMENT ON COLUMN SNOWFLAKE_CONFIG.DATA_CENTER_ID IS '数据中心ID';
COMMENT ON COLUMN SNOWFLAKE_CONFIG.WORKER_ID IS '工作序列';
COMMENT ON COLUMN SNOWFLAKE_CONFIG.LAST_TIMESTAMP IS '上次生成的时间戳';
COMMENT ON COLUMN SNOWFLAKE_CONFIG.SEQUENCE IS '存储序列号';

--给雪花配置初始化
insert into SNOWFLAKE_CONFIG VALUES(1,1, TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS')),1);
-- 自定义按位或函数
CREATE OR REPLACE FUNCTION CUSTOM_BITWISE_OR(A NUMBER, B NUMBER) RETURN NUMBER IS
    RESULT NUMBER := 0;
    BIT_A NUMBER;
    BIT_B NUMBER;
    I NUMBER;
BEGIN
    -- 循环处理每一位
    FOR I IN 0..63 LOOP
        -- 提取 A 的第 I 位
        BIT_A := MOD(FLOOR(A / POWER(2, I)), 2);
        -- 提取 B 的第 I 位
        BIT_B := MOD(FLOOR(B / POWER(2, I)), 2);
        -- 如果至少有一个位为 1，则将该位对应的 2 的幂次方加到结果中
        RESULT := RESULT + 
                  CASE 
                      WHEN (BIT_A + BIT_B) > 0 THEN 1
                      ELSE 0
                  END * POWER(2, I);
    END LOOP;
    RETURN RESULT;
END;
/

CREATE OR REPLACE PROCEDURE GENERATE_SNOWFLAKE_ID (  
  P_DATA_CENTER_ID IN NUMBER,  
  P_WORKER_ID IN NUMBER,  
  P_ID OUT NUMBER  
) IS  
  V_LAST_TIMESTAMP NUMBER(20);  
  V_SEQUENCE NUMBER(12);  
  V_CURRENT_TIMESTAMP NUMBER(20);  
  V_TIMESTAMP_DIFF NUMBER(20);  
BEGIN  
  -- 获取当前时间戳  
  V_CURRENT_TIMESTAMP := TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISS'));  
  
  -- 锁定配置表  
  SELECT LAST_TIMESTAMP, SEQUENCE INTO V_LAST_TIMESTAMP, V_SEQUENCE  
  FROM SNOWFLAKE_CONFIG  
  WHERE DATA_CENTER_ID = P_DATA_CENTER_ID AND WORKER_ID = P_WORKER_ID  
  FOR UPDATE;  
  
  -- 时间戳比较  
  V_TIMESTAMP_DIFF := V_CURRENT_TIMESTAMP - V_LAST_TIMESTAMP;  
  IF V_TIMESTAMP_DIFF < 0 THEN  
    -- 时钟回拨处理  
    RAISE_APPLICATION_ERROR(-20001, 'CLOCK MOVED BACKWARDS');  
  ELSIF V_TIMESTAMP_DIFF = 0 THEN  
    -- 同一毫秒内，序列号自增  
    V_SEQUENCE := V_SEQUENCE + 1;  
    IF V_SEQUENCE > 4095 THEN  
      -- 序列号溢出，等待下一毫秒  
      LOOP  
        V_CURRENT_TIMESTAMP := EXTRACT(SECOND FROM SYSTIMESTAMP) * 1000;  
        V_TIMESTAMP_DIFF := V_CURRENT_TIMESTAMP - V_LAST_TIMESTAMP;  
        EXIT WHEN V_TIMESTAMP_DIFF > 0;  
      END LOOP;  
      V_SEQUENCE := 0;  
    END IF;  
  ELSE  
    -- 新的毫秒，重置序列号  
    V_SEQUENCE := 0;  
  END IF;  
  
  -- 更新配置表  
  UPDATE SNOWFLAKE_CONFIG  
  SET LAST_TIMESTAMP = V_CURRENT_TIMESTAMP, SEQUENCE = V_SEQUENCE  
  WHERE DATA_CENTER_ID = P_DATA_CENTER_ID AND WORKER_ID = P_WORKER_ID;  
  
  -- 生成 ID，使用自定义按位或函数
  P_ID := CUSTOM_BITWISE_OR(
      CUSTOM_BITWISE_OR(
          CUSTOM_BITWISE_OR(
              V_CURRENT_TIMESTAMP * POWER(2, 22),
              P_DATA_CENTER_ID * POWER(2, 17)
          ),
          P_WORKER_ID * POWER(2, 12)
      ),
      V_SEQUENCE
  );
END;
/

--调用存储过程，默认添加数据是雪花算法主键字段

-- 创建触发器 PERSON
CREATE OR REPLACE TRIGGER TRG_PERSON_SNOWFLAKE_ID
BEFORE INSERT ON PERSON
FOR EACH ROW
DECLARE
    V_DATA_CENTER_ID NUMBER := 1; -- 数据中心 ID，可根据实际情况修改
    V_WORKER_ID NUMBER := 1; -- 机器 ID，可根据实际情况修改
    V_GENERATED_ID NUMBER;
BEGIN
    -- 调用生成雪花 ID 的存储过程
    GENERATE_SNOWFLAKE_ID(V_DATA_CENTER_ID, V_WORKER_ID, V_GENERATED_ID);
    -- 将生成的 ID 赋值给插入行的 ID 列
    :NEW.UUID := V_GENERATED_ID;
END;
/