CREATE TABLE `tbl_order_quotation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) DEFAULT NULL,
  `price` double NOT NULL,
  `quantity` int(11) NOT NULL,
  `quantity_unit` int(11) DEFAULT NULL,
  `total_price` double NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `type` varchar(55) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `destination_port` varchar(255) DEFAULT NULL,
  `inspection` int(11) DEFAULT NULL,
  `delivery_date` timestamp NULL DEFAULT NULL,
  `remark` text,
  `file` varchar(255) DEFAULT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `quantity_unit` (`quantity_unit`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_order_quotation_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `se_product` (`id`),
  CONSTRAINT `tbl_order_quotation_ibfk_2` FOREIGN KEY (`quantity_unit`) REFERENCES `tbl_unit_type` (`id`),
  CONSTRAINT `tbl_order_quotation_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1

CREATE TABLE `tbl_transaction` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `txn_amount` double(30,2) NOT NULL,
  `order_id` bigint(20) NOT NULL,
  `txn_type` varchar(55) NOT NULL,
  `txn_date` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  `product` varchar(55) NOT NULL,
  `user_ip` varbinary(16) NOT NULL,
  `status` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tbl_transaction_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `tbl_order_quotation` (`id`),
  CONSTRAINT `tbl_transaction_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1




CREATE TABLE `tbl_ipay_txn` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `txn_id` bigint(20) NOT NULL,
  `paymentid` varchar(255) NOT NULL,
  `refno` varchar(255) NOT NULL,
  `amount` double(20,6) NOT NULL,
  `ecurrency` varchar(55) NOT NULL,
  `prod_desc` varchar(255) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `user_contact` int(11) DEFAULT NULL,
  `remark` varchar(255) NOT NULL,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` int(11) NOT NULL,
  `errdesc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `txn_id` (`txn_id`),
  CONSTRAINT `tbl_ipay_txn_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_user` (`id`),
  CONSTRAINT `tbl_ipay_txn_ibfk_2` FOREIGN KEY (`txn_id`) REFERENCES `tbl_transaction` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

