CREATE SCHEMA `smsdlvtest` DEFAULT CHARACTER SET utf8mb4;

CREATE TABLE `smsdlv_inbox` (
`ID` varchar(50) NOT NULL,
`FromNo` varchar(50) DEFAULT NULL,
`Message` text,
`RecTime` datetime DEFAULT NULL,
`Route` varchar(255) DEFAULT NULL,
`IMSI` varchar(50) DEFAULT NULL,
`PhoneNo` varchar(50) NULL,
PRIMARY KEY (`ID`),
UNIQUE INDEX `ID_UNIQUE` (`ID` ASC) VISIBLE
) ENGINE=InnoDB DEFAULT charset=utf8mb4;


CREATE TABLE `smsdlv_outbox` (
`ID` varchar(50) NOT NULL,
`ToNo` varchar(50) DEFAULT NULL,
`Subject` varchar(255) DEFAULT NULL,
`Message` text,
`FileAttach` text,
`CreateTime` datetime DEFAULT NULL,
`SendTime` datetime DEFAULT NULL,
`IsPrior` bit(1) DEFAULT b'0',
`IsSent` bit(1) DEFAULT b'0',
`IsRead` bit(1) DEFAULT b'0',
`Route` varchar(255) DEFAULT NULL,
`MMS` bit(1) DEFAULT b'0',
`FlashSMS` bit(1) DEFAULT b'0',
`ReadTag` varchar(50) DEFAULT NULL,
`IMSI` varchar(50) DEFAULT NULL,
`clientTag` varchar(50) DEFAULT NULL,
`sentIndex` int(11) DEFAULT NULL,
`dlv_Status` bit(1) DEFAULT NULL,
`dlv_Time` datetime DEFAULT NULL,
`smpp_refid` varchar(50) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE INDEX `ID_UNIQUE` (`ID` ASC) VISIBLE
) ENGINE=InnoDB DEFAULT charset=utf8mb4;




CREATE TABLE `smsdlv_sim` (
`IMSI` VARCHAR(50) NOT NULL,
`PhoneNo` VARCHAR(50) NULL,
`COMPort` VARCHAR(50) NULL,
`UpdateTime` DATETIME NULL,
PRIMARY KEY (`IMSI`)) ENGINE=InnoDB DEFAULT charset=utf8mb4;

create index idx_readtag ON smsdlv_outbox ( readtag ASC);
create index idx_isread ON smsdlv_outbox ( isread ASC);
create index idx_sendtime ON smsdlv_outbox ( sendtimeASC);
create index idx_isprior ON smsdlv_outbox (isprior ASC);




#improve read speed (optional)
DELIMITER $$
CREATE PROCEDURE `sp_readdata`(
IN pclienttag varchar(50),
IN preadtag varchar(50),
IN prowcount integer)
BEGIN
start transaction;
update smsdlv_outbox set isread=1,readTag=preadtag,clientTag=pclienttag where isread <>1 order by isPrior,createtime limit prowcount ;
select id,tono,subject,message,fileattach,mms,IsPrior,flashsms,route,imsi from smsdlv_outbox where readTag=preadtag order by createtime;
commit;
END$$
DELIMITER ;