DELIMITER $$

DROP PROCEDURE IF EXISTS `get_company_details`$$

CREATE PROCEDURE `get_company_details`(
    IN p_company_id INT
)
BEGIN
    -- Declaración de variables
    DECLARE company_json JSON;
    DECLARE employees_schedule JSON;
    DECLARE p_start_date DATE;
    DECLARE p_end_date DATE;

    -- Obtener la fecha actual y calcular el rango de fechas (siguientes 7 días)
    SET p_start_date = CURDATE();
    SET p_end_date = DATE_ADD(p_start_date, INTERVAL 7 DAY);

    -- Llamar al procedimiento get_employee_schedules para obtener los empleados con sus horarios
    CALL get_employee_schedules(p_company_id, p_start_date, p_end_date, @employees_json);

    -- Obtener el resultado del parámetro OUT
    SET employees_schedule = @employees_json;

    -- Construcción del objeto JSON principal
    SELECT JSON_OBJECT(
        'success', TRUE,
        'data', JSON_OBJECT(
            'company_info', JSON_OBJECT(
                'company_name', IFNULL(REPLACE(REPLACE(c.company_name, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                'description', IFNULL(REPLACE(REPLACE(c.description, CHAR(13), '\\r'), CHAR(10), '\\n'), '')
            ),
            'addresses', IFNULL((
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'street_address', IFNULL(REPLACE(REPLACE(a.street_address, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'address_line_2', IFNULL(REPLACE(REPLACE(a.address_line_2, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'postal_code', IFNULL(REPLACE(REPLACE(a.postal_code, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'city', IFNULL(REPLACE(REPLACE(a.city, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'state', IFNULL(REPLACE(REPLACE(a.state, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'country', IFNULL(REPLACE(REPLACE(a.country, CHAR(13), '\\r'), CHAR(10), '\\n'), '')
                    )
                )
                FROM company_addresses ca
                JOIN addresses a ON ca.address_id = a.id_address
                WHERE ca.company_id = c.id_company
            ), JSON_ARRAY()),
            'employees', IFNULL(
                JSON_EXTRACT(employees_schedule, '$.employees'),
                JSON_ARRAY()
            ),
            'services', IFNULL((
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'id_service', s.id_service,
                        'service_name', IFNULL(REPLACE(REPLACE(s.service_name, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'description', IFNULL(REPLACE(REPLACE(s.description, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'price', s.price,
                        'discount', s.discount,
                        'duration', IFNULL(REPLACE(REPLACE(s.duration, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'commission_percentage', s.commission_percentage,
                        'employees', IFNULL((
                            SELECT JSON_ARRAYAGG(
                                JSON_OBJECT(
                                    'id_employee', e.id_employee,
                                    'name', IFNULL(REPLACE(REPLACE(u.name, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                                    'last_name', IFNULL(REPLACE(REPLACE(u.last_name, CHAR(13), '\\r'), CHAR(10), '\\n'), '')
                                )
                            )
                            FROM employee_services es
                            JOIN employees e ON es.employee_id = e.id_employee
                            JOIN users u ON e.user_id = u.id_user
                            WHERE es.service_id = s.id_service
                              AND e.company_id = c.id_company
                        ), JSON_ARRAY())
                    )
                )
                FROM services s
                WHERE s.company_id = c.id_company
            ), JSON_ARRAY()),
            'products', IFNULL((
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'id_product', p.id_product,
                        'product_name', IFNULL(REPLACE(REPLACE(p.product_name, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'description', IFNULL(REPLACE(REPLACE(p.description, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'price', p.price,
                        'stock_quantity', p.stock_quantity,
                        'commission_percentage', p.commission_percentage
                    )
                )
                FROM products p
                WHERE p.company_id = c.id_company
            ), JSON_ARRAY()),
            'reviews', IFNULL((
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'id_review', r.id_review,
                        'user_id', r.user_id,
                        'rating', r.rating,
                        'comment', IFNULL(REPLACE(REPLACE(r.comment, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'created_at', IFNULL(DATE_FORMAT(r.created_at, '%Y-%m-%d %H:%i:%s'), '')
                    )
                )
                FROM reviews r
                WHERE r.company_id = c.id_company
            ), JSON_ARRAY()),
            'operating_hours', IFNULL((
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'day_of_week', IFNULL(REPLACE(REPLACE(oh.day_of_week, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'open_time', IFNULL(TIME_FORMAT(oh.open_time, '%H:%i:%s'), ''),
                        'close_time', IFNULL(TIME_FORMAT(oh.close_time, '%H:%i:%s'), ''),
                        'interval_number', oh.interval_number
                    )
                )
                FROM company_operating_hours oh
                WHERE oh.company_id = c.id_company
            ), JSON_ARRAY()),
            'categories', IFNULL((
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'id_category', cat.id_category,
                        'name', IFNULL(REPLACE(REPLACE(cat.name, CHAR(13), '\\r'), CHAR(10), '\\n'), ''),
                        'subcategories', IFNULL((
                            SELECT JSON_ARRAYAGG(
                                JSON_OBJECT(
                                    'id_subcategory', sub.id_subcategory,
                                    'name', IFNULL(REPLACE(REPLACE(sub.name, CHAR(13), '\\r'), CHAR(10), '\\n'), '')
                                )
                            )
                            FROM subcategories sub
                            WHERE sub.category_id = cat.id_category
                              AND sub.company_id = c.id_company
                        ), JSON_ARRAY())
                    )
                )
                FROM categories cat
                WHERE cat.company_id = c.id_company
            ), JSON_ARRAY())
        )
    ) INTO company_json
    FROM companies c
    WHERE c.id_company = p_company_id;

    -- Retornar el JSON resultante
    SELECT company_json AS company_details;
END $$
DELIMITER ;
