SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE invoice_pipefy_item_matches;
TRUNCATE TABLE pipefy_request_items;
TRUNCATE TABLE pipefy_request_providers;
TRUNCATE TABLE pipefy_requests;
SET FOREIGN_KEY_CHECKS=1;

INSERT INTO pipefy_requests (
  pipefy_id,
  title,
  rc_list,
  creator_email,
  contact_name,
  contact_phone,
  delivery_address,
  project_name,
  executive_name,
  client_name,
  products_count,
  subtotal_cost,
  subtotal_sale,
  payload,
  created_at,
  updated_at
) VALUES (
  'REQ-DEMO-100001',
  'JN-01000',
  '9000012345',
  'carlos.p@weareopenminds.com',
  'Compras Demo',
  '+525500000000',
  'CDMX',
  'SOLICITUD DEMO DOCUMENTACION',
  'Carlos Perez',
  'Cliente Demo',
  3,
  100000.00,
  200000.00,
  JSON_OBJECT(
    'footerJson',
    JSON_OBJECT(
      'Ejecutivo_OM', JSON_ARRAY('Carlos Perez'),
      'Usuarios', JSON_ARRAY('Cliente Demo')
    )
  ),
  NOW(),
  NOW()
);

SET @request_id := LAST_INSERT_ID();

INSERT INTO pipefy_request_providers (
  pipefy_request_id,
  provider_name,
  provider_name_norm,
  items_count,
  total_cost,
  total_sale,
  created_at,
  updated_at
) VALUES (
  @request_id,
  'ESTRATEGIA EN SUMINISTROS',
  'estrategiaensuministros',
  3,
  100000.00,
  200000.00,
  NOW(),
  NOW()
);

SET @provider_id := LAST_INSERT_ID();

INSERT INTO pipefy_request_items (
  pipefy_request_id,
  pipefy_request_provider_id,
  product_index,
  product_name,
  description,
  concept_code,
  quantity,
  unit_cost,
  unit_sale,
  total_cost,
  total_sale,
  provider_name,
  created_at,
  updated_at
) VALUES
(
  @request_id,
  @provider_id,
  1,
  'COD:A1B2 Servicio Logistico',
  'COD:A1B2 Servicio Logistico',
  'A1B2',
  10,
  3000.00,
  7000.00,
  30000.00,
  70000.00,
  'ESTRATEGIA EN SUMINISTROS',
  NOW(),
  NOW()
),
(
  @request_id,
  @provider_id,
  2,
  'COD:C3D4 Material POP',
  'COD:C3D4 Material POP',
  'C3D4',
  10,
  3500.00,
  6500.00,
  35000.00,
  65000.00,
  'ESTRATEGIA EN SUMINISTROS',
  NOW(),
  NOW()
),
(
  @request_id,
  @provider_id,
  3,
  'COD:E5F6 Produccion Especial',
  'COD:E5F6 Produccion Especial',
  'E5F6',
  10,
  3500.00,
  6500.00,
  35000.00,
  65000.00,
  'ESTRATEGIA EN SUMINISTROS',
  NOW(),
  NOW()
);

SELECT @request_id AS request_id, @provider_id AS provider_id;
SELECT id, title, rc_list, subtotal_cost, subtotal_sale FROM pipefy_requests;
SELECT id, pipefy_request_id, provider_name, total_cost, total_sale FROM pipefy_request_providers;
SELECT id, pipefy_request_id, concept_code, quantity, total_cost, total_sale FROM pipefy_request_items ORDER BY id;
