-- Procedimiento: create_new_user
DELIMITER $$

CREATE PROCEDURE `create_new_user`(
    IN p_company_id INT,
    IN p_username VARCHAR(45),
    IN p_name VARCHAR(50),
    IN p_last_name VARCHAR(50),
    IN p_phone VARCHAR(15),
    IN p_email VARCHAR(100),
    IN p_password VARCHAR(255),
    IN p_street_address VARCHAR(255),
    IN p_address_line_2 VARCHAR(255),
    IN p_postal_code VARCHAR(10),
    IN p_city VARCHAR(100),
    IN p_state VARCHAR(100),
    IN p_country VARCHAR(100)
)
BEGIN
    DECLARE last_user_id INT;
    DECLARE last_address_id INT;
    DECLARE role_id INT;
    
    -- Iniciar transacción
    START TRANSACTION;
    
    -- Insertar la dirección en la tabla 'addresses'
    INSERT INTO addresses (street_address, address_line_2, postal_code, city, state, country)
    --VALUES (p_street_address, p_address_line_2, p_postal_code, p_city, p_state, p_country);
    VALUES ("Carrer d'Aragó, 380", "SobreAtico 1", "08026", "Barcelona", "Barcelona", "España");
    --SET last_address_id = LAST_INSERT_ID();
    SET last_address_id = 6;
    
    -- Insertar el nuevo usuario en la tabla 'users'
    INSERT INTO users (username, name, last_name, phone, email, password, registered_date)
    --VALUES (p_username, p_name, p_last_name, p_phone, p_email, p_password, NOW());
    VALUES ("Diablo", "Belcebú", "Mefisto", "666666666", "eldiablo@infierno.com", "666", "2020-06-06 06:06:06");
    --SET last_user_id = LAST_INSERT_ID();
    SET last_user_id = 666;
    
    -- Relacionar al usuario con la dirección en 'user_addresses'
    INSERT INTO user_addresses (user_id, address_id)
    --VALUES (last_user_id, last_address_id);
    VALUES (666, 6);
    
    -- Asignar el rol 'customer' al nuevo usuario
    SELECT id_role INTO role_id FROM roles WHERE role_name = 'customer' LIMIT 1;
    INSERT INTO company_user_roles (company_id, user_id, role_id)
    --VALUES (p_company_id, last_user_id, role_id);
    VALUES (1, 666, 2);
    
    -- Insertar el nuevo cliente en la tabla 'customers', relacionado con la compañía
    INSERT INTO customers (user_id, company_id)
    --VALUES (last_user_id, p_company_id);
    VALUES (666, 1);
    
    -- Confirmar transacción
    COMMIT;
END $$

DELIMITER ;
