CREATE DATABASE /*!32312 IF NOT EXISTS*/ `paymentaccount_mb` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_bin */;

USE `paymentaccount_mb`;

--
-- Sequence structure for `counter_sequence`
--

DROP SEQUENCE IF EXISTS `counter_sequence`;
CREATE SEQUENCE `counter_sequence` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB;
DO SETVAL(`counter_sequence`, 1001, 0);

--
-- Table structure for table `account`
--

DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `account` (
  `id` binary(16) NOT NULL DEFAULT unhex(replace(uuid(),'-','')),
  `currency_code` varchar(3) NOT NULL,
  `balance` decimal(13,2) NOT NULL,
  `account_type` enum('TRANSITION','SUPPLIER','CPS','SALES_REPRESENTATIVE','OPERATOR','TRANSITION_OPERATOR','SUPPLIER_OPERATOR','CPS_OPERATOR','THUNES') NOT NULL,
  `operator` varchar(20) DEFAULT NULL,
  `sales_representative` varchar(10) DEFAULT NULL,
  `supplier` varchar(10) DEFAULT NULL,
  `counter` int(11) DEFAULT nextval(`paymentaccount_mb`.`counter_sequence`),
  `year_of_creation` int(11) NOT NULL DEFAULT year(curdate()),
  `account_number` varchar(14) GENERATED ALWAYS AS (concat(`year_of_creation`,`counter`)) VIRTUAL,
  `start_date` date NOT NULL DEFAULT curdate(),
  `end_date` date DEFAULT NULL CHECK (`end_date` is null or `start_date` <= `end_date`),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `kyc_supplier` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES `account` WRITE;
/*!40000 ALTER TABLE `account` DISABLE KEYS */;
INSERT INTO `account` VALUES
(0x483C73714AB511F08A09005056BA82FA,'DKK',12355.10,'TRANSITION',NULL,NULL,NULL,1,2025,'20251','2025-06-16',NULL,'2025-06-16 15:25:15',NULL,'suurkula',NULL),
(0x4840B0804AB511F08A09005056BA82FA,'EUR',729025.76,'TRANSITION',NULL,NULL,NULL,2,2025,'20252','2025-06-16',NULL,'2025-06-16 15:25:15',NULL,'suurkula',NULL),
(0x48430FC94AB511F08A09005056BA82FA,'GBP',2761.53,'TRANSITION',NULL,NULL,NULL,3,2025,'20253','2025-06-16',NULL,'2025-06-16 15:25:15',NULL,'suurkula',NULL),
(0x4845D73B4AB511F08A09005056BA82FA,'USD',2814500.00,'TRANSITION',NULL,NULL,NULL,4,2025,'20254','2025-06-16',NULL,'2025-06-16 15:25:15',NULL,'suurkula',NULL),
(0x4848D02B4AB511F08A09005056BA82FA,'EUR',64695.30,'SUPPLIER',NULL,NULL,'ABCHOUSE1',5,2025,'20255','2025-06-16',NULL,'2025-06-16 15:25:15',NULL,'suurkula','LXABCCONCE');


UNLOCK TABLES;
