-- phpMyAdmin SQL Dump
-- Custom database structure for FlexiPay/Wallet App Integration

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+06:00";

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

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

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phone` varchar(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `pin` varchar(4) NOT NULL,
  `balance` decimal(10,2) NOT NULL DEFAULT '4550.00',
  `cashback_balance` decimal(10,2) NOT NULL DEFAULT '120.00',
  `points` int(11) NOT NULL DEFAULT '1450',
  `is_verified` tinyint(1) NOT NULL DEFAULT '1',
  `account_type` varchar(20) NOT NULL DEFAULT 'Personal',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default user
INSERT INTO `users` (`phone`, `name`, `pin`, `balance`, `cashback_balance`, `points`, `is_verified`, `account_type`) VALUES
('01712345678', 'Rakib Hasan', '1234', 4550.00, 120.00, 1450, 1, 'Personal')
ON DUPLICATE KEY UPDATE `phone`=`phone`;

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

--
-- 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`;

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

--
-- Table structure for table `admin_settings`
--

CREATE TABLE IF NOT EXISTS `admin_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `notice_board` text NOT NULL,
  `bkash_num` varchar(15) NOT NULL,
  `nagad_num` varchar(15) NOT NULL,
  `rocket_num` varchar(15) NOT NULL,
  `upay_num` varchar(15) NOT NULL,
  `successtopup_key` varchar(100) DEFAULT '',
  `successtopup_secret` varchar(100) DEFAULT '',
  `api_base_url` varchar(150) NOT NULL DEFAULT 'https://api.successtopup.com/',
  `is_live` tinyint(1) NOT NULL DEFAULT '0',
  `recharge_api_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `admin_pin` varchar(10) NOT NULL DEFAULT '123456',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed dynamic administrator configuration variables
INSERT INTO `admin_settings` (`id`, `notice_board`, `bkash_num`, `nagad_num`, `rocket_num`, `upay_num`, `successtopup_key`, `successtopup_secret`, `api_base_url`, `is_live`, `recharge_api_enabled`, `admin_pin`) VALUES
(1, 'Welcome to FlexiPay! Enjoy up to 80 Taka flat cashback on bKash Add Money. Special Ramadan Drive offers are active now.', '01823456789', '01987654321', '01555444333', '01312345678', '', '', 'https://api.successtopup.com/', 0, 0, '123456')
ON DUPLICATE KEY UPDATE `id`=`id`;

COMMIT;