--
-- Host: localhost    Database: 504db
-- ------------------------------------------------------


--
-- Table structure for table `balance`
--

CREATE TABLE `balance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `amount` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `balance`
--

INSERT INTO `balance` VALUES (1,0),(2,7709);

--
-- Table structure for table `ci_admin_roles`
--

CREATE TABLE `ci_admin_roles` (
  `admin_role_id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_role_title` varchar(30) CHARACTER SET utf8 NOT NULL,
  `admin_role_status` int(11) NOT NULL,
  `admin_role_created_by` int(1) NOT NULL,
  `admin_role_created_on` datetime NOT NULL,
  `admin_role_modified_by` int(11) NOT NULL,
  `admin_role_modified_on` datetime NOT NULL,
  PRIMARY KEY (`admin_role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ci_admin_roles`
--

INSERT INTO `ci_admin_roles` VALUES (1,'Super Admin',1,0,'2018-03-15 12:48:04',0,'2018-03-17 12:53:16'),(2,'Admin',1,0,'2018-03-15 12:53:19',0,'2019-01-26 08:27:34'),(5,'Sync role',1,0,'2021-04-04 11:38:22',0,'2021-04-04 10:55:46');

--
-- Table structure for table `missing_payment`
--

CREATE TABLE `missing_payment` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `fileid` bigint(20) NOT NULL,
  `TransactionType` varchar(40) NOT NULL,
  `TransID` varchar(40) NOT NULL,
  `TransTime` varchar(40) NOT NULL,
  `TransAmount` varchar(40) NOT NULL,
  `BusinessShortCode` varchar(40) NOT NULL,
  `BillRefNumber` varchar(40) NOT NULL,
  `InvoiceNumber` varchar(40) NOT NULL,
  `ThirdPartyTransID` varchar(40) NOT NULL,
  `MSISDN` varchar(100) DEFAULT NULL,
  `FirstName` varchar(60) NOT NULL,
  `MiddleName` varchar(60) DEFAULT NULL,
  `LastName` varchar(60) DEFAULT NULL,
  `OrgAccountBalance` double NOT NULL,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `missing_payment`
--


--
-- Table structure for table `module`
--

CREATE TABLE `module` (
  `module_id` int(11) NOT NULL AUTO_INCREMENT,
  `module_name` varchar(255) NOT NULL,
  `controller_name` varchar(255) NOT NULL,
  `fa_icon` varchar(100) NOT NULL,
  `operation` text NOT NULL,
  `sort_order` tinyint(4) NOT NULL,
  PRIMARY KEY (`module_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `module`
--

INSERT INTO `module` VALUES (1,'Admin List','admin','','view|add|edit|delete|change_status|access',0),(2,'Role & Permissions','admin_roles','','view|add|edit|delete|change_status|access',0),(7,'Export','export','','access',0),(8,'General Settings','general_settings','','view|add|edit|access',0),(9,'Sales','sales','','view|add|failed|export_csv|export_failed_csv|access',0),(10,'Payments','payments','','view|sync|export_csv|access',0),(11,'Customers','customers','','view|export_csv|access',0),(12,'Reports','reports','','view|access',0),(13,'Dashboard Summary','summary','','access',0);

--
-- Table structure for table `module_access`
--

CREATE TABLE `module_access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `admin_role_id` int(11) NOT NULL,
  `module` varchar(255) NOT NULL,
  `operation` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `RoleId` (`admin_role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `module_access`
--

INSERT INTO `module_access` VALUES (1,1,'users','view'),(2,1,'users','add'),(3,1,'users','edit'),(5,1,'users','access'),(6,1,'users','change_status'),(7,1,'export','access'),(8,1,'general_settings','view'),(9,1,'general_settings','add'),(10,1,'general_settings','edit'),(11,1,'general_settings','access'),(12,2,'admin','view'),(13,2,'admin','add'),(14,2,'admin','edit'),(15,2,'admin','change_status'),(16,2,'admin_roles','view'),(18,2,'general_settings','view'),(19,2,'sales','view'),(20,2,'sales','failed'),(21,2,'payments','view'),(22,2,'payments','sync'),(23,2,'customers','view'),(24,2,'customers','export_csv'),(25,2,'sales','access'),(26,2,'general_settings','access'),(27,2,'admin_roles','access'),(28,2,'admin','access'),(30,2,'admin_roles','change_status'),(32,2,'customers','index'),(33,2,'sales','index'),(34,2,'payments','access'),(35,2,'customers','access'),(37,2,'payments','index'),(38,2,'admin_roles','add'),(39,5,'payments','access'),(41,5,'payments','access'),(44,2,'customers','view'),(45,2,'customers','export_csv'),(46,2,'customers','view'),(47,2,'customers','export_csv'),(48,2,'reports','view'),(49,2,'reports','access'),(50,2,'sales','view'),(53,2,'admin','delete'),(56,2,'reports','access'),(63,2,'summary','access'),(64,5,'sales','view'),(65,5,'sales','failed'),(66,5,'payments','view'),(67,5,'sales','access'),(70,2,'general_settings','edit'),(71,2,'general_settings','add');

--
-- Table structure for table `payment`
--

CREATE TABLE `payment` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `TransactionType` varchar(40) NOT NULL,
  `TransID` varchar(40) NOT NULL,
  `TransTime` varchar(40) NOT NULL,
  `TransAmount` varchar(40) NOT NULL,
  `BusinessShortCode` varchar(40) NOT NULL,
  `BillRefNumber` varchar(40) NOT NULL,
  `InvoiceNumber` varchar(40) NOT NULL,
  `ThirdPartyTransID` varchar(40) NOT NULL,
  `MSISDN` varchar(100) DEFAULT NULL,
  `FirstName` varchar(60) NOT NULL,
  `MiddleName` varchar(60) DEFAULT NULL,
  `LastName` varchar(60) DEFAULT NULL,
  `OrgAccountBalance` double NOT NULL,
  `payment_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `transid_index` (`TransID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `payment`
--

INSERT INTO `payment` VALUES (1,'Pay Bill','TES1JV3W9H','20250528163502','5.00','504604','0718000659','','1','119b898d2e96275cbe2157 ***** 8101db035c95d625e712b3a573882012dbd5a','ALMODAD','','',77,'2025-05-28 13:35:04');

--
-- Table structure for table `payment_tmp`
--

CREATE TABLE `payment_tmp` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `TransactionType` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `TransID` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `TransTime` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `TransAmount` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `BusinessShortCode` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `BillRefNumber` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `InvoiceNumber` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `ThirdPartyTransID` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `MSISDN` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `FirstName` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `MiddleName` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `LastName` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `OrgAccountBalance` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `payment_tmp`
--


--
-- Table structure for table `request_tmp`
--

CREATE TABLE `request_tmp` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sender` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
  `amount` int(10) NOT NULL,
  `mpesa_number` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` int(5) DEFAULT '1',
  `transaction_id` int(20) DEFAULT '0',
  `ref` varchar(20) COLLATE utf8_unicode_ci DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `request_tmp`
--


--
-- Table structure for table `resendq`
--

CREATE TABLE `resendq` (
  `id` bigint(15) NOT NULL AUTO_INCREMENT,
  `status` int(10) NOT NULL DEFAULT '1',
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `resendq`
--


--
-- Table structure for table `transaction`
--

CREATE TABLE `transaction` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `payment_id` bigint(20) NOT NULL,
  `pretups_id` varchar(200) NOT NULL DEFAULT '',
  `amount` int(20) NOT NULL,
  `customer_message` varchar(255) NOT NULL DEFAULT '',
  `status` int(10) DEFAULT '0',
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `channel` varchar(20) NOT NULL DEFAULT 'PAYBILL',
  `network` varchar(20) DEFAULT 'Airtel',
  `resend_status` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_transid` (`id`),
  KEY `idx_payment_id` (`payment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `transaction`
--

INSERT INTO `transaction` VALUES (1,1,'',5,'R250528.1635.210003 confirmed. You have topped up 718000659 with Ksh. 5. New balance is Ksh. 7709.55. ',200,'2025-05-28 13:35:04','PAYBILL','SAFCOM',0);

--
-- Table structure for table `uploads`
--

CREATE TABLE `uploads` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `filepath` text,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `uploads`
--



