#######################################################################################################
#Error table 
CREATE TABLE `system_sd_sync` (
  `cmd_id` varchar(27) COLLATE utf8mb4_bin NOT NULL,
  `cmd` char(1) COLLATE utf8mb4_bin NOT NULL,
  `sync_process` int(1) NOT NULL DEFAULT '0',
  `sync_status` int(1) NOT NULL DEFAULT '0',
  `sync_date` datetime DEFAULT NULL,
  `schedule_made` int(1) NOT NULL,
  `schedule_id` varchar(27) COLLATE utf8mb4_bin DEFAULT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `wholeday` int(1) NOT NULL DEFAULT '0',
  `repeat` int(1) NOT NULL DEFAULT '0',
  `schedule_public` int(1) NOT NULL DEFAULT '0',
  `title` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL,
  `place` varchar(200) COLLATE utf8mb4_bin DEFAULT NULL,
  `contents` longtext COLLATE utf8mb4_bin,
  `register_id` varchar(100) COLLATE utf8mb4_bin NOT NULL,
  `participant` longtext COLLATE utf8mb4_bin,
  `owa_id` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL,
  `sync_register` int(1) DEFAULT '0',
  `sync_regist_date` datetime DEFAULT NULL,
  `sync_updater` int(1) DEFAULT '0',
  `sync_update_date` datetime DEFAULT NULL,
  `schedule_group_id` varchar(27) COLLATE utf8mb4_bin DEFAULT NULL,
  `sync_id` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `ical_uid` varchar(300) COLLATE utf8mb4_bin DEFAULT NULL,
  `repeat_type` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  `repeat_period` varchar(4) COLLATE utf8mb4_bin DEFAULT NULL,
  `repeat_period_option` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  `repeat_start_date` datetime DEFAULT NULL,
  `repeat_end_date` datetime DEFAULT NULL,
  `original_start` datetime DEFAULT NULL,
  `repeat_master_fg` varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
  `category_id` varchar(27) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`cmd_id`),
  KEY `IDX1_SD_SYNC` (`schedule_id`,`cmd`,`sync_updater`),
  KEY `IDX2_SD_SYNC` (`sync_status`,`sync_register`),
  KEY `IDX3_SD_SYNC` (`register_id`),
  KEY `IDX4_IKEP4_SD_SYNC` (`sync_id`,`sync_process`,`sync_register`),
  KEY `IDX7_SD_SYNC` (`sync_id`,`sync_status`),
  KEY `IDX8_SD_SYNC` (`owa_id`,`sync_id`),
  KEY `IDX9_SD_SYNC` (`ical_uid`,`sync_id`),
  KEY `IDX10_SD_SYNC` (`sync_process`,`schedule_made`,`sync_status`,`register_id`),
  KEY `IDX11_SD_SYNC` (`sync_status`,`repeat_master_fg`,`sync_process`,`schedule_made`,`repeat`),
  KEY `IDX12_SD_SYNC` (`sync_regist_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;




###################################################################################################################
#sequnce table and function

CREATE TABLE `system_sequence` (
  `SEQ_NAME` varchar(50) COLLATE utf8mb4_bin NOT NULL COMMENT '½ÃÄö½º¸í',
  `LAST_CACHE_VAL` decimal(27,0) NOT NULL COMMENT 'ÇöÀç½ÃÄö½º°ª',
  `YEARMONTH_FLAG` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
  `PREFIX` varchar(3) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`SEQ_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='½ÃÄö½ºÅ×À̺í';


DROP FUNCTION IF EXISTS dbname.FN_NEXT_VAL;
CREATE FUNCTION dbname.`FN_NEXT_VAL`(`p_seq_name` VARCHAR(50)) RETURNS varchar(20) CHARSET utf8 COLLATE utf8_bin
    MODIFIES SQL DATA
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
   DECLARE V_RETURN_SEQ   varchar(15);

   SELECT YEARMONTH_FLAG, PREFIX, DATE_FORMAT(NOW(), '%y%m')
     INTO @YEARMONTH_FLAG, @PERFIX, @NOW
     FROM system_sequence
    WHERE seq_name = upper(p_seq_name);


   UPDATE system_sequence
      SET last_cache_val = (@v_current_value := last_cache_val + 1)
    WHERE seq_name = upper(p_seq_name);

   IF @PERFIX IS NULL
   THEN
      SET V_RETURN_SEQ = @v_current_value;
   ELSE
      IF @YEARMONTH_FLAG = 'Y'
      THEN
         SET V_RETURN_SEQ = @PERFIX || @NOW || LPAD(@v_current_value, 8, '0');
      ELSE
       --  SET V_RETURN_SEQ = @PERFIX || LPAD(@v_current_value, 8, '0');
         SET V_RETURN_SEQ = @PERFIX || @v_current_value;
      END IF;
   END IF;

   RETURN V_RETURN_SEQ;
END;