-- Custom database structure for FlexiPay/Wallet App Integration

START TRANSACTION;

CREATE DATABASE IF NOT EXISTS `flexipay_db`;
USE `flexipay_db`;

-- --------------------------------------------------------

--
-- Table structure for table `transactions`
--

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` varchar(50) NOT NULL,
  `user_phone` varchar(11) NOT NULL,
  `type` varchar(30) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `fee` decimal(10,2) NOT NULL DEFAULT '0.00',
  `status` varchar(20) NOT NULL,
  `trxId` varchar(50) NOT NULL,
  `reference_phone` varchar(20) NOT NULL,
  `carrier_or_provider` varchar(50) NOT NULL,
  `timestamp` bigint(20) NOT NULL,
  `note` varchar(255) DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `trxId` (`trxId`),
  KEY `user_phone` (`user_phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default transaction logs
INSERT INTO `transactions` (`id`, `user_phone`, `type`, `amount`, `fee`, `status`, `trxId`, `reference_phone`, `carrier_or_provider`, `timestamp`, `note`) VALUES
('tx_1', '01712345678', 'ADD_MONEY', 2000.00, 0.00, 'SUCCESS', 'bK8X9Y7W10M', '01823456789', 'bKash', 1781530798000, 'Added from admin wallet bKash'),
('tx_2', '01712345678', 'CASHBACK', 30.00, 0.00, 'SUCCESS', 'CBW90X121Z', '01712345678', 'FlexiPay Promo', 1781530799000, 'Promo cashback on bKash Add Money'),
('tx_3', '01712345678', 'RECHARGE', 149.00, 0.00, 'SUCCESS', 'GP902KLO891', '01700998877', 'Grameenphone', 1781531798000, 'Prepaid Flexiload successful')
ON DUPLICATE KEY UPDATE `id`=`id`;

COMMIT;
