-- -----------------------------------------------------------------------------------------------------------------------
-- ----------- Crear una nueva base de datos en MYSQL + tablas  -----------------------------------------------------
-- -----------------------------------------------------------------------------------------------------------------------

# 1. Crear la nueva base de datos en MYSQL

CREATE SCHEMA learndata ;

# 2. Crear la tabla de clientes para almacenar la información de nuestros clientes.

CREATE TABLE dim_clientes (
    id_cliente int,
    fecha_creacion_cliente DATE,
    nombre_cliente varchar(100),
    apellido_cliente varchar(100),
    email_cliente varchar(100),
    telefono_cliente varchar(100),
    region_cliente varchar(100),
    pais_cliente varchar(100),
    codigo_postal_cliente varchar(100),
    direccion_cliente varchar(255),
    PRIMARY KEY (id_cliente)
   );

# 3. Crear la tabla productos para almacenar la información sobre los curso que vendemos.

CREATE TABLE dim_producto (
  id_producto int ,
  sku_producto int NOT NULL,
  nombre_producto text,
  publicado_producto BOOLEAN ,
  inventario_producto text,
  precio_normal_producto INT ,
  categoria_producto text,
  PRIMARY KEY (sku_producto)
);
# 4. Crear la tabla de pedidos donde se almacenarán todas nuestras ventas

CREATE TABLE fac_pedidos (
		id_pedido INT NOT NULL,
  sku_producto INT,
  estado_pedido VARCHAR(50),
  fecha_pedido DATE ,
  id_cliente INT  ,
  tipo_pago_pedido VARCHAR(50) ,
  costo_pedido INT  ,
  importe_de_descuento_pedido decimal(10,0) ,
  importe_total_pedido INT ,
  cantidad_pedido INT  ,
  codigo_cupon_pedido VARCHAR(100),
  PRIMARY KEY (id_pedido),
  FOREIGN KEY (id_cliente) REFERENCES dim_clientes (id_cliente),
  FOREIGN KEY (sku_producto) REFERENCES dim_producto (sku_producto)
);

# 5. Crear la tabla de pagos de stripe qeu recibimos

CREATE TABLE fac_pagos_stripe (
  fecha_pago datetime(6) ,
  id_pedido int ,
  importe_pago int ,
  moneda_pago text,
  comision_pago decimal(10,2) ,
  neto_pago decimal(10,2) ,
  tipo_pago text,
  FOREIGN KEY (id_pedido) REFERENCES fac_pedidos (id_pedido)
)

-- -----------------------------------------------------------------------------------------------------------------------
-- -------- Crear la tabla de productos a partir de los datos en crudo  -----------------------------------------------------
-- -----------------------------------------------------------------------------------------------------------------------

# 1. Análisis previo para determinar situación de la tabla

SELECT * 
FROM datoscrudos.raw_productos_wocommerce;

---- Concluimos que los datos de esta tabla vienen bien por lo que no será necesario ninguna transformación extra.

# 2. Creación de nueva tabla con nombres adecuados 

INSERT INTO cursosdata.dim_producto

SELECT
id as id_producto,
sku as sku_producto,
nombre as nombre_producto,
publicado as publicado_producto,
inventario as inventario_producto,
precio_normal as precio_normal_producto,
categorias as categoria_producto
FROM datoscrudos.raw_productos_wocommerce

-- -----------------------------------------------------------------------------------------------------------------------
-- -------- Crear la tabla de clientes a partir de los datos en crudo  -----------------------------------------------------
-- -----------------------------------------------------------------------------------------------------------------------

# 1) Convertir el campo date_created que viene como timestamp a solo fecha

SELECT
id as id_cliente,
DATE(STR_TO_DATE(date_created,"%d/%m/%Y %H:%i:%s")) AS fecha_creacion_cliente,
billing
FROM datoscrudos.raw_clientes_wocommerce;

# 2) Extraer del campo billing, todos los descriptivos del cliente que necesitamos aprendiendo a parsear un JSON. 

SELECT
id as id_cliente,
DATE(STR_TO_DATE(date_created,"%d/%m/%Y %H:%i:%s")) AS fecha_creacion_cliente,
JSON_VALUE(billing,'$[0].first_name') AS nombre_cliente,
JSON_VALUE(billing,'$[0].last_name') AS apellido_cliente,
JSON_VALUE(billing,'$[0].email') AS email_cliente,
JSON_VALUE(billing,'$[0].phone') AS telefono_cliente,
JSON_VALUE(billing,'$[0].Region') AS region_cliente,
JSON_VALUE(billing,'$[0].country') AS pais_cliente,
JSON_VALUE(billing,'$[0].postcode') AS codigo_postal_cliente,
JSON_VALUE(billing,'$[0].address_1') AS direccion_cliente
FROM datoscrudos.raw_clientes_wocommerce

# 3) Insertar los campos a la nueva tabla 

INSERT INTO cursosdata.dim_clientes

SELECT
id as id_cliente,
DATE(STR_TO_DATE(date_created,"%d/%m/%Y %H:%i:%s")) AS fecha_creacion_cliente,
JSON_VALUE(billing,'$[0].first_name') AS nombre_cliente,
JSON_VALUE(billing,'$[0].last_name') AS apellido_cliente,
JSON_VALUE(billing,'$[0].email') AS email_cliente,
JSON_VALUE(billing,'$[0].phone') AS telefono_cliente,
JSON_VALUE(billing,'$[0].Region') AS region_cliente,
JSON_VALUE(billing,'$[0].country') AS pais_cliente,
JSON_VALUE(billing,'$[0].postcode') AS codigo_postal_cliente,
JSON_VALUE(billing,'$[0].address_1') AS direccion_cliente
FROM datoscrudos.raw_clientes_wocommerce

-- -----------------------------------------------------------------------------------------------------------------------
-- -------- Crear la tabla de pedidos a partir de los datos en crudo  -----------------------------------------------------
-- -----------------------------------------------------------------------------------------------------------------------

# 1) Sustituir el nombre del producto por el id.

	SELECT
		numero_de_pedido,
		estado_de_pedido,
		fecha_de_pedido,
    #p.SKU_producto,
    CASE WHEN p.SKU_producto IS NULL THEN 3 ELSE p.SKU_producto END as SKU_producto,
		`id cliente` AS id_cliente,
		titulo_metodo_de_pago,
		coste_articulo,
		importe_de_descuento_del_carrito,
		importe_total_pedido,
		cantidad,
		cupon_articulo
	FROM datoscrudos.raw_pedidos_wocommerce w
	LEFT JOIN learndata_crudo.dim_producto p ON p.nombre_producto = w.nombre_del_articulo

# 2) Normalizamos la columna método de pago

	SELECT
		numero_de_pedido,
		estado_de_pedido,
		fecha_de_pedido,
    CASE WHEN p.SKU_producto IS NULL THEN 3 ELSE p.SKU_producto END as SKU_producto,
		`id cliente` AS id_cliente,
		CASE WHEN titulo_metodo_de_pago LIKE '%Stripe%' THEN 'Stripe' ELSE 'Tarjeta' END AS metodo_pago_pedido,
		coste_articulo,
		importe_de_descuento_del_carrito,
		importe_total_pedido,
		cantidad,
		cupon_articulo
	FROM datoscrudos.raw_pedidos_wocommerce w
	LEFT JOIN cursosdata.dim_producto p ON p.nombre_producto = w.nombre_del_articulo

# 3) Convertir a date la columna fecha_pedido

		SELECT
		numero_de_pedido,
		estado_de_pedido,
		DATE(fecha_de_pedido) AS fecha_pedido,
    CASE WHEN p.SKU_producto IS NULL THEN 3 ELSE p.SKU_producto END as SKU_producto,
		`id cliente` AS id_cliente,
		CASE WHEN titulo_metodo_de_pago LIKE '%Stripe%' THEN 'Stripe' ELSE 'Tarjeta' END AS metodo_pago_pedido,
		coste_articulo,
		importe_de_descuento_del_carrito,
		importe_total_pedido,
		cantidad,
		cupon_articulo
	FROM datoscrudos.raw_pedidos_wocommerce w
	LEFT JOIN cursosdata.dim_producto p ON p.nombre_producto = w.nombre_del_articulo

# 4) Redondear decimales de la columna coste_articulo a enteros

SELECT
		numero_de_pedido,
		estado_de_pedido,
		DATE(fecha_de_pedido) AS fecha_pedido,
    CASE WHEN p.SKU_producto IS NULL THEN 3 ELSE p.SKU_producto END as SKU_producto,
		`id cliente` AS id_cliente,
		CASE WHEN titulo_metodo_de_pago LIKE '%Stripe%' THEN 'Stripe' ELSE 'Tarjeta' END AS metodo_pago_pedido,
		CEILING(coste_articulo) AS costo_pedido,
		importe_de_descuento_del_carrito,
		importe_total_pedido,
		cantidad,
		cupon_articulo
	FROM datoscrudos.raw_pedidos_wocommerce w
	LEFT JOIN cursosdata.dim_producto p ON p.nombre_producto = w.nombre_del_articulo

# 5) Insertamos los pedidos a la tabla

INSERT INTO cursosdata.fac_pedidos
SELECT
    numero_de_pedido,
    CASE WHEN p.SKU_producto IS NULL THEN 3 ELSE CAST(p.SKU_producto AS UNSIGNED) END as SKU_producto,
		estado_de_pedido,
		DATE(fecha_de_pedido) AS fecha_pedido,
		w.`id cliente`as id_cliente,
		CASE WHEN titulo_metodo_de_pago LIKE '%Stripe%' THEN 'Stripe' ELSE 'Tarjeta' END AS tipo_pago_pedido,
		CEILING(coste_articulo) AS costo_pedido,
		importe_de_descuento_del_carrito,
		importe_total_pedido,
		cantidad as cantidad_pedido,
		cupon_articulo
FROM datoscrudos.raw_pedidos_wocommerce w
LEFT JOIN cursosdata.dim_producto p ON p.nombre_producto = w.nombre_del_articulo

----- Trato de duplicados -- detectamos que no nos deja insertar la información porque encontramos un duplicado --

SELECT * FROM datoscrudos.raw_pedidos_wocommerce
WHERE numero_de_pedido = 41624

----- Debemos primero analizar si podemos detectar porque viene y si no se elimina, reportamos al equipo que corresponda --

DELETE FROM datoscrudos.raw_pedidos_wocommerce WHERE numero_de_pedido = 41624 and `id cliente` = 1324

--- Una vez eliminado volves a ejecutar la query --

INSERT INTO learndata.fac_pedidos
    SELECT
		numero_de_pedido as id_pedido,
        CASE WHEN p.SKU_producto IS NULL THEN 3 ELSE p.SKU_producto END as SKU_producto,
		estado_de_pedido,
		DATE(fecha_de_pedido) as fecha_pedido,
        `id cliente` AS id_cliente,
		CASE WHEN titulo_metodo_de_pago LIKE '%Stripe%' THEN 'Stripe' ELSE 'Tarjeta' END AS tipo_pago_pedido,
        CEILING(coste_articulo) AS costo_pedido,
        importe_de_descuento_del_carrito as importe_de_descuento_pedido,
		importe_total_pedido,
        cantidad as cantidad_pedido,
		cupon_articulo as codigo_cupon_pedido
	FROM learndata_crudo.raw_pedidos_wocommerce w
	LEFT JOIN learndata.dim_producto p ON p.nombre_producto = w.nombre_del_articulo

-- -----------------------------------------------------------------------------------------------------------------------
-- ----------- Crear una nueva base de datos en MYSQL + tablas  -----------------------------------------------------
-- -----------------------------------------------------------------------------------------------------------------------

# 1)Obtener el número de pedido con la función RIGHT. Quitar el numero de pedido de la descripción que es lo que nos va a permitir unir esta tabla con otras

		SELECT 
		created,
		RIGHT(description,5) as id_pedido,
		amount,
		currency,
		fee,
		net,
		status,
		type
		FROM datoscrudos.raw_pagos_stripe;

# 2) Pasar a timestamp el campo “created”

		SELECT 
		TIMESTAMP(created) as fecha_pago,
		RIGHT(description,5) as id_pedido,
		amount,
		currency,
		fee,
		net,
		status,
		type
		FROM datoscrudos.raw_pagos_stripe ;

# 3)Reemplazar las commas por puntos

		SELECT 
		TIMESTAMP(created) as fecha_pago,
		RIGHT(description,5) as id_pedido,
		amount,
		currency,
		REPLACE(fee,',','.') as comision_pago,
		REPLACE(net,',','.') as neto_pago,
		status,
		type
		FROM datoscrudos.raw_pagos_stripe;

	# b. Convertir el número a decimal con dos lugares despues de la comma.

		SELECT 
		TIMESTAMP(created) as fecha_pago,
		RIGHT(description,5) as id_pedido,
		amount,
		currency,
		CAST(REPLACE(fee,',','.') AS DECIMAL(10,2)) as comision_pago,
		CAST(REPLACE(net,',','.') AS DECIMAL(10,2)) as neto_pago,
		status,
		type
		FROM datoscrudos.raw_pagos_stripe

# 4) Insertar tabla en nueva

SET @@SESSION.sql_mode='ALLOW_INVALID_DATES'; 

# (Ejecutar este comando para resolverlo)

INSERT INTO cursosdata.fac_pagos_stripe

SELECT
timestamp(created) as fecha_pago,
RIGHT(description,5) as id_pedido,
amount as importe_pago,
currency as moneda_pago,
CAST(REPLACE(fee,',','.') AS DECIMAL(10,2)) as comision_pago,
CAST(REPLACE(net,',','.') AS DECIMAL(10,2)) as neto_pago,
type as tipo_pago
FROM datoscrudos.raw_pagos_stripe;