-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Nov 27, 2023 at 04:08 AM
-- Server version: 8.0.32
-- PHP Version: 8.2.0

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


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `emedical_jovast`
--

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

--
-- Table structure for table `billing_exemptions`
--

DROP TABLE IF EXISTS `billing_exemptions`;
CREATE TABLE IF NOT EXISTS `billing_exemptions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type_id` int NOT NULL,
  `subtype` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `subtype_id` int NOT NULL,
  `parent` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_id` int NOT NULL,
  `reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `personnel` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `personnel_id` int NOT NULL,
  `store_id` int NOT NULL,
  `code` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `birthdate` date NOT NULL,
  `sex` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pricelist` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pricelist_id` int NOT NULL,
  `insurance` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `insurance_id` int NOT NULL,
  `scheme` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `scheme_id` int NOT NULL,
  `product` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_id` int NOT NULL,
  `card_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorization_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `supplementary` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `folio_id` int NOT NULL,
  `folio_no` int NOT NULL,
  `year` year NOT NULL,
  `month` int NOT NULL,
  `date` date NOT NULL,
  `visit_id` int NOT NULL,
  `visit_location_id` int NOT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `urgency` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `urgency_code` int NOT NULL,
  `progress` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_code` int NOT NULL,
  `progress_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_user_id` int NOT NULL,
  `progress_datetime` datetime NOT NULL,
  `attachments` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `items` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `cost` decimal(15,2) NOT NULL,
  `subtotal` decimal(15,2) NOT NULL,
  `tax` decimal(15,2) NOT NULL,
  `shipping` decimal(15,2) NOT NULL,
  `discount` decimal(15,2) NOT NULL,
  `exemption` decimal(15,2) NOT NULL,
  `promise` decimal(15,2) NOT NULL,
  `payable` decimal(15,2) NOT NULL,
  `paid` decimal(15,2) NOT NULL,
  `due` decimal(15,2) NOT NULL,
  `requested` int NOT NULL,
  `requested_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `requested_user_id` int NOT NULL,
  `requested_datetime` datetime NOT NULL,
  `authorized` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorized_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorized_user_id` int NOT NULL,
  `authorized_datetime` datetime NOT NULL,
  `approved` int NOT NULL,
  `approved_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `approved_user_id` int NOT NULL,
  `approved_datetime` datetime NOT NULL,
  `transferred` int NOT NULL,
  `transferred_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `transferred_user_id` int NOT NULL,
  `transferred_datetime` datetime NOT NULL,
  `received` int NOT NULL,
  `received_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `received_user_id` int NOT NULL,
  `received_datetime` datetime NOT NULL,
  `confirmed` int NOT NULL,
  `confirmed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `confirmed_user_id` int NOT NULL,
  `confirmed_datetime` datetime NOT NULL,
  `billed` int NOT NULL,
  `billed_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_user_id` int NOT NULL,
  `billed_datetime` datetime NOT NULL,
  `payment` int NOT NULL,
  `payment_method` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_channel` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_transaction` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_receipt` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_phone` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_user_id` int NOT NULL,
  `payment_datetime` datetime NOT NULL,
  `forwarded` int NOT NULL,
  `forwarded_to` int NOT NULL,
  `forwarded_id` int NOT NULL,
  `forwarded_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `forwarded_user_id` int NOT NULL,
  `forwarded_datetime` datetime NOT NULL,
  `dispensed` int NOT NULL,
  `dispensed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dispensed_user_id` int NOT NULL,
  `dispensed_datetime` datetime NOT NULL,
  `returned` int NOT NULL,
  `returned_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `returned_user_id` int NOT NULL,
  `returned_datetime` datetime NOT NULL,
  `printed` int NOT NULL,
  `printed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `printed_user_id` int NOT NULL,
  `printed_datetime` datetime NOT NULL,
  `f1` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f2` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f3` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f4` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f5` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f6` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f7` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f8` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f9` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_user_id` int NOT NULL,
  `created_datetime` datetime NOT NULL,
  `modified_user_id` int NOT NULL,
  `modified_datetime` datetime NOT NULL,
  `status` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `code` (`code`),
  KEY `subtype` (`subtype`),
  KEY `parent` (`parent`),
  KEY `reference_code` (`reference_no`),
  KEY `personnel_id` (`personnel_id`),
  KEY `name` (`name`),
  KEY `insurance_id` (`insurance_id`),
  KEY `scheme_id` (`scheme_id`),
  KEY `urgency_code` (`urgency_code`),
  KEY `progress_code` (`progress_code`),
  KEY `requested` (`requested`),
  KEY `authorized` (`authorized`),
  KEY `approved` (`approved`),
  KEY `confirmed` (`confirmed`),
  KEY `transfered` (`transferred`),
  KEY `printed` (`printed`),
  KEY `created_user_id` (`created_user_id`),
  KEY `created_datetime` (`created_datetime`),
  KEY `modified_user_id` (`modified_user_id`),
  KEY `modified_datetime` (`modified_datetime`),
  KEY `status` (`status`),
  KEY `store_id` (`store_id`),
  KEY `parent_id` (`parent_id`) USING BTREE,
  KEY `forwarded` (`forwarded`),
  KEY `forwarded_to` (`forwarded_to`),
  KEY `billed` (`billed`),
  KEY `forwarded_id` (`forwarded_id`),
  KEY `requested_datetime` (`requested_datetime`),
  KEY `billed_datetime` (`billed_datetime`),
  KEY `payment` (`payment`),
  KEY `payment_method` (`payment_method`),
  KEY `payment_channel` (`payment_channel`),
  KEY `payment_account` (`payment_account`),
  KEY `payment_account_id` (`payment_account_id`),
  KEY `payment_user_id` (`payment_user_id`),
  KEY `payment_reference` (`payment_reference`),
  KEY `payment_datetime` (`payment_datetime`),
  KEY `card_no` (`card_no`),
  KEY `authorization_no` (`authorization_no`),
  KEY `folio_id` (`folio_id`),
  KEY `folio_no` (`folio_no`),
  KEY `year` (`year`),
  KEY `month` (`month`),
  KEY `visit_id` (`visit_id`),
  KEY `pricelist_id` (`pricelist_id`),
  KEY `date` (`date`),
  KEY `visit_location_id` (`visit_location_id`),
  KEY `insurance` (`insurance`),
  KEY `scheme` (`scheme`),
  KEY `dispensed` (`dispensed`),
  KEY `returned` (`returned`),
  KEY `subtype_id` (`subtype_id`),
  KEY `type_id` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contains exemptions';

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

--
-- Table structure for table `billing_exemption_items`
--

DROP TABLE IF EXISTS `billing_exemption_items`;
CREATE TABLE IF NOT EXISTS `billing_exemption_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type_id` int NOT NULL,
  `subtype` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `subtype_id` int NOT NULL,
  `subtype_item_id` int NOT NULL,
  `parent` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_id` int NOT NULL,
  `insurance_id` int NOT NULL,
  `account_id` int NOT NULL,
  `scheme_id` int NOT NULL,
  `folio_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorization_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `approval_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `store_id` int NOT NULL,
  `item_id` int NOT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `details` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `qty` int NOT NULL,
  `uom` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` int NOT NULL,
  `pack` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `size` int NOT NULL,
  `balance` int NOT NULL,
  `batch` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiry` date NOT NULL,
  `progress` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_code` int NOT NULL,
  `progress_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_user_id` int NOT NULL,
  `progress_datetime` datetime NOT NULL,
  `attachments` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `unit_cost` decimal(15,2) NOT NULL,
  `cost` decimal(15,2) NOT NULL,
  `unit_price` decimal(15,2) NOT NULL,
  `price` decimal(15,2) NOT NULL,
  `subtotal` decimal(15,2) NOT NULL,
  `tax` decimal(15,2) NOT NULL,
  `shipping` decimal(15,2) NOT NULL,
  `discount` decimal(15,2) NOT NULL,
  `exemption` decimal(15,2) NOT NULL,
  `promise` decimal(15,2) NOT NULL,
  `payable` decimal(15,2) NOT NULL,
  `paid` decimal(15,2) NOT NULL,
  `due` decimal(15,2) NOT NULL,
  `date` date NOT NULL,
  `requested` int NOT NULL,
  `requested_quantity` int NOT NULL,
  `requested_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `requested_user_id` int NOT NULL,
  `requested_datetime` datetime NOT NULL,
  `authorized` int NOT NULL,
  `authorized_quantity` int NOT NULL,
  `authorized_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorized_user_id` int NOT NULL,
  `authorized_datetime` datetime NOT NULL,
  `approved` int NOT NULL,
  `approved_quantity` int NOT NULL,
  `approved_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `approved_user_id` int NOT NULL,
  `approved_datetime` datetime NOT NULL,
  `transferred` int NOT NULL,
  `transferred_quantity` int NOT NULL,
  `transferred_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `transferred_user_id` int NOT NULL,
  `transferred_datetime` datetime NOT NULL,
  `received` int NOT NULL,
  `received_quantity` int NOT NULL,
  `received_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `received_user_id` int NOT NULL,
  `received_datetime` datetime NOT NULL,
  `confirmed` int NOT NULL,
  `confirmed_quantity` int NOT NULL,
  `confirmed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `confirmed_user_id` int NOT NULL,
  `confirmed_datetime` datetime NOT NULL,
  `billed` int NOT NULL,
  `billed_quantity` int NOT NULL,
  `billed_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_user_id` int NOT NULL,
  `billed_datetime` datetime NOT NULL,
  `payment` int NOT NULL,
  `payment_quantity` int NOT NULL,
  `payment_channel` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_method` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account_id` int NOT NULL,
  `payment_transaction` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_receipt` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_phone` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_user_id` int NOT NULL,
  `payment_datetime` datetime NOT NULL,
  `forwarded` int NOT NULL,
  `forwarded_quantity` int NOT NULL,
  `forwarded_to` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `forwarded_id` int NOT NULL,
  `forwarded_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `forwarded_user_id` int NOT NULL,
  `forwarded_datetime` datetime NOT NULL,
  `dispensed` int NOT NULL,
  `dispensed_quantity` int NOT NULL,
  `dispensed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dispensed_user_id` int NOT NULL,
  `dispensed_datetime` datetime NOT NULL,
  `returned` int NOT NULL,
  `returned_quantity` int NOT NULL,
  `returned_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `returned_user_id` int NOT NULL,
  `returned_datetime` datetime NOT NULL,
  `printed` int NOT NULL,
  `printed_quantity` int NOT NULL,
  `printed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `printed_user_id` int NOT NULL,
  `printed_datetime` datetime NOT NULL,
  `f1` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f2` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f3` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f4` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f5` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f6` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f7` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f8` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f9` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_user_id` int NOT NULL,
  `created_datetime` datetime NOT NULL,
  `modified_user_id` int NOT NULL,
  `modified_datetime` datetime NOT NULL,
  `status` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent` (`parent`),
  KEY `parent_id` (`parent_id`),
  KEY `item_id` (`item_id`),
  KEY `name` (`name`),
  KEY `batch` (`batch`),
  KEY `expiry` (`expiry`),
  KEY `progress` (`progress`),
  KEY `progress_code` (`progress_code`),
  KEY `requested` (`requested`),
  KEY `authorized` (`authorized`),
  KEY `approved` (`approved`),
  KEY `transferred` (`transferred`),
  KEY `confirmed` (`confirmed`),
  KEY `status` (`status`),
  KEY `store_id` (`store_id`),
  KEY `date` (`date`) USING BTREE,
  KEY `type` (`type`),
  KEY `dispensed` (`dispensed`),
  KEY `returned` (`returned`),
  KEY `type_id` (`type_id`),
  KEY `subtype` (`subtype`),
  KEY `subtype_id` (`subtype_id`),
  KEY `subtype_item_id` (`subtype_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contains exemption items';

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

--
-- Table structure for table `billing_promises`
--

DROP TABLE IF EXISTS `billing_promises`;
CREATE TABLE IF NOT EXISTS `billing_promises` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type_id` int NOT NULL,
  `subtype` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `subtype_id` int NOT NULL,
  `parent` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_id` int NOT NULL,
  `reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `personnel` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `personnel_id` int NOT NULL,
  `store_id` int NOT NULL,
  `code` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `birthdate` date NOT NULL,
  `sex` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pricelist` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pricelist_id` int NOT NULL,
  `insurance` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `insurance_id` int NOT NULL,
  `scheme` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `scheme_id` int NOT NULL,
  `product` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_id` int NOT NULL,
  `card_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorization_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `supplementary` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `folio_id` int NOT NULL,
  `folio_no` int NOT NULL,
  `year` year NOT NULL,
  `month` int NOT NULL,
  `date` date NOT NULL,
  `visit_id` int NOT NULL,
  `visit_location_id` int NOT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `urgency` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `urgency_code` int NOT NULL,
  `progress` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_code` int NOT NULL,
  `progress_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_user_id` int NOT NULL,
  `progress_datetime` datetime NOT NULL,
  `attachments` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `items` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `cost` decimal(15,2) NOT NULL,
  `subtotal` decimal(15,2) NOT NULL,
  `tax` decimal(15,2) NOT NULL,
  `shipping` decimal(15,2) NOT NULL,
  `discount` decimal(15,2) NOT NULL,
  `exemption` decimal(15,2) NOT NULL,
  `promise` decimal(15,2) NOT NULL,
  `payable` decimal(15,2) NOT NULL,
  `paid` decimal(15,2) NOT NULL,
  `due` decimal(15,2) NOT NULL,
  `requested` int NOT NULL,
  `requested_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `requested_user_id` int NOT NULL,
  `requested_datetime` datetime NOT NULL,
  `authorized` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorized_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorized_user_id` int NOT NULL,
  `authorized_datetime` datetime NOT NULL,
  `approved` int NOT NULL,
  `approved_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `approved_user_id` int NOT NULL,
  `approved_datetime` datetime NOT NULL,
  `transferred` int NOT NULL,
  `transferred_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `transferred_user_id` int NOT NULL,
  `transferred_datetime` datetime NOT NULL,
  `received` int NOT NULL,
  `received_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `received_user_id` int NOT NULL,
  `received_datetime` datetime NOT NULL,
  `confirmed` int NOT NULL,
  `confirmed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `confirmed_user_id` int NOT NULL,
  `confirmed_datetime` datetime NOT NULL,
  `billed` int NOT NULL,
  `billed_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_user_id` int NOT NULL,
  `billed_datetime` datetime NOT NULL,
  `payment` int NOT NULL,
  `payment_method` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_channel` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_transaction` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_receipt` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_phone` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_user_id` int NOT NULL,
  `payment_datetime` datetime NOT NULL,
  `forwarded` int NOT NULL,
  `forwarded_to` int NOT NULL,
  `forwarded_id` int NOT NULL,
  `forwarded_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `forwarded_user_id` int NOT NULL,
  `forwarded_datetime` datetime NOT NULL,
  `dispensed` int NOT NULL,
  `dispensed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dispensed_user_id` int NOT NULL,
  `dispensed_datetime` datetime NOT NULL,
  `returned` int NOT NULL,
  `returned_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `returned_user_id` int NOT NULL,
  `returned_datetime` datetime NOT NULL,
  `printed` int NOT NULL,
  `printed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `printed_user_id` int NOT NULL,
  `printed_datetime` datetime NOT NULL,
  `f1` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f2` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f3` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f4` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f5` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f6` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f7` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f8` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f9` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_user_id` int NOT NULL,
  `created_datetime` datetime NOT NULL,
  `modified_user_id` int NOT NULL,
  `modified_datetime` datetime NOT NULL,
  `status` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `code` (`code`),
  KEY `subtype` (`subtype`),
  KEY `parent` (`parent`),
  KEY `reference_code` (`reference_no`),
  KEY `personnel_id` (`personnel_id`),
  KEY `name` (`name`),
  KEY `insurance_id` (`insurance_id`),
  KEY `scheme_id` (`scheme_id`),
  KEY `urgency_code` (`urgency_code`),
  KEY `progress_code` (`progress_code`),
  KEY `requested` (`requested`),
  KEY `authorized` (`authorized`),
  KEY `approved` (`approved`),
  KEY `confirmed` (`confirmed`),
  KEY `transfered` (`transferred`),
  KEY `printed` (`printed`),
  KEY `created_user_id` (`created_user_id`),
  KEY `created_datetime` (`created_datetime`),
  KEY `modified_user_id` (`modified_user_id`),
  KEY `modified_datetime` (`modified_datetime`),
  KEY `status` (`status`),
  KEY `store_id` (`store_id`),
  KEY `parent_id` (`parent_id`) USING BTREE,
  KEY `forwarded` (`forwarded`),
  KEY `forwarded_to` (`forwarded_to`),
  KEY `billed` (`billed`),
  KEY `forwarded_id` (`forwarded_id`),
  KEY `requested_datetime` (`requested_datetime`),
  KEY `billed_datetime` (`billed_datetime`),
  KEY `payment` (`payment`),
  KEY `payment_method` (`payment_method`),
  KEY `payment_channel` (`payment_channel`),
  KEY `payment_account` (`payment_account`),
  KEY `payment_account_id` (`payment_account_id`),
  KEY `payment_user_id` (`payment_user_id`),
  KEY `payment_reference` (`payment_reference`),
  KEY `payment_datetime` (`payment_datetime`),
  KEY `card_no` (`card_no`),
  KEY `authorization_no` (`authorization_no`),
  KEY `folio_id` (`folio_id`),
  KEY `folio_no` (`folio_no`),
  KEY `year` (`year`),
  KEY `month` (`month`),
  KEY `visit_id` (`visit_id`),
  KEY `pricelist_id` (`pricelist_id`),
  KEY `date` (`date`),
  KEY `visit_location_id` (`visit_location_id`),
  KEY `insurance` (`insurance`),
  KEY `scheme` (`scheme`),
  KEY `dispensed` (`dispensed`),
  KEY `returned` (`returned`),
  KEY `subtype_id` (`subtype_id`),
  KEY `type_id` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contains promise to pay';

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

--
-- Table structure for table `billing_promise_items`
--

DROP TABLE IF EXISTS `billing_promise_items`;
CREATE TABLE IF NOT EXISTS `billing_promise_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type_id` int NOT NULL,
  `subtype` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `subtype_id` int NOT NULL,
  `subtype_item_id` int NOT NULL,
  `parent` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_id` int NOT NULL,
  `insurance_id` int NOT NULL,
  `account_id` int NOT NULL,
  `scheme_id` int NOT NULL,
  `folio_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorization_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `approval_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `store_id` int NOT NULL,
  `item_id` int NOT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `details` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `qty` int NOT NULL,
  `uom` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` int NOT NULL,
  `pack` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `size` int NOT NULL,
  `balance` int NOT NULL,
  `batch` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiry` date NOT NULL,
  `progress` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_code` int NOT NULL,
  `progress_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_user_id` int NOT NULL,
  `progress_datetime` datetime NOT NULL,
  `attachments` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `unit_cost` decimal(15,2) NOT NULL,
  `cost` decimal(15,2) NOT NULL,
  `unit_price` decimal(15,2) NOT NULL,
  `price` decimal(15,2) NOT NULL,
  `subtotal` decimal(15,2) NOT NULL,
  `tax` decimal(15,2) NOT NULL,
  `shipping` decimal(15,2) NOT NULL,
  `discount` decimal(15,2) NOT NULL,
  `exemption` decimal(15,2) NOT NULL,
  `promise` decimal(15,2) NOT NULL,
  `payable` decimal(15,2) NOT NULL,
  `paid` decimal(15,2) NOT NULL,
  `due` decimal(15,2) NOT NULL,
  `date` date NOT NULL,
  `requested` int NOT NULL,
  `requested_quantity` int NOT NULL,
  `requested_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `requested_user_id` int NOT NULL,
  `requested_datetime` datetime NOT NULL,
  `authorized` int NOT NULL,
  `authorized_quantity` int NOT NULL,
  `authorized_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorized_user_id` int NOT NULL,
  `authorized_datetime` datetime NOT NULL,
  `approved` int NOT NULL,
  `approved_quantity` int NOT NULL,
  `approved_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `approved_user_id` int NOT NULL,
  `approved_datetime` datetime NOT NULL,
  `transferred` int NOT NULL,
  `transferred_quantity` int NOT NULL,
  `transferred_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `transferred_user_id` int NOT NULL,
  `transferred_datetime` datetime NOT NULL,
  `received` int NOT NULL,
  `received_quantity` int NOT NULL,
  `received_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `received_user_id` int NOT NULL,
  `received_datetime` datetime NOT NULL,
  `confirmed` int NOT NULL,
  `confirmed_quantity` int NOT NULL,
  `confirmed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `confirmed_user_id` int NOT NULL,
  `confirmed_datetime` datetime NOT NULL,
  `billed` int NOT NULL,
  `billed_quantity` int NOT NULL,
  `billed_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_user_id` int NOT NULL,
  `billed_datetime` datetime NOT NULL,
  `payment` int NOT NULL,
  `payment_quantity` int NOT NULL,
  `payment_channel` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_method` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account_id` int NOT NULL,
  `payment_transaction` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_receipt` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_phone` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_user_id` int NOT NULL,
  `payment_datetime` datetime NOT NULL,
  `forwarded` int NOT NULL,
  `forwarded_quantity` int NOT NULL,
  `forwarded_to` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `forwarded_id` int NOT NULL,
  `forwarded_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `forwarded_user_id` int NOT NULL,
  `forwarded_datetime` datetime NOT NULL,
  `dispensed` int NOT NULL,
  `dispensed_quantity` int NOT NULL,
  `dispensed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dispensed_user_id` int NOT NULL,
  `dispensed_datetime` datetime NOT NULL,
  `returned` int NOT NULL,
  `returned_quantity` int NOT NULL,
  `returned_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `returned_user_id` int NOT NULL,
  `returned_datetime` datetime NOT NULL,
  `printed` int NOT NULL,
  `printed_quantity` int NOT NULL,
  `printed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `printed_user_id` int NOT NULL,
  `printed_datetime` datetime NOT NULL,
  `f1` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f2` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f3` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f4` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f5` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f6` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f7` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f8` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f9` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_user_id` int NOT NULL,
  `created_datetime` datetime NOT NULL,
  `modified_user_id` int NOT NULL,
  `modified_datetime` datetime NOT NULL,
  `status` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent` (`parent`),
  KEY `parent_id` (`parent_id`),
  KEY `item_id` (`item_id`),
  KEY `name` (`name`),
  KEY `batch` (`batch`),
  KEY `expiry` (`expiry`),
  KEY `progress` (`progress`),
  KEY `progress_code` (`progress_code`),
  KEY `requested` (`requested`),
  KEY `authorized` (`authorized`),
  KEY `approved` (`approved`),
  KEY `transferred` (`transferred`),
  KEY `confirmed` (`confirmed`),
  KEY `status` (`status`),
  KEY `store_id` (`store_id`),
  KEY `date` (`date`) USING BTREE,
  KEY `type` (`type`),
  KEY `dispensed` (`dispensed`),
  KEY `returned` (`returned`),
  KEY `type_id` (`type_id`),
  KEY `subtype` (`subtype`),
  KEY `subtype_id` (`subtype_id`),
  KEY `subtype_item_id` (`subtype_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contains promise to pay items';

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

--
-- Table structure for table `billing_wallets`
--

DROP TABLE IF EXISTS `billing_wallets`;
CREATE TABLE IF NOT EXISTS `billing_wallets` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type_id` int NOT NULL,
  `subtype` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `subtype_id` int NOT NULL,
  `parent` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_id` int NOT NULL,
  `reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `personnel` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `personnel_id` int NOT NULL,
  `store_id` int NOT NULL,
  `code` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `birthdate` date NOT NULL,
  `sex` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pricelist` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `pricelist_id` int NOT NULL,
  `insurance` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `insurance_id` int NOT NULL,
  `scheme` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `scheme_id` int NOT NULL,
  `product` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_id` int NOT NULL,
  `card_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorization_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `supplementary` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `folio_id` int NOT NULL,
  `folio_no` int NOT NULL,
  `year` year NOT NULL,
  `month` int NOT NULL,
  `date` date NOT NULL,
  `visit_id` int NOT NULL,
  `visit_location_id` int NOT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `urgency` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `urgency_code` int NOT NULL,
  `progress` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_code` int NOT NULL,
  `progress_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_user_id` int NOT NULL,
  `progress_datetime` datetime NOT NULL,
  `attachments` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `items` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `cost` decimal(15,2) NOT NULL,
  `subtotal` decimal(15,2) NOT NULL,
  `tax` decimal(15,2) NOT NULL,
  `shipping` decimal(15,2) NOT NULL,
  `discount` decimal(15,2) NOT NULL,
  `exemption` decimal(15,2) NOT NULL,
  `promise` decimal(15,2) NOT NULL,
  `payable` decimal(15,2) NOT NULL,
  `paid` decimal(15,2) NOT NULL,
  `due` decimal(15,2) NOT NULL,
  `requested` int NOT NULL,
  `requested_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `requested_user_id` int NOT NULL,
  `requested_datetime` datetime NOT NULL,
  `authorized` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorized_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorized_user_id` int NOT NULL,
  `authorized_datetime` datetime NOT NULL,
  `approved` int NOT NULL,
  `approved_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `approved_user_id` int NOT NULL,
  `approved_datetime` datetime NOT NULL,
  `transferred` int NOT NULL,
  `transferred_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `transferred_user_id` int NOT NULL,
  `transferred_datetime` datetime NOT NULL,
  `received` int NOT NULL,
  `received_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `received_user_id` int NOT NULL,
  `received_datetime` datetime NOT NULL,
  `confirmed` int NOT NULL,
  `confirmed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `confirmed_user_id` int NOT NULL,
  `confirmed_datetime` datetime NOT NULL,
  `billed` int NOT NULL,
  `billed_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_user_id` int NOT NULL,
  `billed_datetime` datetime NOT NULL,
  `payment` int NOT NULL,
  `payment_method` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_channel` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_transaction` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_receipt` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_phone` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_user_id` int NOT NULL,
  `payment_datetime` datetime NOT NULL,
  `forwarded` int NOT NULL,
  `forwarded_to` int NOT NULL,
  `forwarded_id` int NOT NULL,
  `forwarded_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `forwarded_user_id` int NOT NULL,
  `forwarded_datetime` datetime NOT NULL,
  `dispensed` int NOT NULL,
  `dispensed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dispensed_user_id` int NOT NULL,
  `dispensed_datetime` datetime NOT NULL,
  `returned` int NOT NULL,
  `returned_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `returned_user_id` int NOT NULL,
  `returned_datetime` datetime NOT NULL,
  `printed` int NOT NULL,
  `printed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `printed_user_id` int NOT NULL,
  `printed_datetime` datetime NOT NULL,
  `f1` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f2` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f3` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f4` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f5` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f6` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f7` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f8` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f9` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_user_id` int NOT NULL,
  `created_datetime` datetime NOT NULL,
  `modified_user_id` int NOT NULL,
  `modified_datetime` datetime NOT NULL,
  `status` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `code` (`code`),
  KEY `subtype` (`subtype`),
  KEY `parent` (`parent`),
  KEY `reference_code` (`reference_no`),
  KEY `personnel_id` (`personnel_id`),
  KEY `name` (`name`),
  KEY `insurance_id` (`insurance_id`),
  KEY `scheme_id` (`scheme_id`),
  KEY `urgency_code` (`urgency_code`),
  KEY `progress_code` (`progress_code`),
  KEY `requested` (`requested`),
  KEY `authorized` (`authorized`),
  KEY `approved` (`approved`),
  KEY `confirmed` (`confirmed`),
  KEY `transfered` (`transferred`),
  KEY `printed` (`printed`),
  KEY `created_user_id` (`created_user_id`),
  KEY `created_datetime` (`created_datetime`),
  KEY `modified_user_id` (`modified_user_id`),
  KEY `modified_datetime` (`modified_datetime`),
  KEY `status` (`status`),
  KEY `store_id` (`store_id`),
  KEY `parent_id` (`parent_id`) USING BTREE,
  KEY `forwarded` (`forwarded`),
  KEY `forwarded_to` (`forwarded_to`),
  KEY `billed` (`billed`),
  KEY `forwarded_id` (`forwarded_id`),
  KEY `requested_datetime` (`requested_datetime`),
  KEY `billed_datetime` (`billed_datetime`),
  KEY `payment` (`payment`),
  KEY `payment_method` (`payment_method`),
  KEY `payment_channel` (`payment_channel`),
  KEY `payment_account` (`payment_account`),
  KEY `payment_account_id` (`payment_account_id`),
  KEY `payment_user_id` (`payment_user_id`),
  KEY `payment_reference` (`payment_reference`),
  KEY `payment_datetime` (`payment_datetime`),
  KEY `card_no` (`card_no`),
  KEY `authorization_no` (`authorization_no`),
  KEY `folio_id` (`folio_id`),
  KEY `folio_no` (`folio_no`),
  KEY `year` (`year`),
  KEY `month` (`month`),
  KEY `visit_id` (`visit_id`),
  KEY `pricelist_id` (`pricelist_id`),
  KEY `date` (`date`),
  KEY `visit_location_id` (`visit_location_id`),
  KEY `insurance` (`insurance`),
  KEY `scheme` (`scheme`),
  KEY `dispensed` (`dispensed`),
  KEY `returned` (`returned`),
  KEY `subtype_id` (`subtype_id`),
  KEY `type_id` (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contains customer wallets';

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

--
-- Table structure for table `billing_wallet_items`
--

DROP TABLE IF EXISTS `billing_wallet_items`;
CREATE TABLE IF NOT EXISTS `billing_wallet_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type_id` int NOT NULL,
  `subtype` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `subtype_id` int NOT NULL,
  `subtype_item_id` int NOT NULL,
  `parent` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_id` int NOT NULL,
  `insurance_id` int NOT NULL,
  `account_id` int NOT NULL,
  `scheme_id` int NOT NULL,
  `folio_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorization_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `approval_no` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `store_id` int NOT NULL,
  `item_id` int NOT NULL,
  `name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `details` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `qty` int NOT NULL,
  `uom` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` int NOT NULL,
  `pack` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `size` int NOT NULL,
  `balance` int NOT NULL,
  `batch` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiry` date NOT NULL,
  `progress` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_code` int NOT NULL,
  `progress_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `progress_user_id` int NOT NULL,
  `progress_datetime` datetime NOT NULL,
  `attachments` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `unit_cost` decimal(15,2) NOT NULL,
  `cost` decimal(15,2) NOT NULL,
  `unit_price` decimal(15,2) NOT NULL,
  `price` decimal(15,2) NOT NULL,
  `subtotal` decimal(15,2) NOT NULL,
  `tax` decimal(15,2) NOT NULL,
  `shipping` decimal(15,2) NOT NULL,
  `discount` decimal(15,2) NOT NULL,
  `exemption` decimal(15,2) NOT NULL,
  `promise` decimal(15,2) NOT NULL,
  `payable` decimal(15,2) NOT NULL,
  `paid` decimal(15,2) NOT NULL,
  `due` decimal(15,2) NOT NULL,
  `date` date NOT NULL,
  `requested` int NOT NULL,
  `requested_quantity` int NOT NULL,
  `requested_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `requested_user_id` int NOT NULL,
  `requested_datetime` datetime NOT NULL,
  `authorized` int NOT NULL,
  `authorized_quantity` int NOT NULL,
  `authorized_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorized_user_id` int NOT NULL,
  `authorized_datetime` datetime NOT NULL,
  `approved` int NOT NULL,
  `approved_quantity` int NOT NULL,
  `approved_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `approved_user_id` int NOT NULL,
  `approved_datetime` datetime NOT NULL,
  `transferred` int NOT NULL,
  `transferred_quantity` int NOT NULL,
  `transferred_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `transferred_user_id` int NOT NULL,
  `transferred_datetime` datetime NOT NULL,
  `received` int NOT NULL,
  `received_quantity` int NOT NULL,
  `received_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `received_user_id` int NOT NULL,
  `received_datetime` datetime NOT NULL,
  `confirmed` int NOT NULL,
  `confirmed_quantity` int NOT NULL,
  `confirmed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `confirmed_user_id` int NOT NULL,
  `confirmed_datetime` datetime NOT NULL,
  `billed` int NOT NULL,
  `billed_quantity` int NOT NULL,
  `billed_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `billed_user_id` int NOT NULL,
  `billed_datetime` datetime NOT NULL,
  `payment` int NOT NULL,
  `payment_quantity` int NOT NULL,
  `payment_channel` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_method` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_account_id` int NOT NULL,
  `payment_transaction` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_reference` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_receipt` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_phone` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_user_id` int NOT NULL,
  `payment_datetime` datetime NOT NULL,
  `forwarded` int NOT NULL,
  `forwarded_quantity` int NOT NULL,
  `forwarded_to` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `forwarded_id` int NOT NULL,
  `forwarded_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `forwarded_user_id` int NOT NULL,
  `forwarded_datetime` datetime NOT NULL,
  `dispensed` int NOT NULL,
  `dispensed_quantity` int NOT NULL,
  `dispensed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dispensed_user_id` int NOT NULL,
  `dispensed_datetime` datetime NOT NULL,
  `returned` int NOT NULL,
  `returned_quantity` int NOT NULL,
  `returned_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `returned_user_id` int NOT NULL,
  `returned_datetime` datetime NOT NULL,
  `printed` int NOT NULL,
  `printed_quantity` int NOT NULL,
  `printed_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `printed_user_id` int NOT NULL,
  `printed_datetime` datetime NOT NULL,
  `f1` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f2` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f3` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f4` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f5` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f6` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f7` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f8` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `f9` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_user_id` int NOT NULL,
  `created_datetime` datetime NOT NULL,
  `modified_user_id` int NOT NULL,
  `modified_datetime` datetime NOT NULL,
  `status` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent` (`parent`),
  KEY `parent_id` (`parent_id`),
  KEY `item_id` (`item_id`),
  KEY `name` (`name`),
  KEY `batch` (`batch`),
  KEY `expiry` (`expiry`),
  KEY `progress` (`progress`),
  KEY `progress_code` (`progress_code`),
  KEY `requested` (`requested`),
  KEY `authorized` (`authorized`),
  KEY `approved` (`approved`),
  KEY `transferred` (`transferred`),
  KEY `confirmed` (`confirmed`),
  KEY `status` (`status`),
  KEY `store_id` (`store_id`),
  KEY `date` (`date`) USING BTREE,
  KEY `type` (`type`),
  KEY `dispensed` (`dispensed`),
  KEY `returned` (`returned`),
  KEY `type_id` (`type_id`),
  KEY `subtype` (`subtype`),
  KEY `subtype_id` (`subtype_id`),
  KEY `subtype_item_id` (`subtype_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contains wallet items';
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
