CREATE TABLE `la_exception` (
                                `id` INT(11) NOT NULL AUTO_INCREMENT,
                                `exception` TEXT NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
                                `la_request_id` INT(11) NULL DEFAULT NULL,
                                `status` TINYINT(4) NOT NULL DEFAULT '1',
                                PRIMARY KEY (`id`) USING BTREE
)COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

ALTER TABLE `la_requests`
    ADD COLUMN `is_exception` TINYINT NULL DEFAULT '0' AFTER `is_terminated`;
INSERT INTO `system_level_policies` (`module`, `label`, `status`) VALUES ('Loan&Advance', 'exception', '1');
CREATE TABLE `la_setup_pool_amount`
(
    `id`                    INT(11) NOT NULL AUTO_INCREMENT,
    `pool_type`      VARCHAR(15) NULL DEFAULT NULL,
    `creator_comments`      varchar(255) DEFAULT NULL,
    `requested_pool_amount` DOUBLE(25,2) DEFAULT '0',
    `balance_pool_amount` DOUBLE(25,2) DEFAULT '0',
    `in_process_amount` DOUBLE(25,2) DEFAULT '0',
    `is_pool_principle_update` INT(2) DEFAULT '0',
    `pool_principle_updated_amount` DOUBLE(25,2) DEFAULT '0',
	 `created_at`            TIMESTAMP NULL DEFAULT NULL,
    `updated_at`            TIMESTAMP NULL DEFAULT NULL,
    `deleted_at`            TIMESTAMP NULL DEFAULT NULL,
    `created_by`            INT(9) UNSIGNED NULL DEFAULT NULL,
    `updated_by`            INT(9) UNSIGNED NULL DEFAULT NULL,
    `deleted_by`            INT(9) UNSIGNED NULL DEFAULT NULL,
    `client_id`             INT(9) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY                     `Foregin_key_la_setup_pool_amount_created_by` (`created_by`),
    KEY                     `Foregin_key_la_setup_pool_amount_updated_by` (`updated_by`),
    KEY                     `Foregin_key_la_setup_pool_amount_deleted_by` (`deleted_by`),
    KEY                     `Foregin_key_la_setup_pool_amount_client_id` (`client_id`),
    CONSTRAINT `Foregin_key_la_setup_pool_amount_client_id` FOREIGN KEY (`client_id`) REFERENCES `proll_client` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `Foregin_key_la_setup_pool_amount_created_by` FOREIGN KEY (`created_by`) REFERENCES `proll_employee` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `Foregin_key_la_setup_pool_amount_deleted_by` FOREIGN KEY (`deleted_by`) REFERENCES `proll_employee` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `Foregin_key_la_setup_pool_amount_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `proll_employee` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);



CREATE TABLE `la_setup_pool_amount_detail`
(
    `id`                    INT(11) NOT NULL AUTO_INCREMENT,
    `la_setup_pool_amount_id`      INT(11) NULL DEFAULT NULL,
    `amount`        DOUBLE(25,2) DEFAULT '0',
    `comments`      varchar(255) DEFAULT NULL,
    `is_return` INT(4) NULL DEFAULT '0',
    `is_disburse` INT(4) NULL DEFAULT '0',
    `created_at`            TIMESTAMP NULL DEFAULT NULL,
    `updated_at`            TIMESTAMP NULL DEFAULT NULL,
    `deleted_at`            TIMESTAMP NULL DEFAULT NULL,
    `created_by`            INT(9) UNSIGNED NULL DEFAULT NULL,
    `updated_by`            INT(9) UNSIGNED NULL DEFAULT NULL,
    `deleted_by`            INT(9) UNSIGNED NULL DEFAULT NULL,
    `client_id`             INT(9) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY                     `Foregin_key_la_setup_pool_amount_detail_created_by` (`created_by`),
    KEY                     `Foregin_key_la_setup_pool_amount_detail_la_setup_pool_amount` (`la_setup_pool_amount_id`),
    KEY                     `Foregin_key_la_setup_pool_amount_detail_updated_by` (`updated_by`),
    KEY                     `Foregin_key_la_setup_pool_amount_detail_deleted_by` (`deleted_by`),
    KEY                     `Foregin_key_la_setup_pool_amount_detail_client_id` (`client_id`),
    CONSTRAINT `Foregin_key_la_setup_pool_amount_detail_la_setup_pool_amount` FOREIGN KEY (`la_setup_pool_amount_id`) REFERENCES `la_setup_pool_amount` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `Foregin_key_la_setup_pool_amount_detail_client_id` FOREIGN KEY (`client_id`) REFERENCES `proll_client` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `Foregin_key_la_setup_pool_amount_detail_created_by` FOREIGN KEY (`created_by`) REFERENCES `proll_employee` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `Foregin_key_la_setup_pool_amount_detail_deleted_by` FOREIGN KEY (`deleted_by`) REFERENCES `proll_employee` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `Foregin_key_la_setup_pool_amount_detail_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `proll_employee` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);



ALTER TABLE `la_requests` ADD `is_sent_ceo` INT(4) DEFAULT 0;


ALTER TABLE `la_setup` ADD `la_setup_pool_amount_id` INT(11);

ALTER TABLE `la_setup`
    ADD CONSTRAINT `Foregin_key_la_setup_la_setup_pool_amount`
        FOREIGN KEY (`la_setup_pool_amount_id`) REFERENCES `la_setup_pool_amount`(`id`);
INSERT INTO `system_level_policies` (`module`, `label`, `status`) VALUES ('Loan&Advance', 'sent_ceo', '1');
