/*1.  Crear cuentas para VISA y Master con 50 y 80 millones respectivamente*/

INSERT INTO `cuentas` (`id_cuenta`, `numero`, `saldo`, `id_cliente`) VALUES
  (5,'005',50000000.000,5),
  (6,'006',80000000.000,6);

COMMIT;


/*2.  Adicionar un atributo clasificación a cada transacción para saber si es ingreso o egreso, actualizarlo al valor que le corresponda*/

UPDATE
  `transacciones`
SET
  `transacciones`.`data` = JSON_SET(`transacciones`.`data`, '$.clasificacion', 'ingreso')
WHERE
  `transacciones`.`id_tipo` = 1 ;
commit;

UPDATE
  `transacciones`
SET
  `transacciones`.`data` = JSON_SET(`transacciones`.`data`, '$.clasificacion', 'egreso')
WHERE
  `transacciones`.`id_tipo` = 2 or `transacciones`.`id_tipo`= 3  or `transacciones`.`id_tipo`= 4;
commit;


/*3.  Consignación del salario para pedro por $1.950.000*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{ "comentario":"Consignación", "detalle":"Pago de salario" }',1,1950000,1);
COMMIT;

/*4. Consignación en efectivo para María en sucursal Terra Plaza por $600.000, realizada por Juan Perez, cedula 76.346.890
*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Consignación",
  "detalle":"Pago  en efectivo",
  "ciudad":"Popayán",
  "sucursal":{"lugar":"Terra Plaza", "ubicacion":{"x": 2.4868081, "y": -76.5625747}},
  "realiza":{"nombre":"Juan Perez", "cedula":"76346890"}
}
',3,600000,1);
COMMIT;

/*5. Consignación en efectivo para Pedro en sucursal Terra Plaza por $400.000, realizada por Marcos Solarte, cedula 76.254.187
*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Consignación",
  "detalle":"Pago  en efectivo",
  "ciudad":"Popayán",
  "sucursal":{"lugar":"Terra Plaza", "ubicacion":{"x": 2.4868081, "y": -76.5625747}},
  "realiza":{"nombre":"Marcos Solarte", "cedula":"76254187"}
}
',3,400000,1);
COMMIT;

/*6.  Consignación en efectivo para Pablo en sucursal Terra Plaza por $350.000, realizada por Andres Gomez, cedula 76.984.561*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Consignación",
  "detalle":"Pago  en efectivo",
  "ciudad":"Popayán",
  "sucursal":{"lugar":"Terra Plaza", "ubicacion":{"x": 2.4868081, "y": -76.5625747}},
  "realiza":{"nombre":"Andres Gomez", "cedula":"76346890"}
}
',2,350000,1);
COMMIT;

/*7.  Pago de servicio de acueducto de María por $69.500*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Pago",
  "detalle":"Pago  en servicio de Acueducto",
  "realiza":{"nombre":"Maria", "cuenta":"003"}
}
',4,69500,4);
COMMIT;

/*8.  Pago de tarjeta de crédito Visa de Pablo por valor de $200.000*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Pago",
  "detalle":"Pago de tarjeta de credito",
  "realiza":{"nombre":"Pablo", "cuenta":"002"}
}
',5,200000,4);
COMMIT;

/*9.  Pago en restaurante por Pedro por valor de $35.000*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Pago",
  "detalle":"Pago  en restaurante",
  "cuenta":{"numero":"1xxx", "titular":"Restaurate Petro"},
  "realiza":{"nombre":"Pedro", "cuenta":"001"}
}
',null,350000,4);
COMMIT;

/*10.Retiro en cajero electrónico código ATM345, ubicado en Calle 5 con Cra 4 Popayán, por valor de $100.000, desde la cuenta 003*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"RETIRO",
  "detalle":"Retiro en cajero electrónico",
  "ciudad":"Popayán",
  "retiro":{"medio":"cajero","cajero":{"tipo":"ATH", "codigo":"ATM345"}},
  "sucursal":{"lugar":"Calle 5 con Cra 4", "ubicacion":{"x": 2.4410333, "y": -76.6070543}}
}
',3,100000,2);
COMMIT;

/*11. Retiro en cajero electrónico código ATM312, ubicado en Calle 8 con Cra 2 Popayán, por valor de $150.000, desde la cuenta 002*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"RETIRO",
  "detalle":"Retiro en cajero electrónico",
  "ciudad":"Popayán",
  "retiro":{"medio":"cajero","cajero":{"tipo":"ATH", "codigo":"ATM312"}},
  "sucursal":{"lugar":"Calle 8 con Cra 2", "ubicacion":{"x": 2.4374374, "y": -76.6056817}}
}
',2,150000,2);
COMMIT;

/*12.  Comisión  por uso de cajero de $1.800 por cada transacción*/

UPDATE
  `transacciones`
SET
  `transacciones`.`data` = JSON_SET(`transacciones`.`data`, '$.comision', 1800)
WHERE
  `transacciones`.`data`->" $.retiro.medio" = 'cajero';
commit;

/*13.  Pago de tarjeta de crédito Master de María por $80.000 desde la APP del banco, desde El Big Ben (https://goo.gl/maps/dTngDKcksNp)*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Pago",
  "detalle":"Pago de tarjeta de credito",
  "medio":"APP del Banco",
  "ubicacion":{"x": 51.5007029, "y": -0.1261358},
  "realiza":{"nombre":"Maria", "cuenta":"003"}
}
',6,80000,4);
COMMIT;

/* 14.  Reversado de transacción con tarjeta VISA numero 1547, por valor de $125.000, para María */

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Consignación",
  "detalle":"Reversado de transacción con tarjeta VISA",
  "transaccion":1547,
  "realiza":{"nombre":"Franquicia VISA", "cuenta":"005"}
}
',3,125000,1);
COMMIT;

/*15.  Pago de intereses a las cuentas 001, 003 y 004 por valor de 0.01%*/

/*16.  Transferencia de $150.000 de pedro a pablo*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"TRANSFERENCIA",
  "detalle":"Transferencia a cuentas del banco",
  "realiza":{"nombre":"Pedro", "cuenta":"001"}
}
',2,150000,3);
COMMIT;

/*17.  Descuento de cuota de administración del mes de mayo de 2018 para todas las cuentas ($15.000)*/

/*18.  Consignación en cheque desde agilizador ubicado en campanario, por valor de $300.000 para la cuenta de María*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Consignación",
  "detalle":"Consignación en cheque",
  "cheque":{"tipo":"deposito","numero":2546, "ciudad":"Popayan", "fecha":"2018-05-30"},
  "realiza":{"nombre":"Usuario", "cedula":"1xxx"},
  "medio":"Agilizador",
  "origen":{"ciudad":"Popayan","lugar": "Centro comercial campanario","ubicacion":{"x": 2.4593543, "y": -76.5968831}}
}
',3,300000,1);
COMMIT;

/*19.  Pago de servicio de acueducto de Pedro por $80.500*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Pago",
  "detalle":"Pago  en servicio de Acueducto",
  "realiza":{"nombre":"Pedro", "cuenta":"001"}
}
',4,80500,4);
COMMIT;

/* 20.  Pago de servicio de acueducto de Pablo por $105.000 */


INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Pago",
  "detalle":"Pago  en servicio de Acueducto",
  "realiza":{"nombre":"Pablo", "cuenta":"002"}
}
',4,105000,4);
COMMIT;

/*21.  Consulta de saldo desde la APP del banco, de la cuenta de Pablo desde el centro de Medellín*/

INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Consulta",
  "detalle":"Consulta de saldo",
  "medio":"APP del Banco",
  "origen":{"ciudad":"Medellin","lugar": "Centro","ubicacion":{"x": 6.226372, "y": -75.5917202}},
  "realiza":{"nombre":"Pablo", "cuenta":"002"}
}
',null,0,null);
COMMIT;

/* 22.  Consulta de saldo desde cajero código ATM124 de la cuenta de Pedro desde el centro de Bogotá*/


INSERT INTO `transacciones` (`id_transaccion`, `fecha_hora`, `data`, `id_cuenta`, `valor`, `id_tipo`) VALUES
  (default,CURDATE(),'{
  "comentario":"Consulta",
  "detalle":"Consulta de saldo",
  "retiro":{"medio":"cajero","cajero":{"tipo":"ATH", "codigo":"ATM124"}},
  "origen":{"ciudad":"Bogota","lugar": "Centro","ubicacion":{"x": 4.6482837, "y": -74.2478938}},
  "realiza":{"nombre":"Pedro", "cuenta":"001"}
}
',null,0,null);
COMMIT;

/*23.  Cobro de comisión por consulta de saldo $1.500*/

UPDATE
  `transacciones`
SET
  `transacciones`.`data` = JSON_SET(`transacciones`.`data`, '$.comision', 1500)
WHERE
  `transacciones`.`data`->" $.comentario" = 'Consulta';
commit;

/*CONSULTAS*/
/*1. El total de ingresos y egreso en el día*/

SELECT
 `transacciones`.`data`->"$.clasificacion" as Tipo,
SUM(`transacciones`.`valor`) as Total

FROM
  `transacciones`   where DATE(`transacciones`.`fecha_hora`)=CURDATE() and `transacciones`.`data`->"$.clasificacion" is not null
  group by Tipo order by Total


/*2. El saldo total de cada cuenta con su propietario*/

SELECT
  `cliente`.`nombre`,
  SUM(`cuentas`.`saldo`)+sum(`transacciones`.`valor`)
FROM
  `cuentas`
  INNER JOIN `cliente` ON (`cuentas`.`id_cliente` = `cliente`.`id_cliente`)
  INNER JOIN `transacciones` ON (`cuentas`.`id_cuenta` = `transacciones`.`id_cuenta`) group by `cliente`.`nombre`

/*3. El total de dinero en el banco*/

SELECT
 SUM((`cuentas`.`saldo`) +( SELECT
 (SUM(`transacciones`.`valor`) + SUM(`transacciones`.`data`->"$.comision"))as T1 FROM `transacciones`))  as Total
FROM
  `cuentas`

/*4. El total de dinero que salió del banco*/
SELECT
 SUM(x.`valor`) as Total
FROM `transacciones` x where x.`data`->"$.clasificacion" = 'egreso' and x.id_cuenta is null

/*5. El total pagado por comisiones*/

SELECT
 SUM(x.`data`->"$.comision") as Comisiones
FROM `transacciones` x


/*6. El TOTAL a pagar por 4 por mil por consignaciones en cada cuenta*/




/*7. Total de la transacciones realizadas en cajeros por cuenta*/

SELECT
 COUNT(x.`valor`) as Consignaciones
FROM `transacciones` x where x.`data`->"$.retiro.medio" = 'cajero'

/*8. Total de la transacciones realizadas en caja*/

SELECT
 COUNT(x.`valor`) as Consignaciones
FROM `transacciones` x where x.`data`->"$.detalle" = 'Pago  en efectivo'

/*9. Total de transacciones en cheque*/

SELECT
 COUNT(x.`valor`) as Consignaciones
FROM `transacciones` x where x.`data`->"$.detalle" = 'Consignación en cheque'

/*10.Total ingresado al acueducto*/


SELECT
  `cliente`.`nombre`,
  sum(`transacciones`.`valor`)
FROM
  `cuentas`
  INNER JOIN `cliente` ON (`cuentas`.`id_cliente` = `cliente`.`id_cliente`)
  INNER JOIN `transacciones` ON (`cuentas`.`id_cuenta` = `transacciones`.`id_cuenta`) where `cliente`.`nombre`='Acueducto'


/*11.Total pagado en tarjetas*/

SELECT
 SUM(x.`valor`) as Total
FROM `transacciones` x where x.`data`->"$.detalle" = 'Pago de tarjeta de credito'

/*12.Total pagado en salarios*/

SELECT
 SUM(x.`valor`) as Total
FROM `transacciones` x where x.`data`->"$.detalle" = 'Pago de salario'


/*13.Listado de consignaciones discriminando efectivo y cheque*/

SELECT
   x.`valor` as Total,
   x.`data`->"$.detalle" as tipo
FROM `transacciones` x where x.id_tipo=1  and (x.`data`->"$.detalle" = 'Pago  en efectivo' or x.`data`->"$.detalle" = 'Consignación en cheque') order by tipo

/*14.Mostrar una URL para acceder a la ubicación de las transacciones que la posean: ejemplo :   https://www.google.com/maps/search/?api=1&query=2.4593597,-76.5968831*/
SELECT
    x.`data`->"$.sucursal.lugar" as Lugar,
   CONCAT('https://www.google.com/maps/search/?api=1&query=', x.`data`->"$.sucursal.ubicacion.x",',',x.`data`->"$.sucursal.ubicacion.y")  as ubicacion

FROM `transacciones` x where x.`data`->"$.sucursal.lugar" is not null