USE appventas-dev;

DROP PROCEDURE IF EXISTS sp_generate_demo_data;
DELIMITER $$

CREATE PROCEDURE sp_generate_demo_data(IN p_org_id INT)
BEGIN

    -- =========================
    -- VARIABLES
    -- =========================
    DECLARE v_sale_total DECIMAL(12,2);
    DECLARE v_sale_qty INT;
    DECLARE v_num_details INT;
    DECLARE v_num_clients INT;
    DECLARE v_num_sales INT;

    DECLARE v_start_date DATE DEFAULT '2025-12-01';
    DECLARE v_end_date DATE DEFAULT '2026-04-12';
    DECLARE v_current_date DATE;

    DECLARE v_day_count INT DEFAULT 0;
    DECLARE v_max_days INT DEFAULT 80;

    DECLARE v_product_id INT;
    DECLARE v_client_id BIGINT;
    DECLARE v_sale_id INT;
    DECLARE v_fiado_payment_id INT;
    DECLARE v_contado_payment_id INT;
    DECLARE v_facturado_payment_id INT;
    DECLARE v_sale_kardex_type_id INT;
    DECLARE v_purchase_kardex_type_id INT;
    DECLARE v_sale_detail_id INT;
    DECLARE v_purchase_id INT;
    DECLARE v_provider_id INT;

    DECLARE v_qty INT;
    DECLARE v_price DECIMAL(10,2);
    DECLARE v_total DECIMAL(10,2);

    DECLARE v_prev_balance INT;
    DECLARE v_new_balance INT;

    DECLARE v_payment_method_id INT;

    DECLARE done INT DEFAULT 0;

    DECLARE cur_products CURSOR FOR
        SELECT id FROM sa_product WHERE org_id = p_org_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- =========================
    -- LIMPIEZA
    -- =========================
    DELETE FROM transactions WHERE user_id = p_org_id;
    DELETE FROM sa_kardex_entry WHERE org_id = p_org_id;
    DELETE FROM sa_purchase WHERE org_id = p_org_id;
    DELETE FROM sa_sale_detail WHERE sale_id IN (SELECT id FROM sa_sale WHERE org_id = p_org_id);
    DELETE FROM sa_provider_sale WHERE org_id = p_org_id;
    DELETE FROM sa_sale WHERE org_id = p_org_id;

    DELETE FROM sa_daily_closing WHERE daily_closing_head_id IN (
        SELECT id FROM sa_daily_closing_head WHERE org_id = p_org_id
    );
	
    DELETE FROM sa_product_slot WHERE product_id IN (
        SELECT id FROM sa_product WHERE org_id = p_org_id
    );
    DELETE FROM sa_daily_closing_head WHERE org_id = p_org_id;

    DELETE FROM sa_product WHERE org_id = p_org_id;
    DELETE FROM sa_category WHERE org_id = p_org_id;
    DELETE FROM clients WHERE user_id = p_org_id;

    -- =========================
    -- DATA BASE
    -- =========================
	-- =========================
	-- CATEGORÍAS
	-- =========================
	INSERT INTO sa_category (name, description, org_id) VALUES 
	('Bebidas', 'Gaseosas y refrescos', p_org_id),
	('Snacks', 'Pasabocas', p_org_id),
	('Lacteos', 'Productos lácteos', p_org_id),
	('Aseo', 'Limpieza del hogar', p_org_id),
	('Panaderia', 'Productos de panadería', p_org_id),
	('Enlatados', 'Comida enlatada', p_org_id),
	('Granos', 'Arroz, frijoles, lentejas', p_org_id),
	('Dulces', 'Confitería', p_org_id);

	-- =========================
	-- PRODUCTOS (RELACIONADOS POR SELECT)
	-- =========================
	INSERT INTO sa_product (name, purchase_price, sale_price, category_id, org_id)
	SELECT 'Coca Cola', 2000, 3000, id, p_org_id FROM sa_category WHERE name = 'Bebidas' AND org_id = p_org_id
	UNION ALL
	SELECT 'Pepsi', 1800, 2800, id, p_org_id FROM sa_category WHERE name = 'Bebidas' AND org_id = p_org_id
	UNION ALL
	SELECT 'Agua', 1000, 1800, id, p_org_id FROM sa_category WHERE name = 'Bebidas' AND org_id = p_org_id

	UNION ALL
	SELECT 'Papas', 1500, 2500, id, p_org_id FROM sa_category WHERE name = 'Snacks' AND org_id = p_org_id
	UNION ALL
	SELECT 'Chocorramo', 1200, 2200, id, p_org_id FROM sa_category WHERE name = 'Snacks' AND org_id = p_org_id
	UNION ALL
	SELECT 'Galletas', 1300, 2300, id, p_org_id FROM sa_category WHERE name = 'Snacks' AND org_id = p_org_id

	UNION ALL
	SELECT 'Leche', 3000, 4200, id, p_org_id FROM sa_category WHERE name = 'Lacteos' AND org_id = p_org_id
	UNION ALL
	SELECT 'Queso', 4000, 6000, id, p_org_id FROM sa_category WHERE name = 'Lacteos' AND org_id = p_org_id
	UNION ALL
	SELECT 'Yogurt', 2500, 3800, id, p_org_id FROM sa_category WHERE name = 'Lacteos' AND org_id = p_org_id

	UNION ALL
	SELECT 'Detergente', 5000, 7000, id, p_org_id FROM sa_category WHERE name = 'Aseo' AND org_id = p_org_id
	UNION ALL
	SELECT 'Jabon', 2000, 3500, id, p_org_id FROM sa_category WHERE name = 'Aseo' AND org_id = p_org_id
	UNION ALL
	SELECT 'Cloro', 3000, 5000, id, p_org_id FROM sa_category WHERE name = 'Aseo' AND org_id = p_org_id

	UNION ALL
	SELECT 'Pan', 500, 1200, id, p_org_id FROM sa_category WHERE name = 'Panaderia' AND org_id = p_org_id
	UNION ALL
	SELECT 'Croissant', 1500, 3000, id, p_org_id FROM sa_category WHERE name = 'Panaderia' AND org_id = p_org_id

	UNION ALL
	SELECT 'Atun', 3000, 4800, id, p_org_id FROM sa_category WHERE name = 'Enlatados' AND org_id = p_org_id
	UNION ALL
	SELECT 'Sardinas', 2500, 4000, id, p_org_id FROM sa_category WHERE name = 'Enlatados' AND org_id = p_org_id

	UNION ALL
	SELECT 'Arroz', 2000, 3200, id, p_org_id FROM sa_category WHERE name = 'Granos' AND org_id = p_org_id
	UNION ALL
	SELECT 'Lentejas', 1800, 3000, id, p_org_id FROM sa_category WHERE name = 'Granos' AND org_id = p_org_id

	UNION ALL
	SELECT 'Chocolate', 1200, 2200, id, p_org_id FROM sa_category WHERE name = 'Dulces' AND org_id = p_org_id
	UNION ALL
	SELECT 'Caramelos', 800, 1500, id, p_org_id FROM sa_category WHERE name = 'Dulces' AND org_id = p_org_id;


	INSERT INTO sa_provider_sale (name, balance, phone, email, address, org_id, state) VALUES
	('Proveedor Central', 0.00, '3001000001', 'central@mail.com', 'Calle 1 #10-20', 76, 'A'),
	('Distribuidora Norte', 0.00, '3001000002', 'norte@mail.com', 'Carrera 5 #20-30', 76, 'A'),
	('Comercial Sur', 0.00, '3001000003', 'sur@mail.com', 'Av 3 #15-40', 76, 'A'),
	('Almacén Oriente', 0.00, '3001000004', 'oriente@mail.com', 'Calle 8 #25-60', 76, 'A'),
	('Proveedora Occidente', 0.00, '3001000005', 'occidente@mail.com', 'Carrera 12 #30-10', 76, 'A'),
	('Suministros Globales', 0.00, '3001000006', 'global@mail.com', 'Zona Industrial Lote 5', 76, 'A'),
	('Distribuciones Express', 0.00, '3001000007', 'express@mail.com', 'Calle 45 #18-90', 76, 'A'),
	('Mayorista Andino', 0.00, '3001000008', 'andino@mail.com', 'Av Principal #50-70', 76, 'A'),
	('Red Comercial SAS', 0.00, '3001000009', 'red@mail.com', 'Carrera 22 #40-15', 76, 'A'),
	('Abastecimientos del Valle', 0.00, '3001000010', 'valle@mail.com', 'Calle 60 #12-34', 76, 'A');

    INSERT INTO clients (user_id, name, created_at)
    SELECT p_org_id, CONCAT('Cliente ', n), NOW()
    FROM (
        SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
        UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
    ) t;

    -- =========================
    -- LOOP PRINCIPAL
    -- =========================
    SET v_current_date = v_start_date;
	
	SELECT id INTO v_sale_kardex_type_id
	FROM sa_kardex_entry_type
	WHERE type = 'OUT'
	AND org_id = p_org_id
	AND is_default = 1
	LIMIT 1;

	SELECT id INTO v_purchase_kardex_type_id
	FROM sa_kardex_entry_type
	WHERE type = 'IN'
	AND org_id = p_org_id
	AND is_default = 1
	LIMIT 1;
	
	SELECT id INTO v_fiado_payment_id
	FROM sa_payment_method
	WHERE org_id = p_org_id
	AND is_credit = 1
	LIMIT 1;
	
	SELECT id INTO v_facturado_payment_id
	FROM sa_payment_method
	WHERE org_id = p_org_id
	AND is_default = 1
	LIMIT 1;
	
	SELECT id INTO v_contado_payment_id
	FROM sa_payment_method
	WHERE org_id = p_org_id
	AND is_credit = 0
	AND is_default = 0
	LIMIT 1;
	
    main_loop: WHILE v_current_date <= v_end_date AND v_day_count < v_max_days DO

        IF RAND() > 0.5 THEN
            SET v_current_date = DATE_ADD(v_current_date, INTERVAL 1 DAY);
            ITERATE main_loop;
        END IF;

        SET v_day_count = v_day_count + 1;

        -- =========================
        -- COMPRA
        -- =========================
        INSERT INTO sa_purchase (movement_date, kardex_entry_type_id, org_id)
        VALUES (v_current_date, v_purchase_kardex_type_id, p_org_id);

        SET v_purchase_id = LAST_INSERT_ID();

        SET done = 0;
        OPEN cur_products;

        products_loop: LOOP
            FETCH cur_products INTO v_product_id;

            IF done THEN
                LEAVE products_loop;
            END IF;

            SELECT IFNULL(MAX(balance), 0)
            INTO v_prev_balance
            FROM sa_kardex_entry
            WHERE product_id = v_product_id
            AND org_id = p_org_id;

            SET v_qty = FLOOR(10 + RAND()*50);
            SET v_price = FLOOR(1000 + RAND()*4000);
			
			SET v_provider_id = (
				SELECT id FROM sa_provider_sale
				WHERE org_id = p_org_id
				ORDER BY RAND()
				LIMIT 1
			);			

            SET v_new_balance = v_prev_balance + v_qty;

            INSERT INTO sa_kardex_entry (
                product_id, purchase_id, kardex_entry_type_id, provider_id,
                previous_quantity, quantity, balance,
                unit_price, total_price, org_id
            )
            VALUES (
                v_product_id, v_purchase_id, v_purchase_kardex_type_id, v_provider_id,
                v_prev_balance, v_qty, v_new_balance,
                v_price, v_qty * v_price, p_org_id
            );

        END LOOP;

        CLOSE cur_products;

        -- =========================
        -- VENTAS
        -- =========================
        SET v_num_sales = FLOOR(1 + RAND()*5);

        sales_loop: WHILE v_num_sales > 0 DO

            INSERT INTO sa_sale (
                date, kardex_entry_type_id, quantity,
                subtotal, total, org_id
            )
            VALUES (v_current_date, v_sale_kardex_type_id, 0, 0, 0, p_org_id);

            SET v_sale_id = LAST_INSERT_ID();

            SET v_sale_total = 0;
            SET v_sale_qty = 0;
			

            -- 👇 CLIENTES EN LA VENTA
            SET v_num_clients = FLOOR(1 + RAND()*5);

            clients_loop: WHILE v_num_clients > 0 DO

                SET v_client_id = (
                    SELECT id FROM clients
                    WHERE user_id = p_org_id
                    ORDER BY RAND()
                    LIMIT 1
                );

                -- 👇 PRODUCTOS POR CLIENTE
                SET v_num_details = FLOOR(1 + RAND()*3);

                details_loop: WHILE v_num_details > 0 DO

                    SET v_product_id = (
                        SELECT id FROM sa_product
                        WHERE org_id = p_org_id
                        ORDER BY RAND()
                        LIMIT 1
                    );

                    SELECT IFNULL(MAX(balance), 0)
                    INTO v_prev_balance
                    FROM sa_kardex_entry
                    WHERE product_id = v_product_id
                    AND org_id = p_org_id;

                    SET v_qty = FLOOR(1 + RAND()*5);

                    IF v_prev_balance < v_qty THEN
                        SET v_qty = v_prev_balance;
                    END IF;

                    IF v_qty = 0 THEN
                        ITERATE details_loop;
                    END IF;

                    SET v_price = (
                        SELECT sale_price FROM sa_product WHERE id = v_product_id
                    );

                    SET v_total = v_qty * v_price;

                    SET @rand = RAND();

                    IF @rand < 0.6 THEN
                        SET v_payment_method_id = v_fiado_payment_id;
                    ELSEIF @rand < 0.85 THEN
                        SET v_payment_method_id = v_facturado_payment_id;
                    ELSE
                        SET v_payment_method_id = v_contado_payment_id;
                    END IF;

                    INSERT INTO sa_sale_detail (
                        sale_id, client_id, product_id, category_id,
                        payment_method_id, quantity, unit_price, total_price
                    )
                    VALUES (
                        v_sale_id, v_client_id, v_product_id,
                        (SELECT category_id FROM sa_product WHERE id = v_product_id),
                        v_payment_method_id, v_qty, v_price, v_total
                    );

                    SET v_sale_detail_id = LAST_INSERT_ID();

                    IF v_payment_method_id = v_fiado_payment_id THEN
                        INSERT INTO transactions (
                            date, user_id, client_id, sale_detail_id, paymentMethod, units, price, state
                        )
                        VALUES (
                            v_current_date, p_org_id, v_client_id, v_sale_detail_id,
                            'fiado', v_qty, v_total, 'A'
                        );
                    END IF;

                    SET v_new_balance= v_prev_balance - v_qty;

                    INSERT INTO sa_kardex_entry (
                        product_id, sale_detail_id, kardex_entry_type_id,
                        previous_quantity, quantity, balance,
                        unit_price, total_price, client_id, org_id
                    )
                    VALUES (
                        v_product_id, v_sale_detail_id, v_sale_kardex_type_id,
                        v_prev_balance, v_qty, v_new_balance,
                        v_price, v_total, v_client_id, p_org_id
                    );

                    SET v_sale_total = v_sale_total + v_total;
                    SET v_sale_qty = v_sale_qty + v_qty;

                    SET v_num_details = v_num_details - 1;

                END WHILE;

                SET v_num_clients = v_num_clients - 1;

            END WHILE;

            UPDATE sa_sale
            SET total = v_sale_total,
                subtotal = v_sale_total,
                quantity = v_sale_qty
            WHERE id = v_sale_id;

            SET v_num_sales = v_num_sales - 1;

        END WHILE;

        SET v_current_date = DATE_ADD(v_current_date, INTERVAL 1 DAY);

    END WHILE;

END$$

DELIMITER ;

CALL sp_generate_demo_data(76);