db schema update

1 files changed, 109 insertions, 84 deletions

diff --git a/db/schema.sql b/db/schema.sql
index 07b216e7..e3cbca3b 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -1,13 +1,13 @@
--- MySQL dump 10.19  Distrib 10.3.30-MariaDB, for debian-linux-gnu (x86_64)
+-- MySQL dump 10.14  Distrib 5.5.68-MariaDB, for Linux (x86_64)
 --
--- Host: 0.0.0.0    Database: coinos
+-- Host: localhost    Database: coinos
 -- ------------------------------------------------------
--- Server version	10.6.1-MariaDB-1:10.6.1+maria~focal
+-- Server version	5.5.68-MariaDB
 
 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
-/*!40101 SET NAMES utf8mb4 */;
+/*!40101 SET NAMES utf8 */;
 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
 /*!40103 SET TIME_ZONE='+00:00' */;
 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
@@ -21,7 +21,7 @@
 
 /*!40000 DROP DATABASE IF EXISTS `coinos`*/;
 
-CREATE DATABASE /*!32312 IF NOT EXISTS*/ `coinos` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
+CREATE DATABASE /*!32312 IF NOT EXISTS*/ `coinos` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
 USE `coinos`;
 
@@ -33,11 +33,11 @@ DROP TABLE IF EXISTS `SequelizeMeta`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `SequelizeMeta` (
-  `name` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL,
+  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   PRIMARY KEY (`name`),
   UNIQUE KEY `name` (`name`),
   UNIQUE KEY `SequelizeMeta_name_unique` (`name`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -59,16 +59,17 @@ CREATE TABLE `accounts` (
   `ticker` varchar(255) DEFAULT NULL,
   `precision` int(11) DEFAULT NULL,
   `domain` varchar(255) DEFAULT NULL,
-  `contract` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
+  `contract` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
+  `index` int(11) NOT NULL DEFAULT '0',
   `pubkey` varchar(255) DEFAULT NULL,
-  `index` int(11) NOT NULL DEFAULT 0,
   `hide` tinyint(1) DEFAULT NULL,
   `seed` varchar(255) DEFAULT NULL,
   `path` varchar(255) DEFAULT NULL,
   `network` varchar(255) DEFAULT NULL,
   `privkey` varchar(255) DEFAULT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=4197 DEFAULT CHARSET=latin1;
+  PRIMARY KEY (`id`),
+  KEY `asset` (`asset`)
+) ENGINE=InnoDB AUTO_INCREMENT=21553 DEFAULT CHARSET=latin1;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -79,10 +80,10 @@ DROP TABLE IF EXISTS `codes`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `codes` (
-  `code` varchar(255) NOT NULL,
-  `text` text DEFAULT NULL,
+  `code` varchar(255) NOT NULL DEFAULT '',
+  `text` text,
   PRIMARY KEY (`code`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -100,8 +101,10 @@ CREATE TABLE `deposits` (
   `amount` double DEFAULT NULL,
   `credited` tinyint(1) DEFAULT NULL,
   `code` varchar(255) DEFAULT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=4122 DEFAULT CHARSET=latin1;
+  PRIMARY KEY (`id`),
+  KEY `deposits_user_id_foreign` (`user_id`),
+  CONSTRAINT `deposits_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4568 DEFAULT CHARSET=latin1;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -114,7 +117,7 @@ DROP TABLE IF EXISTS `invoices`;
 CREATE TABLE `invoices` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `user_id` int(11) DEFAULT NULL,
-  `text` text DEFAULT NULL,
+  `text` text,
   `createdAt` datetime NOT NULL,
   `updatedAt` datetime NOT NULL,
   `rate` double DEFAULT NULL,
@@ -122,16 +125,24 @@ CREATE TABLE `invoices` (
   `address` varchar(255) DEFAULT NULL,
   `received` double DEFAULT NULL,
   `amount` double DEFAULT NULL,
-  `tip` double NOT NULL DEFAULT 0,
+  `tip` double DEFAULT NULL,
   `network` varchar(255) DEFAULT NULL,
   `unconfidential` varchar(255) DEFAULT NULL,
   `uuid` varchar(255) DEFAULT NULL,
-  `memo` text DEFAULT NULL,
+  `memo` text,
   `account_id` int(11) DEFAULT NULL,
   `path` varchar(255) DEFAULT NULL,
-  `webhook` text DEFAULT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=7965 DEFAULT CHARSET=latin1;
+  `webhook` text,
+  PRIMARY KEY (`id`),
+  KEY `part_of_unconfidential` (`unconfidential`(10)),
+  KEY `text_index` (`text`(100)),
+  KEY `unconfidential_index` (`unconfidential`(100)),
+  KEY `address_index` (`address`(100)),
+  KEY `invoices_user_id_foreign` (`user_id`),
+  KEY `invoices_account_id_foreign` (`account_id`),
+  CONSTRAINT `invoices_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
+  CONSTRAINT `invoices_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=46708 DEFAULT CHARSET=latin1;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -148,7 +159,7 @@ CREATE TABLE `linkingkeys` (
   `createdAt` datetime NOT NULL,
   `updatedAt` datetime NOT NULL,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=4671 DEFAULT CHARSET=latin1;
+) ENGINE=InnoDB AUTO_INCREMENT=12617 DEFAULT CHARSET=latin1;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -162,9 +173,9 @@ CREATE TABLE `migrations` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `name` varchar(255) DEFAULT NULL,
   `batch` int(11) DEFAULT NULL,
-  `migration_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `migration_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
+) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -195,13 +206,19 @@ CREATE TABLE `orders` (
   `user_id` int(11) DEFAULT NULL,
   `createdAt` datetime NOT NULL,
   `updatedAt` datetime NOT NULL,
-  `accepted` tinyint(1) NOT NULL DEFAULT 0,
-  `rate` double GENERATED ALWAYS AS (`v2` / `v1`) VIRTUAL,
-  `completedAt` datetime DEFAULT NULL,
+  `accepted` tinyint(1) NOT NULL DEFAULT '0',
   `a1_id` int(11) DEFAULT NULL,
   `a2_id` int(11) DEFAULT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=9316 DEFAULT CHARSET=utf8mb4;
+  `completedAt` datetime DEFAULT NULL,
+  `rate` double DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `orders_user_id_foreign` (`user_id`),
+  KEY `orders_a1_id_foreign` (`a1_id`),
+  KEY `orders_a2_id_foreign` (`a2_id`),
+  CONSTRAINT `orders_a1_id_foreign` FOREIGN KEY (`a1_id`) REFERENCES `accounts` (`id`),
+  CONSTRAINT `orders_a2_id_foreign` FOREIGN KEY (`a2_id`) REFERENCES `accounts` (`id`),
+  CONSTRAINT `orders_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=337 DEFAULT CHARSET=utf8mb4;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -214,7 +231,7 @@ DROP TABLE IF EXISTS `payments`;
 CREATE TABLE `payments` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `user_id` int(11) DEFAULT NULL,
-  `hash` text DEFAULT NULL,
+  `hash` text,
   `createdAt` datetime NOT NULL,
   `updatedAt` datetime NOT NULL,
   `rate` double DEFAULT NULL,
@@ -222,58 +239,63 @@ CREATE TABLE `payments` (
   `address` varchar(255) DEFAULT NULL,
   `received` tinyint(1) DEFAULT NULL,
   `amount` double DEFAULT NULL,
-  `tip` double NOT NULL DEFAULT 0,
+  `tip` double NOT NULL DEFAULT '0',
   `confirmed` tinyint(1) NOT NULL,
-  `fee` double NOT NULL DEFAULT 0,
+  `fee` double NOT NULL DEFAULT '0',
   `network` varchar(255) DEFAULT NULL,
   `account_id` int(11) DEFAULT NULL,
   `preimage` varchar(255) DEFAULT NULL,
-  `memo` text DEFAULT NULL,
+  `memo` text,
+  `redeemed` tinyint(1) NOT NULL DEFAULT '0',
   `redeemcode` varchar(255) DEFAULT NULL,
-  `redeemed` tinyint(1) NOT NULL DEFAULT 0,
   `path` varchar(255) DEFAULT NULL,
   `invoice_id` int(11) DEFAULT NULL,
   PRIMARY KEY (`id`),
+  KEY `idx_redeemcode` (`redeemcode`),
   KEY `payments_invoice_id_foreign` (`invoice_id`),
-  CONSTRAINT `payments_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=40344 DEFAULT CHARSET=latin1;
+  KEY `payments_user_id_foreign` (`user_id`),
+  KEY `payments_account_id_foreign` (`account_id`),
+  CONSTRAINT `payments_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
+  CONSTRAINT `payments_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`),
+  CONSTRAINT `payments_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=29225 DEFAULT CHARSET=latin1;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
--- Table structure for table `referrals`
+-- Table structure for table `prs`
 --
 
-DROP TABLE IF EXISTS `referrals`;
+DROP TABLE IF EXISTS `prs`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `referrals` (
-  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-  `token` varchar(255) DEFAULT NULL,
-  `expiry` date DEFAULT NULL,
-  `status` enum('pending','active','expired','cancelled') DEFAULT NULL,
-  `sponsor_id` int(11) NOT NULL,
-  `user_id` int(11) DEFAULT NULL,
-  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
-  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-  PRIMARY KEY (`id`),
-  UNIQUE KEY `token` (`token`),
-  KEY `sponsor_id` (`sponsor_id`),
-  KEY `user_id` (`user_id`)
-) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
+CREATE TABLE `prs` (
+  `text` text,
+  `preimage` text
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
--- Table structure for table `urls`
+-- Table structure for table `referrals`
 --
 
-DROP TABLE IF EXISTS `urls`;
+DROP TABLE IF EXISTS `referrals`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `urls` (
-  `hash` varchar(255) DEFAULT NULL,
-  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
-  UNIQUE KEY `urls_hash_unique` (`hash`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+CREATE TABLE `referrals` (
+  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `user_id` int(11) DEFAULT NULL,
+  `sponsor_id` int(11) NOT NULL,
+  `token` varchar(255) NOT NULL,
+  `expiry` varchar(255) DEFAULT NULL,
+  `status` enum('available','used','expired','cancelled') NOT NULL DEFAULT 'available',
+  `created_at` datetime DEFAULT NULL,
+  `updated_at` datetime DEFAULT NULL,
+  PRIMARY KEY (`id`),
+  KEY `referrals_user_id_foreign` (`user_id`),
+  KEY `referrals_sponsor_id_foreign` (`sponsor_id`),
+  CONSTRAINT `referrals_sponsor_id_foreign` FOREIGN KEY (`sponsor_id`) REFERENCES `users` (`id`),
+  CONSTRAINT `referrals_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -287,30 +309,30 @@ CREATE TABLE `users` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(255) DEFAULT NULL,
   `password` varchar(255) DEFAULT NULL,
-  `unit` varchar(255) DEFAULT NULL,
-  `account_id` int(11) DEFAULT NULL,
+  `unit` varchar(255) DEFAULT 'SAT',
   `currency` varchar(255) DEFAULT NULL,
   `createdAt` datetime NOT NULL,
   `updatedAt` datetime NOT NULL,
   `twofa` tinyint(1) DEFAULT NULL,
-  `authyId` varchar(255) DEFAULT NULL,
   `pin` varchar(255) DEFAULT NULL,
-  `currencies` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
+  `currencies` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
   `otpsecret` varchar(255) DEFAULT NULL,
+  `account_id` int(11) DEFAULT NULL,
   `ip` int(10) unsigned DEFAULT NULL,
-  `subscriptions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
+  `subscriptions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
   `seed` varchar(255) DEFAULT NULL,
-  `linkingKeys` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
-  `fiat` tinyint(1) NOT NULL DEFAULT 0,
-  `index` int(11) NOT NULL DEFAULT 0,
+  `fiat` tinyint(1) NOT NULL DEFAULT '0',
+  `index` int(11) NOT NULL DEFAULT '0',
   `verified` varchar(255) DEFAULT NULL,
-  `locked` tinyint(1) DEFAULT 0,
+  `locked` tinyint(1) DEFAULT '0',
+  `authyId` varchar(255) DEFAULT NULL,
+  `admin` tinyint(1) DEFAULT NULL,
   `email` varchar(255) DEFAULT NULL,
   `phone` varchar(255) DEFAULT NULL,
-  `admin` tinyint(1) DEFAULT NULL,
   PRIMARY KEY (`id`),
-  KEY `ip` (`ip`)
-) ENGINE=InnoDB AUTO_INCREMENT=797 DEFAULT CHARSET=latin1;
+  KEY `ip` (`ip`),
+  KEY `username` (`username`)
+) ENGINE=InnoDB AUTO_INCREMENT=15377 DEFAULT CHARSET=latin1;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -322,16 +344,17 @@ DROP TABLE IF EXISTS `waiting_list`;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `waiting_list` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-  `email` varchar(255) DEFAULT NULL,
-  `sms` varchar(255) DEFAULT NULL,
+  `email` varchar(255) NOT NULL,
+  `phone` varchar(255) NOT NULL,
+  `status` enum('pending','activated','expired','cancelled') NOT NULL,
   `user_id` int(11) DEFAULT NULL,
-  `status` enum('pending','contacted','registered','cancelled') DEFAULT 'pending',
-  `notes` text DEFAULT NULL,
-  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
-  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `notes` text,
+  `created_at` datetime DEFAULT NULL,
+  `updated_at` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
-  UNIQUE KEY `email` (`email`)
-) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
+  KEY `waiting_list_user_id_foreign` (`user_id`),
+  CONSTRAINT `waiting_list_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
 /*!40101 SET character_set_client = @saved_cs_client */;
 
 --
@@ -351,10 +374,12 @@ CREATE TABLE `withdrawals` (
   `transit` varchar(255) DEFAULT NULL,
   `account` varchar(255) DEFAULT NULL,
   `institution` varchar(255) DEFAULT NULL,
-  `notes` text DEFAULT NULL,
+  `notes` text,
   `email` varchar(255) DEFAULT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=4113 DEFAULT CHARSET=latin1;
+  PRIMARY KEY (`id`),
+  KEY `withdrawals_user_id_foreign` (`user_id`),
+  CONSTRAINT `withdrawals_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
+) ENGINE=InnoDB AUTO_INCREMENT=4119 DEFAULT CHARSET=latin1;
 /*!40101 SET character_set_client = @saved_cs_client */;
 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
@@ -366,4 +391,4 @@ CREATE TABLE `withdrawals` (
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2021-07-13 14:05:34
+-- Dump completed on 2021-08-12 16:26:08