Core/Players: Store player creation timestamp and type (normal/npe)

2 files changed, 16 insertions, 1 deletions

diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql
index e1ea55f1a03..637cfc8c1b0 100644
--- a/sql/base/characters_database.sql
+++ b/sql/base/characters_database.sql
@@ -1806,6 +1806,8 @@ CREATE TABLE `characters` (
   `orientation` float NOT NULL DEFAULT '0',
   `taximask` text NOT NULL,
   `online` tinyint(3) unsigned NOT NULL DEFAULT '0',
+  `createTime` bigint(20) NOT NULL DEFAULT '0',
+  `createMode` tinyint(4) NOT NULL DEFAULT '0',
   `cinematic` tinyint(3) unsigned NOT NULL DEFAULT '0',
   `totaltime` int(10) unsigned NOT NULL DEFAULT '0',
   `leveltime` int(10) unsigned NOT NULL DEFAULT '0',
@@ -3718,7 +3720,8 @@ INSERT INTO `updates` VALUES
 ('2021_05_11_00_characters.sql','C3F0337CE8363F970AB4FDB9D23BBB7C650A0B0E','ARCHIVED','2021-05-11 15:39:26',0),
 ('2021_07_04_00_characters.sql','E0E7AD664DDB553E96B457DD9ED8976665E94007','ARCHIVED','2021-07-04 22:23:24',0),
 ('2021_08_11_00_characters.sql','2137A52A45B045104B97D39626CE3C0214625B17','ARCHIVED','2021-08-11 21:48:57',0),
-('2021_08_18_00_characters.sql','5BA1326EE8EC907CDE82E6E8BCB38EA2E661F10A','ARCHIVED','2021-08-18 15:14:17',0);
+('2021_08_18_00_characters.sql','5BA1326EE8EC907CDE82E6E8BCB38EA2E661F10A','ARCHIVED','2021-08-18 15:14:17',0),
+('2021_10_02_00_characters.sql','C6831D3ED03F6BD390A5E5E403FA402D6DC0E95D','RELEASED','2021-10-02 21:21:37',0);
 /*!40000 ALTER TABLE `updates` ENABLE KEYS */;
 UNLOCK TABLES;
 
diff --git a/sql/updates/characters/master/2021_10_02_00_characters.sql b/sql/updates/characters/master/2021_10_02_00_characters.sql
new file mode 100644
index 00000000000..e6e6285b72f
--- /dev/null
+++ b/sql/updates/characters/master/2021_10_02_00_characters.sql
@@ -0,0 +1,12 @@
+ALTER TABLE `characters`
+  ADD `createTime` bigint(20) NOT NULL DEFAULT '0' AFTER `online`,
+  ADD `createMode` tinyint(4) NOT NULL DEFAULT '0' AFTER `createTime`;
+
+ -- AT_LOGIN_FIRST: Characters that have never logged in have accurate creation timestamp in `logout_time`
+UPDATE `characters` SET `createTime`=`logout_time` WHERE (`at_login` & 0x20) != 0;
+ -- attempt to find lowest criteria timestamp for characters that have logged in already
+UPDATE `characters` SET `createTime`=(SELECT COALESCE(MIN(candidates.`date`), UNIX_TIMESTAMP()) FROM (
+SELECT MIN(cap.`date`) `date` FROM `character_achievement_progress` cap WHERE cap.`guid`=`characters`.`guid`
+UNION
+SELECT MIN(ca.`date`) `date` FROM `character_achievement` ca WHERE ca.`guid`=`characters`.`guid`
+) candidates) WHERE (`at_login` & 0x20) = 0;