--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements. See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership. The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied. See the License for the
-- specific language governing permissions and limitations
-- under the License.
--

-- add code and code values for datatables dropdowns

INSERT INTO `m_code_value`(`code_id`,`code_value`,`order_position`)
select mc.id, 'option.Male', ifnull(max(mv.id), 1)
from m_code mc
join m_code_value mv on mv.code_id = mc.id
where mc.`code_name` = "Gender";

INSERT INTO `m_code_value`(`code_id`,`code_value`,`order_position`)
select mc.id, 'option.Female', ifnull(max(mv.id), 1)
from m_code mc
join m_code_value mv on mv.code_id = mc.id
where mc.`code_name` = "Gender";

INSERT INTO `m_code_value`(`code_id`,`code_value`,`order_position`)
select mc.id, 'option.Yes', ifnull(max(mv.id), 1)
from m_code mc
join m_code_value mv on mv.code_id = mc.id
where mc.`code_name` = "YesNo";

INSERT INTO `m_code_value`(`code_id`,`code_value`,`order_position`)
select mc.id, 'option.No', ifnull(max(mv.id), 1)
from m_code mc
join m_code_value mv on mv.code_id = mc.id
where mc.`code_name` = "YesNo";

INSERT INTO `m_code`
(`code_name`, `is_system_defined`)
VALUES
('Education',1);

INSERT INTO `m_code_value`(`code_id`,`code_value`,`order_position`)
select mc.id, 'Primary', ifnull(max(mv.id), 1)
from m_code mc
join m_code_value mv on mv.code_id = mc.id
where mc.`code_name` = "Education";

INSERT INTO `m_code_value`(`code_id`,`code_value`,`order_position`)
select mc.id, 'Secondary', ifnull(max(mv.id), 1)
from m_code mc
join m_code_value mv on mv.code_id = mc.id
where mc.`code_name` = "Education";

INSERT INTO `m_code_value`(`code_id`,`code_value`,`order_position`)
select mc.id, 'University', ifnull(max(mv.id), 1)
from m_code mc
join m_code_value mv on mv.code_id = mc.id
where mc.`code_name` = "Education";

-- ========= datatables=======

DROP TABLE IF EXISTS `extra_client_details`;
CREATE TABLE `extra_client_details` (
  `client_id` BIGINT NOT NULL,
  `Business Description` varchar(100) DEFAULT NULL,
  `Years in Business` INT DEFAULT NULL,
  `Gender_cd` INT DEFAULT NULL,
  `Education_cv` varchar(60) DEFAULT NULL,
  `Next Visit` date DEFAULT NULL,
  `Highest Rate Paid` decimal(19,6) DEFAULT NULL,
  `Comment` text,
  PRIMARY KEY (`client_id`),
  CONSTRAINT `FK_extra_client_details` FOREIGN KEY (`client_id`) REFERENCES `m_client` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;

DROP TABLE IF EXISTS `extra_family_details`;
CREATE TABLE `extra_family_details` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT NOT NULL,
  `Name` varchar(40) DEFAULT NULL,
  `Date of Birth` date DEFAULT NULL,
  `Points Score` INT DEFAULT NULL,
  `Education_cd_Highest` INT DEFAULT NULL,
  `Other Notes` text,
  PRIMARY KEY (`id`),
  KEY `FK_Extra Family Details Data_1` (`client_id`),
  CONSTRAINT `FK_family_details` FOREIGN KEY (`client_id`) REFERENCES `m_client` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;

DROP TABLE IF EXISTS `extra_loan_details`;
CREATE TABLE `extra_loan_details` (
  `loan_id` BIGINT NOT NULL,
  `Business Description` varchar(100) DEFAULT NULL,
  `Years in Business` INT DEFAULT NULL,
  `Gender_cd` INT DEFAULT NULL,
  `Education_cv` varchar(60) DEFAULT NULL,
  `Next Visit` date DEFAULT NULL,
  `Highest Rate Paid` decimal(19,6) DEFAULT NULL,
  `Comment` text,
  PRIMARY KEY (`loan_id`),
  CONSTRAINT `FK_extra_loan_details` FOREIGN KEY (`loan_id`) REFERENCES `m_loan` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;

-- datatables mapping
INSERT INTO `x_registered_table`
(`registered_table_name`,`application_table_name`)
VALUES
('extra_client_details', 'm_client'),
('extra_family_details', 'm_client'),
('extra_loan_details', 'm_loan');


-- make sure permissions created for registered datatables
/* add a create, read, update and delete permission for each registered datatable */
insert into m_permission(grouping, `code`, entity_name, action_name)
select 'datatable', concat('CREATE_', r.registered_table_name), r.registered_table_name, 'CREATE'
from x_registered_table r;

insert into m_permission(grouping, `code`, entity_name, action_name)
select 'datatable', concat('READ_', r.registered_table_name), r.registered_table_name, 'READ'
from x_registered_table r;

insert into m_permission(grouping, `code`, entity_name, action_name)
select 'datatable', concat('UPDATE_', r.registered_table_name), r.registered_table_name, 'UPDATE'
from x_registered_table r;

insert into m_permission(grouping, `code`, entity_name, action_name)
select 'datatable', concat('DELETE_', r.registered_table_name), r.registered_table_name, 'DELETE'
from x_registered_table r;


-- ==== Chart of Accounts =====
truncate `acc_gl_account`;
INSERT INTO `acc_gl_account` VALUES
(1,'Petty Cash Balances',NULL,'11100',0,1,2,1,NULL),
(2,'Cash in Valut 1',NULL,'11101',0,1,1,1,NULL),
(3,'Bank Balances',NULL,'11200',0,1,2,1,NULL),
(4,'Centenary Opening Account',NULL,'11201',0,1,1,1,NULL),
(5,'Centenary Expense Account',NULL,'11202',0,1,1,1,NULL),
(6,'Centenary USD Account',NULL,'11203',0,1,1,1,NULL),
(7,'Loans and Advances',NULL,'13100',0,1,2,1,NULL),
(8,'Loans to Clients',NULL,'13101',0,1,1,1,NULL),
(9,'Outstanding Interest',NULL,'13102',0,1,1,1,NULL),
(10,'Outstanding Late Payment Interest',NULL,'13103',0,1,1,1,NULL),
(11,'Outstanding Bank Fees to be collected',NULL,'13104',0,1,1,1,NULL),
(12,'WriteOff Accounts',NULL,'13200',0,1,2,1,NULL),
(13,'Write-offs (use for funds coming in)',NULL,'13201',0,1,1,1,NULL),
(14,'Write-offs outstanding principal',NULL,'13202',0,1,1,1,NULL),
(15,'Write-offs outstanding interest',NULL,'13203',0,1,1,1,NULL),
(16,'Write-offs collected bank fees',NULL,'13204',0,1,1,1,NULL),
(17,'Write-offs hardware/furniture',NULL,'13205',0,1,1,1,NULL),
(18,'Fixed Assets',NULL,'14100',0,1,2,1,NULL),
(19,'Office Equipment',NULL,'14101',0,1,1,1,NULL),
(20,'Suspense Items (unidentified deposits)',NULL,'15000',0,1,2,1,NULL),
(21,'Assets',NULL,'10000',0,1,2,1,NULL),
(22,'Liabilities',NULL,'20000',0,1,2,2,NULL),
(23,'Shares Account',NULL,'26100',0,1,2,2,NULL),
(24,'Shares Captial',NULL,'26101',0,1,1,2,NULL),
(25,'Donated Equity',NULL,'26300',0,1,2,2,NULL),
(26,'Donated Equity Ameropa Foundation',NULL,'26301',0,1,1,2,NULL),
(27,'Donated Equity e.h',NULL,'26302',0,1,1,2,NULL),
(28,'Overpaid Amounts',NULL,'27000',0,1,2,2,NULL),
(29,'Loss Provision',NULL,'28000',0,1,2,2,NULL),
(30,'Provision Outstanding Principal',NULL,'28001',0,1,1,2,NULL),
(31,'Provision Oustanding Interest',NULL,'28002',0,1,1,2,NULL),(32,'Income',NULL,'30000',0,1,2,4,NULL),
(33,'Interest Income from Loans',NULL,'31100',0,1,2,4,NULL),
(34,'Interest on Loans',NULL,'31101',0,1,1,4,NULL),
(35,'Late Payment Interest',NULL,'31102',0,1,1,4,NULL),
(36,'Income from Micro credit & Lending Activities',NULL,'31300',0,1,2,4,NULL),
(37,'Collected Bank Fees Receivable',NULL,'6201',0,1,1,4,NULL),
(38,'Deposits from Loans Write Off',NULL,'31400',0,1,2,4,NULL),
(39,'Expenditure',NULL,'40000',0,1,2,5,NULL),
(40,'Office Expenditure Account',NULL,'42100',0,1,2,5,NULL),
(41,'Water Charges',NULL,'42102',0,1,1,5,NULL),
(42,'Electricity Charges',NULL,'42103',0,1,1,5,NULL),
(43,'Printing and Stationary',NULL,'42105',0,1,1,5,NULL),
(44,'Office Rent',NULL,'42107',0,1,1,5,NULL),
(45,'Marketing Expense',NULL,'42109',0,1,1,5,NULL),
(46,'Office utilities',NULL,'42112',0,1,1,5,'(supplies, toiletries, kitchen)'),
(47,'Furniture',NULL,'42113',0,1,1,5,NULL),
(48,'CEDA Meeting Expense',NULL,'42114',0,1,1,5,NULL),
(49,'Employee Personal Expsense Account',NULL,'42200',0,1,2,5,NULL),
(50,'Salary Alice',NULL,'42201',0,1,1,5,NULL),
(51,'Salary Irene',NULL,'42202',0,1,1,5,NULL),
(52,'Salary Richard',NULL,'42203',0,1,1,5,NULL),
(53,'Salary Loan Officer TBA',NULL,'42204',0,1,1,5,NULL),
(54,'Medical Insurance Alice & Family',NULL,'42205',0,1,1,5,NULL),
(55,'Medical Insurance Irene',NULL,'42206',0,1,1,5,NULL),
(56,'Medical Insurance Richard',NULL,'42207',0,1,1,5,NULL),
(57,'Medical Insurance Loan Officer TBA',NULL,'42208',0,1,1,5,NULL),
(58,'PAYE all employees',NULL,'42209',0,1,1,5,NULL),
(59,'NSSF all employees',NULL,'42210',0,1,1,5,NULL),
(60,'Lunch Allowances all employees',NULL,'42211',0,1,1,5,NULL),
(61,'IT software and maintenance',NULL,'42300',0,1,2,5,NULL),
(62,'Mifos maintenance contract 12 months',NULL,'42301',0,1,1,5,NULL),
(63,'VPS Contract 12 months',NULL,'42302',0,1,1,5,NULL),
(64,'Bulk SMS Service',NULL,'42303',0,1,1,5,NULL),
(65,'Support Accounting Software',NULL,'42304',0,1,1,5,NULL),
(66,'Mifos Instance Setup',NULL,'42305',0,1,1,5,NULL),
(67,'Misc support expense',NULL,'42306',0,1,1,5,NULL),
(68,'Warid Mobile Line',NULL,'42307',0,1,1,5,NULL),
(69,'Landline',NULL,'42308',0,1,1,5,NULL),
(70,'Modem Alice',NULL,'42309',0,1,1,5,NULL),
(71,'Modem Irene',NULL,'42310',0,1,1,5,NULL),
(72,'Modem Richard',NULL,'42311',0,1,1,5,NULL),
(73,'Repairs',NULL,'42312',0,1,1,5,NULL),
(74,'Airtime Expenses',NULL,'42400',0,1,2,5,NULL),
(75,'Airtime Alice',NULL,'42401',0,1,1,5,NULL),
(76,'Airtime Richard',NULL,'42402',0,1,1,5,NULL),
(77,'Airtime Loan Office TBA',NULL,'42403',0,1,1,5,NULL),
(78,'Special Airtime Alice',NULL,'42404',0,1,1,5,NULL),
(79,'Transportation',NULL,'42500',0,1,2,5,NULL),
(80,'Flat monthly transportation cost',NULL,'42501',0,1,1,5,NULL),
(81,'Faciliation cost for Richard',NULL,'42502',0,1,1,5,NULL),
(82,'Faciliation cost for Loan Officer TBA',NULL,'42503',0,1,1,5,NULL),
(83,'Consultancy Expenses',NULL,'42600',0,1,2,5,NULL),
(84,'Audit Fees',NULL,'42601',0,1,1,5,NULL),
(85,'Legal Fees',NULL,'42602',0,1,1,5,NULL),
(86,'Miscellaneous Expenses Account',NULL,'42700',0,1,2,5,NULL),
(87,'MFI License',NULL,'42703',0,1,1,5,NULL),
(88,'Sundy Expenses',NULL,'42704',0,1,1,5,NULL),
(89,'Bank Fees',NULL,'42800',0,1,2,5,NULL),
(90,'Bank Charges Operating Account',NULL,'42801',0,1,1,5,NULL),
(91,'Bank Charges Expense Account',NULL,'42802',0,1,1,5,NULL),
(92,'E.H Account',NULL,'42900',0,1,2,5,NULL),
(93,'Airtime',NULL,'42901',0,1,1,5,NULL),
(94,'Modem',NULL,'42902',0,1,1,5,NULL),
(95,'Meals',NULL,'42903',0,1,1,5,NULL),
(96,'Transportation',NULL,'42904',0,1,1,5,NULL),
(97,'Miscellaneous',NULL,'42905',0,1,1,5,NULL);