-- 05/06/2019 11:13 am
ALTER TABLE `customers` CHANGE `ciyt_id` `city_id` BIGINT
(20) UNSIGNED NOT NULL COMMENT 'id de la ciudad';
ALTER TABLE `customers`
ADD `mail` VARCHAR
(50) NOT NULL COMMENT 'Correo electrónico de la empresa' AFTER `city_id`;
ALTER TABLE `customers`
ADD `observation` TEXT NULL COMMENT 'Para adicionar comentarios o cualquier observación sobre el cliente' AFTER `status`;
ALTER TABLE `customers`
ADD `billing_mail` VARCHAR
(50) NULL COMMENT 'Correo para facturación, opcional' AFTER `mail`;
ALTER TABLE `customers`
ADD `billing_phone` VARCHAR
(20) NULL COMMENT 'teléfono fijo para facturación ' AFTER `billing_mail`;
ALTER TABLE `customers`
ADD `billing_mobile` VARCHAR
(20) NULL COMMENT 'teléfono celular para facturación ' AFTER `billing_phone`;

-- 11/06/2019
CREATE TABLE `customer_contacts`
(
  `id` bigint
(20) UNSIGNED NOT NULL,
  `customer_id` bigint
(20) UNSIGNED NOT NULL COMMENT 'Cliente asociado al contacto, relación a tabla customers',
  `name` varchar
(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nombres de contacto',
  `last_name` varchar
(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Apellidos de contacto',
  `ocupacion` varchar
(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Cargo u Ocupación del contacto',
  `mail` varchar
(50) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Correo de contacto',
  `phone` varchar
(20) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Teléfono fijo de contacto',
  `mobile` varchar
(20) COLLATE utf8mb4_unicode_ci  NULL COMMENT 'Teléfono celular',
  `created_at` timestamp NULL ,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci OMMENT='Tabla para relacionar los contactos que tiene el cliente para comunicarse';

ALTER TABLE `customer_contacts`
ADD PRIMARY KEY
(`id`),
ADD KEY `customer_contacts_customer_id_foreign`
(`customer_id`);
ALTER TABLE `customer_contacts` MODIFY `id` bigint
(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `customer_contacts`
ADD CONSTRAINT `customer_contacts_customer_id_foreign` FOREIGN KEY
(`customer_id`) REFERENCES `customers`
(`id`);

-- 13/06/2019

CREATE TABLE `documents`
(
  `id` bigint
(20) UNSIGNED NOT NULL COMMENT 'Identificador',
  `table` varchar
(100) DEFAULT NULL COMMENT 'La tabla a la que enlaza ',
  `table_id` int
(11) DEFAULT NULL COMMENT 'Combinando tabla y ID identificamo la sección al cual corresponde el documento cargado. Ejemplo: table:trb_task table_id:5 el documento fue cargado en una gestión correspondiente al id 5. ',
  `name` varchar
(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nombres del documento',
  `type` varchar
(50) DEFAULT NULL COMMENT 'El tipo de documentos, estos tipos son obtenidos del archivo seleccionado, permitidos(PDF / Image / Word)',
  `path` varchar
(255) DEFAULT NULL COMMENT 'Ruta donde se encuentra el documento',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contiene la referencia a todo los documentos cargados en diferentes procesos.   Utiliza como referencia el campo tabla para identificar su correspondiente lugar y enlace. ';

ALTER TABLE `documents`
ADD PRIMARY KEY
(`id`);
ALTER TABLE `documents` MODIFY `id` bigint
(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `customers`
ADD `advance` BIGINT
(20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla parámetros y determina el avance del cliente, tales como: Cliente, Competencias, Incompatible, Potencial, Reasignado, Renuente' AFTER `status`;
ALTER TABLE `customers`
ADD KEY `customers_advance_foreign`
(`advance`);
ALTER TABLE `customers`
ADD CONSTRAINT `customers_advance_foreign` FOREIGN KEY
(`advance`) REFERENCES `parameters`
(`id`);

--- INGRESAR LOS TIPOS DE PARAMETROS
INSERT INTO `t_p_parameters` (`
id`,
`name
`, `created_at`, `updated_at`) VALUES
('4', 'AVANCE CLIENTE', '2019-06-13 00:00:00', '2019-06-13 00:00:00');


--- INGRESAR LOS PARAMETROS RELACIONADOS CON LOS TIPO DE PARAMETROS
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Cliente', '4', 'A', '2019-06-13 00:00:00', '2019-06-13 00:00:00');
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Competencias', '4', 'A', '2019-06-13 00:00:00', '2019-06-13 00:00:00');
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Incompatible', '4', 'A', '2019-06-13 00:00:00', '2019-06-13 00:00:00');
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Potencial', '4', 'A', '2019-06-13 00:00:00', '2019-06-13 00:00:00');
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Reasignado', '4', 'A', '2019-06-13 00:00:00', '2019-06-13 00:00:00');
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Renuente', '4', 'A', '2019-06-13 00:00:00', '2019-06-13 00:00:00');

-- 11/07/2019

-- DIRECCIÓN DEL CLIENTE
ALTER TABLE `customers`
DROP `address`,
DROP `neighborhood`,
DROP `city_id`;
-- quitar los campos de la tabla de cliente

CREATE TABLE `customer_addresses`
(
  `id` bigint
(20) UNSIGNED NOT NULL,
  `customer_id` bigint
(20) UNSIGNED NOT NULL COMMENT 'Cliente o cuenta asociado a la dirección, relación a tabla customers',
  `address` varchar
(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Dirección del cliente o cuenta',
  `neighborhood` varchar
(100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Barrio del cliente o cuenta',
  `city_id` bigint
(20) UNSIGNED NOT NULL COMMENT 'Ciudad asociada a la dirección, relación a tabla cities',
  `created_at` timestamp NULL ,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla para relacionar las n direcciones que tiene el cliente para ubicarlo';

-- key's y foraneas
ALTER TABLE `customer_addresses`
ADD PRIMARY KEY
(`id`);
ALTER TABLE `customer_addresses` MODIFY `id` bigint
(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `customer_addresses`
ADD KEY `customer_addresses_customer_id_foreign`
(`customer_id`);
ALTER TABLE `customer_addresses`
ADD CONSTRAINT `customer_addresses_customer_id_foreign` FOREIGN KEY
(`customer_id`) REFERENCES `customers`
(`id`);
ALTER TABLE `customer_addresses`
ADD KEY `customer_addresses_city_id_foreign`
(`city_id`);
ALTER TABLE `customer_addresses`
ADD CONSTRAINT `customer_addresses_city_id_foreign` FOREIGN KEY
(`city_id`) REFERENCES `cities`
(`id`);

-- TELEFONO
ALTER TABLE `customers`
DROP `phone`,
DROP `mobile`;
-- quitar los campos de la tabla de cliente

CREATE TABLE `address_phones`
(
  `id` bigint
(20) UNSIGNED NOT NULL,
  `address_id` bigint (20) UNSIGNED NOT NULL COMMENT 'Dirección asociada al teléfono',
  `phone` varchar
(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Número de telefono de la dirección',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla para relacionar una dirección con varios teléfonos';

-- key's y foraneas
ALTER TABLE `address_phones`
ADD PRIMARY KEY
(`id`);
ALTER TABLE `address_phones` MODIFY `id` bigint
(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `address_phones`
ADD KEY `address_phones_address_id_foreign`
(`address_id`);
ALTER TABLE `address_phones`
ADD CONSTRAINT `address_phones_address_id_foreign` FOREIGN KEY
(`address_id`) REFERENCES `customer_addresses`
(`id`);

-- CORREO
ALTER TABLE `customers`
DROP `mail`;
-- quitar los campos de la tabla de cliente

CREATE TABLE `address_mails`
(
  `id` bigint
(20) UNSIGNED NOT NULL,
  `address_id` bigint
(20) UNSIGNED NOT NULL COMMENT 'Dirección asociada al teléfono',
  `mail` varchar
(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Correo electrónico de la dirección',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla para relacionar una dirección con varios correos';

-- key's y foraneas
ALTER TABLE `address_mails`
ADD PRIMARY KEY
(`id`);
ALTER TABLE `address_mails` MODIFY `id` bigint
(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `address_mails`
ADD KEY `address_mails_address_id_foreign`
(`address_id`);
ALTER TABLE `address_mails`
ADD CONSTRAINT `address_mails_address_id_foreign` FOREIGN KEY
(`address_id`) REFERENCES `customer_addresses`
(`id`);

-- CAMPOS ADICIONALES
ALTER TABLE `customers`
DROP `latitude`,
DROP `longitude`;
-- quitar los campos de la tabla de cliente
ALTER TABLE `customer_addresses`
ADD `latitude` VARCHAR
(45) NULL COMMENT 'Definición de latitud para punto geografico necesario para APP Comercial' AFTER `city_id`;
ALTER TABLE `customer_addresses`
ADD `longitude` VARCHAR
(45) NULL COMMENT 'Definición de longitud para punto geografico necesario para APP Comercial' AFTER `latitude`;


ALTER TABLE `customers`
DROP `website`;
-- quitar los campos de la tabla de cliente
ALTER TABLE `customer_addresses`
ADD `website` VARCHAR
(50) NULL COMMENT 'Sitio web de la dirección' AFTER `longitude`;

-- 15/07/2019

CREATE TABLE `task_managements`
(
  `id` bigint
(20) UNSIGNED NOT NULL COMMENT 'Identificador',
  `date` timestamp NOT NULL COMMENT 'Fecha y hora de realización de la gestión ',
  `minutes` int
(11) DEFAULT NULL COMMENT 'Tienpo invertido en la gestión sobre la actividad',
  `type` BIGINT
(20) UNSIGNED NULL COMMENT 'Puede definirse como Envío de Carta / Mensaje / Llamada / Whatsapp / SMS / Visita/ otro, se relaciona con la tabla parameters',
  `task_id` BIGINT
(20) UNSIGNED NULL COMMENT 'Relacionada con la tarea o tasks',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Aqui se lleva el control de las gestiones sobre las actividades o cliente/cuenta. ';

ALTER TABLE `task_managements`
ADD PRIMARY KEY
(`id`);
ALTER TABLE `task_managements` MODIFY `id` bigint
(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `task_managements`
ADD KEY `task_managements_task_id_foreign`
(`task_id`);
ALTER TABLE `task_managements`
ADD CONSTRAINT `task_managements_task_id_foreign` FOREIGN KEY
(`task_id`) REFERENCES `tasks`
(`id`);
ALTER TABLE `task_managements`
ADD `management` VARCHAR
(200) NULL COMMENT 'Detalle de la gestión realizada' AFTER `task_id`;

ALTER TABLE `task_managements` ADD KEY `task_managements_type_foreign` (`type`);
ALTER TABLE `task_managements` ADD CONSTRAINT `task_managements_type_foreign` FOREIGN KEY (`type`) REFERENCES `parameters` (`id`);

----
ALTER TABLE `task_managements`
ADD `customer_id` BIGINT
(20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla customers y determina sobre que cuenta o cliente realizó la gestion' AFTER `management`;
ALTER TABLE `task_managements`
ADD KEY `task_managements_customer_id_foreign`
(`customer_id`);
ALTER TABLE `task_managements`
ADD CONSTRAINT `task_managements_customer_id_foreign` FOREIGN KEY
(`customer_id`) REFERENCES `customers`
(`id`);

ALTER TABLE `task_managements`
ADD `user_id` BIGINT
(20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla users y determina quien realizó la gestion' AFTER `customer_id`;
ALTER TABLE `task_managements`
ADD KEY `task_managements_user_id_foreign`
(`user_id`);
ALTER TABLE `task_managements`
ADD CONSTRAINT `task_managements_user_id_foreign` FOREIGN KEY
(`user_id`) REFERENCES `users`
(`id`);

-- 25/07/2019
ALTER TABLE `staff`
ADD `type` BIGINT
(20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla parámetros y determina el tipo de staff, tales como: Administrador, Comercial, Prejuridico, Jurídico' AFTER `status`;
ALTER TABLE `staff`
ADD KEY `staff_type_foreign`
(`type`);
ALTER TABLE `staff`
ADD CONSTRAINT `staff_type_foreign` FOREIGN KEY
(`type`) REFERENCES `parameters`
(`id`);

INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Administrador', '10', 'A', '2019-07-25 00:00:00', '2019-07-25 00:00:00');
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Comercial', '10', 'A', '2019-07-25 00:00:00', '2019-07-25 00:00:00');
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Prejuridico', '10', 'A', '2019-07-25 00:00:00', '2019-07-25 00:00:00');
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Jurídico', '10', 'A', '2019-07-25 00:00:00', '2019-07-25 00:00:00');

--
-- Estructura de tabla para la tabla `notifications`
--

CREATE TABLE `notifications`
(
  `id` char
(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` varchar
(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notifiable_type` varchar
(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notifiable_id` bigint
(20) UNSIGNED NOT NULL,
  `data` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indices de la tabla `notifications`
--
ALTER TABLE `notifications`
ADD PRIMARY KEY
(`id`),
ADD KEY `notifications_notifiable_type_notifiable_id_index`
(`notifiable_type`,`notifiable_id`);

ALTER TABLE `agreements` ADD `staff_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla staff y determina el comercial asociado al acuerdo' AFTER `customer_id`;
ALTER TABLE `agreements` ADD KEY `agreements_staff_id_foreign` (`staff_id`);
ALTER TABLE `agreements` ADD CONSTRAINT `agreements_staff_id_foreign` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`);

ALTER TABLE `agreements`
ADD `percentage_lower` DECIMAL
(8,2) NULL COMMENT 'Porcentaje acordado con el cliente para el capital del crédito inferior a $ 20.000.000' AFTER `legal_percentage`;
ALTER TABLE `agreements`
ADD `percentage_between` DECIMAL
(8,2) NULL COMMENT 'Porcentaje acordado con el cliente para el capital del crédito entre $20.000.001 y $50.000.000' AFTER `percentage_lower`;
ALTER TABLE `agreements`
ADD `percentage_higher` DECIMAL
(8,2) NULL COMMENT 'Porcentaje acordado con el cliente para el capital del crédito superior a $50.000.001' AFTER `percentage_between`;

CREATE TABLE `service_orders`
(
  `id` bigint
(20) UNSIGNED NOT NULL,
  `date` date NOT NULL COMMENT 'Fecha en la que se firma la orden se servicio',
  `number` varchar
(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Determina el número de orden de servicio, el cual debe ser la última mas 1',
  `agreement_id` bigint
(20) UNSIGNED DEFAULT NULL COMMENT 'Determina el acuerdo o contrato asociado a la orden se revicio (tabla => agreements)',
  `prejury_percentage` decimal
(8,2) NOT NULL COMMENT 'Porcentaje acordado con el cliente en la etapa Prejurídica',
  `legal_percentage` decimal
(8,2) DEFAULT NULL COMMENT 'Porcentaje acordado con el cliente en la etapa Jurídica',
  `smaller_amount` varchar
(1) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Es un valor de S/N y define en cada acuerdo si se aceptan valores de menor cuantía, solo el administrador del sistema debe poder habilitar esta opción. la menor cuantía debe definirse en parametros',
  `pre_written` varchar
(1) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Define en S/N si se acepta cartera preescrita esto lo define el administrador del sistema.  En parametros debemos definir las fechas de la cartera prescrita',
  `wallet_type_id` bigint
(20) UNSIGNED DEFAULT NULL COMMENT 'Determina el tipo de cartera asociada a la orden se revicio (tabla => parameters)',
  `customer_id` bigint
(20) UNSIGNED NULL COMMENT 'Cliente asociado a la orden de sercicio, relación a tabla customers',
  `staff_id` bigint
(20) UNSIGNED NULL COMMENT 'Comercial asociado a la orden de sercicio, relación a tabla staff',
  `description` text COLLATE utf8mb4_unicode_ci COMMENT 'Aquí se realizan los comentarios u observaciones',
  `status` varchar
(1) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'P: Por activar,	A: Activa, E: En gestión, F: Completada, C: Cancelada',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla para relacionar las ordenes de servicion con los contratos o acuerdos(agreements)';


-- PK
ALTER TABLE `service_orders`
ADD PRIMARY KEY
(`id`);
ALTER TABLE `service_orders` MODIFY `id` bigint
(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

-- /// foraneas /// --

-- fk agreement
ALTER TABLE `service_orders`
ADD KEY `service_orders_agreement_id_foreign`
(`agreement_id`);
ALTER TABLE `service_orders`
ADD CONSTRAINT `service_orders_agreement_id_foreign` FOREIGN KEY
(`agreement_id`) REFERENCES `agreements`
(`id`);

-- fk staff
ALTER TABLE `service_orders`
ADD KEY `service_orders_staff_id_foreign`
(`staff_id`);
ALTER TABLE `service_orders`
ADD CONSTRAINT `service_orders_staff_id_foreign` FOREIGN KEY
(`staff_id`) REFERENCES `staff`
(`id`);

-- fk customer
ALTER TABLE `service_orders`
ADD KEY `service_orders_customer_id_foreign`
(`customer_id`);
ALTER TABLE `service_orders`
ADD CONSTRAINT `service_orders_customer_id_foreign` FOREIGN KEY
(`customer_id`) REFERENCES `customers`
(`id`);

-- fk parameters
ALTER TABLE `service_orders`
ADD KEY `service_orders_wallet_type_id_foreign`
(`wallet_type_id`);
ALTER TABLE `service_orders`
ADD CONSTRAINT `service_orders_wallet_type_id_foreign` FOREIGN KEY
(`wallet_type_id`) REFERENCES `parameters`
(`id`);

-------------- PARAMETROS
INSERT INTO `t_p_parameters` (`
id`,
`name
`, `created_at`, `updated_at`) VALUES
('11', 'TIPO CARTERA', '2019-08-13 00:00:00', '2019-08-13 00:00:00');

INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Gran volumen', '11', 'A', '2019-08-13 00:00:00', '2019-08-13 00:00:00');
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Volúmenes menores', '11', 'A', '2019-08-13 00:00:00', '2019-08-13 00:00:00');

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `darNumeroOrden`
() RETURNS varchar
(10) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
BEGIN
  DECLARE numero_orden VARCHAR
  (10);

SELECT LPAD(COALESCE(MAX(s.number), 1) + 1, 10,'0')
into numero_orden
FROM service_orders s;

RETURN numero_orden;
END$$
DELIMITER ;

ALTER TABLE `customers`
ADD `sub_industries_id` BIGINT
(20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla sub_industries y determina la industria y subindustria a la cual pertenece el cliente o cuenta' AFTER `check_digit`;
ALTER TABLE `customers`
ADD KEY `customers_sub_industries_id_foreign`
(`sub_industries_id`);
ALTER TABLE `customers`
ADD CONSTRAINT `customers_sub_industries_id_foreign` FOREIGN KEY
(`sub_industries_id`) REFERENCES `sub_industries`
(`id`);


UPDATE `parameters`
SET
`name` = 'Llamada Inicial' WHERE `parameters`.`id` = 18;
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Llamada seguimiento', '6', 'A', '2019-08-28 00:00:00', '2019-08-28 00:00:00');

ALTER TABLE `customers`
DROP `phone`,
DROP `mobile`;
-- quitar los campos de la tabla de cliente

CREATE TABLE `address_mobiles`
(
  `id` bigint
(20) UNSIGNED NOT NULL,
  `address_id` bigint
(20) UNSIGNED NOT NULL COMMENT 'Dirección asociada al celular',
  `mobile` varchar
(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Número de celular de la dirección',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla para relacionar una dirección con varios celulares';

-- key's y foraneas
ALTER TABLE `address_mobiles`
ADD PRIMARY KEY
(`id`);
ALTER TABLE `address_mobiles` MODIFY `id` bigint
(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `address_mobiles`
ADD KEY `address_mobiles_address_id_foreign`
(`address_id`);
ALTER TABLE `address_mobiles`
ADD CONSTRAINT `address_mobiles_address_id_foreign` FOREIGN KEY
(`address_id`) REFERENCES `customer_addresses`
(`id`);


ALTER TABLE `users`
ADD `type` BIGINT
(20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla parámetros y determina el tipo de usuario, tales como: Funcionario y Cliente' AFTER `status`;
ALTER TABLE `users`
ADD KEY `users_type_foreign`
(`type`);
ALTER TABLE `users`
ADD CONSTRAINT `users_type_foreign` FOREIGN KEY
(`type`) REFERENCES `parameters`
(`id`);

INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Funcionario', '12', 'A', '2019-09-04 00:00:00', '2019-09-04 00:00:00');

INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Cliente', '12', 'A', '2019-09-04 00:00:00', '2019-09-04 00:00:00');

-- custom_fields
CREATE TABLE `custom_fields`
(
  `id` bigint
(20) UNSIGNED NOT NULL,
  `key` varchar
(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nombre interno en la base de datos',
  `name` varchar (100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nombre del campo',
  `type` bigint
(20) UNSIGNED DEFAULT NULL COMMENT 'Determina el tipo de campo (tabla => parameters)',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla para relacionar las ordenes de servicion con los contratos o acuerdos(agreements)';

-- PK
ALTER TABLE `custom_fields`
ADD PRIMARY KEY
(`id`);
ALTER TABLE `custom_fields` MODIFY `id` bigint
(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

-- fk parameters
ALTER TABLE `custom_fields`
ADD KEY `custom_fields_type_foreign`
(`type`);
ALTER TABLE `custom_fields`
ADD CONSTRAINT `custom_fields_type_foreign` FOREIGN KEY
(`type`) REFERENCES `parameters`
(`id`);

---- PARAMETROS
INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Texto', '13', 'A', '2019-10-04 00:00:00', '2019-10-04 00:00:00');

INSERT INTO `parameters` (`
id`,
`name
`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES
(NULL, 'Númerico', '13', 'A', '2019-10-04 00:00:00', '2019-10-04 00:00:00');

ALTER TABLE `custom_fields`
ADD `customer_id` BIGINT
(20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla customers y determina la cuenta aspciada' AFTER `type`;
ALTER TABLE `custom_fields`
ADD KEY `custom_fields_customer_id_foreign`
(`customer_id`);
ALTER TABLE `custom_fields`
ADD CONSTRAINT `custom_fields_customer_id_foreign` FOREIGN KEY
(`customer_id`) REFERENCES `customers`
(`id`);

ALTER TABLE `customers`
ADD `check_field` varchar
(1) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'P: Por activar,	A: Activa, E: En gestión, F: Completada, C: Cancelada';

-- RELACIÓN DE LA DIRECCIÓN CON EL DEUDOR

ALTER TABLE `customer_addresses` ADD `debtor_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla debtors y determina el deudor asociado' AFTER `customer_id`;
ALTER TABLE `customer_addresses` ADD KEY `customer_addresses_debtor_id_foreign` (`debtor_id`);
ALTER TABLE `customer_addresses` ADD CONSTRAINT `customer_addresses_debtor_id_foreign` FOREIGN KEY (`debtor_id`) REFERENCES `debtors` (`id`);

ALTER TABLE `customer_addresses` CHANGE `customer_id` `customer_id` BIGINT (20) UNSIGNED NULL COMMENT 'Cliente o cuenta asociado a la dirección, relación a tabla customers';


ALTER TABLE `customer_contacts`
ADD `debtor_id` BIGINT
(20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla debtors y determina el deudor asociado' AFTER `customer_id`;
ALTER TABLE `customer_contacts`
ADD KEY `contacts_debtor_id_foreign`
(`debtor_id`);
ALTER TABLE `customer_contacts`
ADD CONSTRAINT `contacts_debtor_id_foreign` FOREIGN KEY
(`debtor_id`) REFERENCES `debtors`
(`id`);


ALTER TABLE `task_managements`
ADD `debtor_id` BIGINT
(20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla debtors y determina el deudor asociado' AFTER `customer_id`;
ALTER TABLE `task_managements`
ADD KEY `task_managements_debtor_id_foreign`
(`debtor_id`);
ALTER TABLE `task_managements`
ADD CONSTRAINT `task_managements_debtor_id_foreign` FOREIGN KEY
(`debtor_id`) REFERENCES `debtors`
(`id`);

ALTER TABLE `task_managements`
ADD `privacy` BIGINT
(20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla parameters y determina la privacidad' AFTER `debtor_id`;
ALTER TABLE `task_managements`
ADD KEY `task_managements_privacy_foreign`
(`privacy`);
ALTER TABLE `task_managements`
ADD CONSTRAINT `task_managements_privacy_foreign` FOREIGN KEY
(`privacy`) REFERENCES `parameters`
(`id`);


ALTER TABLE `invoices`
ADD `user_id` BIGINT
(20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla users y determina quien realizó la factura' AFTER `debtor_id`;
ALTER TABLE `invoices`
ADD KEY `invoices_user_id_foreign`
(`user_id`);
ALTER TABLE `invoices`
ADD CONSTRAINT `invoices_user_id_foreign` FOREIGN KEY
(`user_id`) REFERENCES `users`
(`id`);


CREATE TABLE `payment_agreements`
(
  `id` bigint
(20) UNSIGNED NOT NULL,
  `debtor_id` bigint
(20) UNSIGNED NOT NULL COMMENT 'Deudor asociado al acuerdo o abono, relación a tabla customers',
  `name` varchar
(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nombres de contacto',
  `last_name` varchar
(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Apellidos de contacto',
  `ocupacion` varchar
(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Cargo u Ocupación del contacto',
  `mail` varchar
(50) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Correo de contacto',
  `phone` varchar
(20) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Teléfono fijo de contacto',
  `mobile` varchar
(20) COLLATE utf8mb4_unicode_ci  NULL COMMENT 'Teléfono celular',
  `created_at` timestamp NULL ,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci OMMENT='Tabla para relacionar los contactos que tiene el cliente para comunicarse';

-- PK
ALTER TABLE `payment_agreements`
ADD PRIMARY KEY
(`id`);
ALTER TABLE `payment_agreements` MODIFY `id` bigint
(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

-- fk parameters
ALTER TABLE `payment_agreements`
ADD KEY `custom_fields_type_foreign`
(`type`);
ALTER TABLE `payment_agreements`
ADD CONSTRAINT `custom_fields_type_foreign` FOREIGN KEY
(`type`) REFERENCES `parameters`
(`id`);

CREATE TABLE `time_intervals`
(
  `user_id` BIGINT (20) UNSIGNED NULL,
  `interval_start` timestamp NOT NULL,
  `interval_end` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci OMMENT='Tabla para guardar temporalmente los rangos de fechas para los acuerdos de pago programados';

CREATE TABLE `debtor_settlements`
(
`id` bigint (20) UNSIGNED NOT NULL,
`date` date NOT NULL COMMENT 'Fecha en la que se hace el cierre de la deuda',
`debtor_id` bigint (20) UNSIGNED NOT NULL COMMENT 'Deudor asociado, relación a tabla debtors',
`wallet_type_name` varchar (50) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Tipo de cartera',
`invoice_count` int (11) NOT NULL COMMENT 'Cantidad de facturas por cobrar al deudor',
`capital` double NOT NULL COMMENT 'Capital total en facturas',
`initial_payments` double NOT NULL COMMENT 'Abonos iniciales realizados al cliente',
`capital_recovered` double NOT NULL COMMENT 'Capital recuperado del la deuda',
`interests_recovered` double NOT NULL COMMENT 'Intereses recuperado del la deuda',
`fees_recovered` double NOT NULL COMMENT 'Honorarios recuperado del la deuda',
`discount` double NOT NULL COMMENT 'Porcentaje de descuento realizado al cerrar la deuda',
`created_at` timestamp NULL ,
`updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla para guardar la liquidación de la deuda al momento de cerrarla';

ALTER TABLE `debtor_settlements`
ADD `user_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla users y determina quien realizó la gestion' AFTER `discount`;
ALTER TABLE `debtor_settlements` ADD KEY `debtor_settlements_user_id_foreign` (`user_id`);
ALTER TABLE `debtor_settlements` ADD CONSTRAINT `debtor_settlements_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `debtor_settlements` ADD PRIMARY KEY (`id`);
ALTER TABLE `debtor_settlements` MODIFY `id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `debtor_settlements` ADD `interest` double NOT NULL COMMENT 'Intereses generados hasta la fecha de cierre de la deuda' AFTER `capital`;
ALTER TABLE `debtor_settlements` ADD `fee` double NOT NULL COMMENT 'Honorarios generados hasta la fecha de cierre de la deuda' AFTER `interest`;

CREATE TABLE `months`
(
  `id` bigint (20) UNSIGNED NOT NULL COMMENT 'Identificador',
  `code` varchar (2) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Código del mes',
  `name` varchar (100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Nombre del mes',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Aqui se guarda la configuración de los meses.';

CREATE TABLE `reports`
(
  `user_id` bigint (20) UNSIGNED NOT NULL COMMENT 'usuario que hace el reporte',
  `type` varchar (20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Tipo de reporte',
  `value1` varchar (100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `value2` varchar (100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `value3` varchar (100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `value4` varchar (100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `value5` varchar (100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Aqui se guarda los reportes que se puedan generar.';

INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (1, '01', 'Enero', '2020-01-09 08:00:00', '2020-01-09 08:00:00');
INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (2, '02', 'Febrero', '2020-01-09 08:00:00', '2020-01-09 08:00:00');
INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (3, '03', 'Marzo', '2020-01-09 08:00:00', '2020-01-09 08:00:00');
INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (4, '04', 'Abril', '2020-01-09 08:00:00', '2020-01-09 08:00:00');
INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (5, '05', 'Mayo', '2020-01-09 08:00:00', '2020-01-09 08:00:00');
INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (6, '06', 'Junio', '2020-01-09 08:00:00', '2020-01-09 08:00:00');
INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (7, '07', 'Julio', '2020-01-09 08:00:00', '2020-01-09 08:00:00');
INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (8, '08', 'Agosto', '2020-01-09 08:00:00', '2020-01-09 08:00:00');
INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (9, '09', 'Septiembre', '2020-01-09 08:00:00', '2020-01-09 08:00:00');
INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (10, '10', 'Octubre', '2020-01-09 08:00:00', '2020-01-09 08:00:00');
INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (11, '11', 'Noviembre', '2020-01-09 08:00:00', '2020-01-09 08:00:00');
INSERT INTO `months` (`id`, `code`, `name`, `created_at`, `updated_at`) VALUES (12, '12', 'Diciembre', '2020-01-09 08:00:00', '2020-01-09 08:00:00');

ALTER TABLE `payments` ADD `process_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla procesos' AFTER `status`;
ALTER TABLE `payments` ADD KEY `payments_process_id_foreign` (`process_id`);
ALTER TABLE `payments` ADD CONSTRAINT `payments_process_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`);

CREATE TABLE `branch_offices`
(
  `id` bigint (20) UNSIGNED NOT NULL,
  `name` varchar (200) NOT NULL COMMENT 'Nombre de la sucursal',
  `city_id` bigint (20) UNSIGNED NOT NULL COMMENT 'Ciudad asociada al la sucursal',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla para guardar las sucursales';

-- key's y foraneas
ALTER TABLE `branch_offices` ADD PRIMARY KEY (`id`);
ALTER TABLE `branch_offices` MODIFY `id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `branch_offices` ADD KEY `branch_offices_city_id_foreign` (`city_id`);
ALTER TABLE `branch_offices` ADD CONSTRAINT `branch_offices_city_id_foreign` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`);

ALTER TABLE `users` ADD `branch_office_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla branch_offices' AFTER `user_type`;
ALTER TABLE `users` ADD KEY `users_branch_office_id_foreign` (`branch_office_id`);
ALTER TABLE `users` ADD CONSTRAINT `users_branch_office_id_foreign` FOREIGN KEY (`branch_office_id`) REFERENCES `branch_offices` (`id`);

ALTER TABLE `customers` ADD `branch_office_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla branch_offices' AFTER `check_field`;
ALTER TABLE `customers` ADD KEY `customers_branch_office_id_foreign` (`branch_office_id`);
ALTER TABLE `customers` ADD CONSTRAINT `customers_branch_office_id_foreign` FOREIGN KEY (`branch_office_id`) REFERENCES `branch_offices` (`id`);

CREATE TABLE `currencies`
(
  `id` bigint (20) UNSIGNED NOT NULL,
  `type` bigint (20) UNSIGNED NOT NULL COMMENT 'Tipo de divisa asociada',
  `date` timestamp NOT NULL COMMENT 'Fecha y hora en la cual se registra',
  `value` double NOT NULL COMMENT 'Valor del cambio de la divisa en COP',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla para guardar las divisas';

-- key's y foraneas
ALTER TABLE `currencies` ADD PRIMARY KEY (`id`);
ALTER TABLE `currencies` MODIFY `id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `currencies` ADD KEY `currencies_type_foreign` (`type`);
ALTER TABLE `currencies` ADD CONSTRAINT `currencies_type_foreign` FOREIGN KEY (`type`) REFERENCES `parameters` (`id`);

INSERT INTO `t_p_parameters` (`id`, `name`, `created_at`, `updated_at`) VALUES ('19', 'TIPO DIVISA', '2020-12-15 17:11:40', '2020-12-15 17:11:40');

INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'Peso colombiano (COP)', '19', 'A', '2020-12-15 17:11:40', '2020-12-15 17:11:40');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'Dólar estadounidense (USD)', '19', 'A', '2020-12-15 17:11:40', '2020-12-15 17:11:40');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'Peso chileno (CLP)', '19', 'A', '2020-12-15 17:11:40', '2020-12-15 17:11:40');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'Peso mexicano (MXN)', '19', 'A', '2020-12-15 17:11:40', '2020-12-15 17:11:40');

ALTER TABLE `invoices` ADD `currency_id` BIGINT(20) UNSIGNED NOT NULL COMMENT 'Divisa que se registra al momento de crear la factura' AFTER `total`, ADD `currency_total` DOUBLE NOT NULL COMMENT 'Valor total en la Divisa que se registra al momento de crear la factura.' AFTER `currency_id`;

ALTER TABLE `invoices` ADD `currency_payment` DOUBLE NOT NULL COMMENT 'Valor del abono al cliente en la Divisa que se registra al momento de crear la factura.' AFTER `payment`;

ALTER TABLE `invoices` ADD KEY `invoices_currency_id_foreign` (`currency_id`);
ALTER TABLE `invoices` ADD CONSTRAINT `invoices_currency_id_foreign` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`);

ALTER TABLE `debtors` ADD `currency_type` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla parameters, determina que tipo de divisa usa' AFTER `observation`;
ALTER TABLE `debtors` ADD KEY `debtors_currency_type_foreign` (`currency_type`);
ALTER TABLE `debtors` ADD CONSTRAINT `debtors_currency_type_foreign` FOREIGN KEY (`currency_type`) REFERENCES `parameters` (`id`);

ALTER TABLE `payments` ADD `currency_id` BIGINT(20) UNSIGNED NULL COMMENT 'Divisa que se registra al momento de crear el abono' AFTER `observation`;
ALTER TABLE `payments` ADD `currency_value` DOUBLE NULL COMMENT 'Valor del abono al cliente en la Divisa que se registra al momento de crear la factura.' AFTER `currency_id`;
ALTER TABLE `payments` ADD KEY `payments_currency_id_foreign` (`currency_id`);
ALTER TABLE `payments` ADD CONSTRAINT `payments_currency_id_foreign` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`);

CREATE TABLE `judgeds`
(
  `id` bigint (20) UNSIGNED NOT NULL,
  `name` varchar (250) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nombres de contacto',
  `address` varchar (250) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Apellidos de contacto',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla guardar los juzgados';

ALTER TABLE `judgeds` ADD PRIMARY KEY (`id`);
ALTER TABLE `judgeds` MODIFY `id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `debtors` ADD `probability` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'A: Alta, M: Media, B: Baja' AFTER `_created_at`;


CREATE TABLE `demands`
(
  `id` bigint (20) UNSIGNED NOT NULL,
  `number` varchar (10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Número interno de la demanda',
  `number_filed` varchar (10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Número de radicado de la demanda',
  `date` date NOT NULL COMMENT 'Fecha del la demanda',
  `judged_id` bigint (20) UNSIGNED NOT NULL COMMENT 'Juzgado donde se presenta la demanda',
  `user_id` bigint (20) UNSIGNED NOT NULL COMMENT 'Usuario que crea la demanda',
  `precautionary_measure` varchar (250) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Medidas cautelares',
  `status` varchar (1) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'P: Presentada,	R: Rechazada',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla guardar las demandas';

ALTER TABLE `demands` ADD PRIMARY KEY (`id`);
ALTER TABLE `demands` MODIFY `id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
-- JUZGADO
ALTER TABLE `demands` ADD KEY `demands_judged_id_foreign` (`judged_id`);
ALTER TABLE `demands` ADD CONSTRAINT `demands_judged_id_foreign` FOREIGN KEY (`judged_id`) REFERENCES `judgeds` (`id`);


-- USUARIO
ALTER TABLE `demands` ADD KEY `demands_user_id_foreign` (`user_id`);
ALTER TABLE `demands` ADD CONSTRAINT `demands_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

-- PONER EN EL JUZGDO
ALTER TABLE `judgeds` ADD `city_id` BIGINT (20) UNSIGNED NULL COMMENT 'Ciudad del juzgado' AFTER `number`;

-- CIUDAD
ALTER TABLE `judgeds` ADD KEY `judgeds_city_id_foreign` (`city_id`);
ALTER TABLE `judgeds` ADD CONSTRAINT `judgeds_city_id_foreign` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`);
-- DEMANDA
ALTER TABLE `demands` ADD `debtor_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla debtors y determina el deudor asociado' AFTER `date`;
ALTER TABLE `demands` ADD KEY `demands_debtor_id_foreign` (`debtor_id`);
ALTER TABLE `demands` ADD CONSTRAINT `demands_debtor_id_foreign` FOREIGN KEY (`debtor_id`) REFERENCES `debtors` (`id`);
-- STAFF
ALTER TABLE `demands` ADD `staff_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla staff y determina el staff asociado' AFTER `user_id`;
ALTER TABLE `demands` ADD KEY `demands_staff_id_foreign` (`staff_id`);
ALTER TABLE `demands` ADD CONSTRAINT `demands_staff_id_foreign` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`);

ALTER TABLE `documents` ADD `process_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con el proceso asociado' AFTER `created_at`;
ALTER TABLE `documents` ADD KEY `documents_staff_id_foreign` (`process_id`);
ALTER TABLE `documents` ADD CONSTRAINT `documents_staff_id_foreign` FOREIGN KEY (`process_id`) REFERENCES `processes` (`id`);

-- GESTIONES DE LA DEMANDA
CREATE TABLE `demand_management` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `number_filed` varchar (10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Número de radicado de la gestión',
  `date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Fecha y hora de realización de la gestión ',
  `type` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Se relaciona con la tabla parameters (tipo de gestión)',
  `demand_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Relacionada con la demanda o demands',
  `management` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Detalle de la gestión realizada',
  `privacy` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Se relaciona con la tabla parameters y determina la privacidad',
  `user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Se relaciona con la tabla users y determina quien realizó la gestion',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Aqui se lleva el control de las gestiones sobre las demandas.';

-- PK
ALTER TABLE `demand_management` ADD PRIMARY KEY (`id`);
ALTER TABLE `demand_management` MODIFY `id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
-- DEMANDA
ALTER TABLE `demand_management` ADD KEY `demand_management_demand_id_foreign` (`demand_id`);
ALTER TABLE `demand_management` ADD CONSTRAINT `demand_management_demand_id_foreign` FOREIGN KEY (`demand_id`) REFERENCES `demands` (`id`);
-- USUARIO
ALTER TABLE `demand_management` ADD KEY `demand_management_user_id_foreign` (`user_id`);
ALTER TABLE `demand_management` ADD CONSTRAINT `demand_management_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
-- TIPO DE GESTIÓN
ALTER TABLE `demand_management` ADD KEY `demand_management_type_foreign` (`type`);
ALTER TABLE `demand_management` ADD CONSTRAINT `demand_management_type_foreign` FOREIGN KEY (`type`) REFERENCES `parameters` (`id`);

ALTER TABLE `demands` ADD `demand_type` BIGINT (20) UNSIGNED NULL COMMENT 'Se determina el tipo de demanda' AFTER `number_filed`;
ALTER TABLE `demands` ADD KEY `demands_demand_type_foreign` (`demand_type`);
ALTER TABLE `demands` ADD CONSTRAINT `demands_demand_type_foreign` FOREIGN KEY (`demand_type`) REFERENCES `parameters` (`id`);

CREATE TABLE `demand_judged`
(
  `id` bigint (20) UNSIGNED NOT NULL,
  `demand_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Relacionada con la demanda o demands',
  `old_judged_id` bigint (20) UNSIGNED NULL COMMENT 'Juzgado anterior donde se presenta la demanda',
  `new_judged_id` bigint (20) UNSIGNED NOT NULL COMMENT 'Juzgado nuevo donde se presenta la demanda',
  `operation` varchar (10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Operacion sobre la tabla demanda',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla guardar los juzgados';

ALTER TABLE `demand_judged` ADD PRIMARY KEY (`id`);
ALTER TABLE `demand_judged` MODIFY `id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `demand_judged` ADD KEY `demand_judged_demand_id_foreign` (`demand_id`);
ALTER TABLE `demand_judged` ADD CONSTRAINT `demand_judged_demand_id_foreign` FOREIGN KEY (`demand_id`) REFERENCES `demands` (`id`);

ALTER TABLE `demand_judged` ADD KEY `demand_judged_old_judged_id_foreign` (`old_judged_id`);
ALTER TABLE `demand_judged` ADD CONSTRAINT `demand_judged_old_judged_id_foreign` FOREIGN KEY (`old_judged_id`) REFERENCES `judgeds` (`id`);

ALTER TABLE `demand_judged` ADD KEY `demand_judged_new_judged_id_foreign` (`new_judged_id`);
ALTER TABLE `demand_judged` ADD CONSTRAINT `demand_judged_new_judged_id_foreign` FOREIGN KEY (`new_judged_id`) REFERENCES `judgeds` (`id`);

-- DISPARADORES

DELIMITER $$
  CREATE TRIGGER create_demand_tgr
  AFTER INSERT ON demands FOR EACH ROW
   BEGIN
    INSERT INTO `demand_judged` (`id`, `demand_id`, `old_judged_id`, `new_judged_id`, `operation`, `updated_at`)
	VALUES (NULL, NEW.id, NULL, NEW.judged_id,'CREATE', NOW());
END $$


DELIMITER $$
  CREATE TRIGGER update_demand_tgr
  AFTER UPDATE ON demands FOR EACH ROW
   BEGIN
    INSERT INTO `demand_judged` (`id`, `demand_id`, `old_judged_id`, `new_judged_id`, `operation`, `updated_at`)
	VALUES (NULL, NEW.id, OLD.judged_id, NEW.judged_id,'CREATE', NOW());
END $$

-- TABLA ESTUDIO DE BIENES
CREATE TABLE `study_goods`
(
  `id` bigint (20) UNSIGNED NOT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Fecha y hora de realización del estudio ',
  `debtor_id` bigint (20) UNSIGNED NOT NULL COMMENT 'Deudor asociado, relación a tabla debtors',
  `description` varchar (250) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Comentarios',
  `status` varchar (1) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'P: Positivo, N: Negativo',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla guardar los estudios de bienes';

ALTER TABLE `study_goods` ADD PRIMARY KEY (`id`);
ALTER TABLE `study_goods` MODIFY `id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `study_goods` ADD KEY `study_goods_debtor_id_foreign` (`debtor_id`);
ALTER TABLE `study_goods` ADD CONSTRAINT `study_goods_debtor_id_foreign` FOREIGN KEY (`debtor_id`) REFERENCES `debtors` (`id`);


-- USUARIOS
ALTER TABLE `study_goods`
ADD `user_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla users y determina quien realizó el estudio' AFTER `status`;
ALTER TABLE `study_goods` ADD KEY `study_goods_user_id_foreign` (`user_id`);
ALTER TABLE `study_goods` ADD CONSTRAINT `study_goods_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

-- TIPOS DE MEDIDAS CAUTELARES
INSERT INTO `t_p_parameters` (`id`, `name`, `created_at`, `updated_at`) VALUES ('24', 'TIPOS DE MEDIDAS CAUTELARES', '2021-06-02 18:48:43', '2021-06-02 18:48:43')

INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'Cuenta bancaria', '24', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'Vehiculo', '24', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'Inmueble', '24', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'Establecimiento de comercio', '24', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'Salario', '24', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'Contratos', '24', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');

-- BANCOS

INSERT INTO `t_p_parameters` (`id`, `name`, `created_at`, `updated_at`) VALUES ('25', 'LISTA DE BANCOS', '2021-06-02 18:48:43', '2021-06-02 18:48:43');

INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'DAVIVIENDA', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'BANCO DE BOGOTÁ', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'BANCO POPULAR', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'BBVA', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'BANCO SANTANDER', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'BANCO AGRARIO', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'HELM BANK', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'CITY BANK', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'BANCO AV VILLAS ', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'BANCO UDAMERIS', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'BANCO CAJA SOCIAL', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'BANCO OCCIDENTE', '25', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');

-- TIPO DE INMUEBLE (LOTE, CONSTRUCCIÓN, APARTAMENTO, CASA) COMENTARIO

INSERT INTO `t_p_parameters` (`id`, `name`, `created_at`, `updated_at`) VALUES ('26', 'TIPO DE INMUEBLE', '2021-06-02 18:48:43', '2021-06-02 18:48:43');

INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'LOTE', '26', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'CONSTRUCCIÓN', '26', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'APARTAMENTO', '26', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'CASA', '26', 'A', '2021-06-02 18:48:43', '2021-06-02 18:48:43');

--MEDIDAS CAUTELARES cautions
CREATE TABLE `cautions`
(
  `id` bigint (20) UNSIGNED NOT NULL,
  `type` BIGINT (20) UNSIGNED NOT NULL COMMENT 'Tipo de medida cautelar, relación a tabla parameters',
  `bank_id` bigint (20) UNSIGNED NULL COMMENT 'Bancos, relación a tabla parameters',
  `property_type_id` bigint (20) UNSIGNED NULL COMMENT 'Tipo de inmueble, relación a tabla parameters',
  `brand` varchar (250) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Maca vehiculo',
  `model` varchar (250) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Modelo vehiculo',
  `line` varchar (250) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Linea vehiculo',
  `registration_number` varchar (20) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Número de registro inmueble',
  `name` varchar (250) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Nombre establecimiento',
  `address` varchar (250) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Direccion de inmueble o establecimiento',
  `entity` varchar (250) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Entidad para el salario o el contrato',
  `description` varchar (250) COLLATE utf8mb4_unicode_ci NULL COMMENT 'Comentarios',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla guardar las medidas cautelares';


ALTER TABLE `cautions` ADD PRIMARY KEY (`id`);
ALTER TABLE `cautions` MODIFY `id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `cautions` ADD KEY `cautions_type_foreign` (`type`);
ALTER TABLE `cautions` ADD CONSTRAINT `cautions_type_foreign` FOREIGN KEY (`type`) REFERENCES `parameters` (`id`);

ALTER TABLE `cautions` ADD KEY `cautions_bank_id_foreign` (`bank_id`);
ALTER TABLE `cautions` ADD CONSTRAINT `cautions_bank_id_foreign` FOREIGN KEY (`bank_id`) REFERENCES `parameters` (`id`);

ALTER TABLE `cautions` ADD KEY `cautions_property_type_id_foreign` (`property_type_id`);
ALTER TABLE `cautions` ADD CONSTRAINT `cautions_property_type_id_foreign` FOREIGN KEY (`property_type_id`) REFERENCES `parameters` (`id`);

ALTER TABLE `cautions` ADD `user_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla users y determina quien la realizó' AFTER `description`;
ALTER TABLE `cautions` ADD KEY `cautions_user_id_foreign` (`user_id`);
ALTER TABLE `cautions` ADD CONSTRAINT `cautions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `cautions` ADD `debtor_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla debtors' AFTER `user_id`;
ALTER TABLE `cautions` ADD KEY `cautions_debtor_id_foreign` (`debtor_id`);
ALTER TABLE `cautions` ADD CONSTRAINT `cautions_debtor_id_foreign` FOREIGN KEY (`debtor_id`) REFERENCES `debtors` (`id`);

-- GESTIONES DE LA MEDIDA CAUTELAR
CREATE TABLE `caution_management` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Fecha y hora de realización de la gestión ',
  `caution_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Relacionada con la medida cautelar',
  `management` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Detalle de la gestión realizada',
  `user_id` bigint(20) UNSIGNED DEFAULT NULL COMMENT 'Se relaciona con la tabla users y determina quien realizó la gestion',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Aqui se lleva el control de las gestiones sobre las medidas cautelares.';

-- PK
ALTER TABLE `caution_management` ADD PRIMARY KEY (`id`);
ALTER TABLE `caution_management` MODIFY `id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
-- DEMANDA
ALTER TABLE `caution_management` ADD KEY `caution_management_caution_id_foreign` (`caution_id`);
ALTER TABLE `caution_management` ADD CONSTRAINT `caution_management_caution_id_foreign` FOREIGN KEY (`caution_id`) REFERENCES `cautions` (`id`);
-- USUARIO
ALTER TABLE `caution_management` ADD KEY `caution_management_user_id_foreign` (`user_id`);
ALTER TABLE `caution_management` ADD CONSTRAINT `caution_management_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

ALTER TABLE `cautions` ADD `status` varchar (1) COLLATE utf8mb4_unicode_ci NULL COMMENT 'P: Positivo, N: Negativo'  AFTER `user_id`;

-- NUEVAS GESTIONES JURIDICAS
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'ADMITE REORGANIZACIÓN', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'RADICACIÓN SOLICITUD RECONOCIMIENTO DE ACREENCIA', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'PRESENTACIÓN PROYECTO DE CALIFICACIÓN Y GRADUACIÓN DE CRÉDITOS', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'PRESENTACIÓN DE OBJECIONES', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'AUDIENCIA PARA RESOLVER OBJECIONES', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'AUDIENCIA CELEBRACIÓN DE ACUERDO DE REORNANIZACIÓN', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'RESOLUCIÓN RECURSO DE REPOSICIÓN', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'RESULTADO DE LAS NOTIFICACIONES', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'SE NOTIFICA EL DEMANDADO', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'DEMANDADO PROPONE RECURSO DE REPOSICIÓN', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'CONTESTACIÓN A LA DEMANDA', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'DEMANDADO PROPONE EXCEPCIONES', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'SE DESCORREN TRASLADO DE LAS EXCEPCIONES', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'DESPACHO', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'GESTIÓN PARA EXTRAJURÍDICO', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'RESULTADO DEL RECURSO', '21', 'A', '2021-08-24 11:55:00', '2021-08-24 11:55:00');

-- CAPITALIZAR
UPDATE parameters set name = CONCAT(UCASE(LEFT(name, 1)), LCASE(SUBSTRING(name, 2))) WHERE tp_parameter_id = 21;

ALTER TABLE `demands` CHANGE `number_filed` `number_filed` VARCHAR(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT 'Número de radicado de la demanda';
ALTER TABLE `cautions` CHANGE `description` `description` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT 'Comentarios';
ALTER TABLE `demands` CHANGE `precautionary_measure` `precautionary_measure` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Medidas cautelares';
INSERT INTO `parameters` (`id`, `name`, `tp_parameter_id`, `value`, `status`, `created_at`, `updated_at`) VALUES (NULL, 'Otra', '24', NULL, 'A', '2021-10-12 21:29:55', '2021-10-12 21:29:55');

----------------------------------------------------------------------------------------------------------------------------------------------------------------
-- HASTA AQUÍ YA SE APLICARON LOS CAMBIOS A LA BASE DE DATOS



-- CARTAS
CREATE TABLE `letters`
(
  `id` bigint (20) UNSIGNED NOT NULL,
  `type` varchar (1) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '1: Carta 1, 2: Carta 2, 3: Carta 3',
  `date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Fecha y hora del envio de la carta',
  `debtor_id` bigint (20) UNSIGNED NOT NULL COMMENT 'Deudor asociado, relación a tabla debtors',
  `description` varchar (250) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Comentarios',
  `status` varchar (1) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'A: Activa, I: Inactiva',
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabla guardar las cartas';

ALTER TABLE `letters` ADD PRIMARY KEY (`id`);
ALTER TABLE `letters` MODIFY `id` bigint (20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

ALTER TABLE `letters` ADD KEY `letters_debtor_id_foreign` (`debtor_id`);
ALTER TABLE `letters` ADD CONSTRAINT `letters_debtor_id_foreign` FOREIGN KEY (`debtor_id`) REFERENCES `debtors` (`id`);

ALTER TABLE `letters` ADD `user_id` BIGINT (20) UNSIGNED NULL COMMENT 'Se relaciona con la tabla users y determina quien la realizó' AFTER `status`;
ALTER TABLE `letters` ADD KEY `letters_user_id_foreign` (`user_id`);
ALTER TABLE `letters` ADD CONSTRAINT `letters_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);