-- =============================================================================
-- Creación manual de Customer + Baddress + User (POBOX) + Pobox
-- Equivale en gran parte al flujo de PoboxService::createPoboxWithNewCustomer
-- (sin envío de email ni actualización de document_sequence si aplica).
--
-- 1) Contraseña por defecto del script: ChangeMe123! (cámbiala en producción).
--    Para otra contraseña: printf '0\n' | php bin/console security:hash-password 'TU_CLAVE'
--    y sustituye @password_hash y @password_salt.
--
-- 2) Definir @customer_number y @pobox_number según la lógica de tu empresa
--    (legacy/agency/company, pobox2customer, document_sequence, etc.).
--
-- 3) Ejecutar contra la base (ej.: mysql ... < scripts/create_pobox_with_customer_manual.sql)
-- =============================================================================
SET @agency_id := 32;
SET @city_id := 30143;
SET @customer_type_id := 1;
SET @agent_id := NULL;
SET @location_id := NULL;
SET @grouptariff_id := NULL;
SET @pobox_type_id := 1;
SET @created_by_user_id := 1;
SET @username := 'nikita_shershnev_tp';
SET @password_hash := 'HmkJy++MeOLifnqQq9oOhzUc7iu/J8XjTOeESv+ddYMGZnc4D/pUKZivZ5aM9Ep7qw1dd9mJhoJ7LXIn2d7GKw==';
-- ChangeMe123!
SET @password_salt := 'pMnMpWueI89UgIF3wJUP6tYde7+bpXIHTOwCaqt/';
SET @customer_number := 'REEMPLAZAR_NUMERO_CLIENTE';
SET @pobox_number := 'REEMPLAZAR_NUMERO_CASILLERO';
SET @cust_name := 'Nikita Shershnev';
SET @cust_lastname := '';
SET @email := 'yamilet@tpdev.com';
SET @phone := '407736421';
SET @docid := '15752258';
SET @address := 'La Beatriz Bloque 21, apt 02-01';
SET @zip := '00001';
START TRANSACTION;
SELECT a.maincompany_id,
  a.warehouse_id INTO @maincompany_id,
  @warehouse_id
FROM agency a
WHERE a.id = @agency_id;
SELECT id INTO @cust_status_id
FROM customerstatus
WHERE name = 'ACTIVO'
LIMIT 1;
SELECT id INTO @pb_status_id
FROM poboxstatus
WHERE name = 'ACTIVO'
LIMIT 1;
INSERT INTO `customer` (
    name,
    lastname,
    number,
    email,
    creationdate,
    maincompany_id,
    agency_id,
    agent_id,
    location_id,
    grouptariff_id,
    type_id,
    status_id,
    active,
    refunded,
    erased,
    aux,
    numberchanged,
    master_id,
    createdby_id,
    adrdefault_id,
    adrmain_id,
    pobox_id,
    changelog,
    custype,
    auxdate,
    aux1,
    aux2,
    quickbooks_id,
    extra_emails
  )
VALUES (
    @cust_name,
    NULLIF(@cust_lastname, ''),
    @customer_number,
    @email,
    NOW(),
    @maincompany_id,
    @agency_id,
    @agent_id,
    @location_id,
    @grouptariff_id,
    @customer_type_id,
    @cust_status_id,
    1,
    0,
    0,
    0,
    0,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
  );
SET @customer_id := LAST_INSERT_ID();
INSERT INTO `baddress` (
    name,
    lastname,
    address,
    phone,
    mobile,
    email,
    zip,
    docid,
    customer_id,
    city_id,
    maincompany_id,
    creationdate,
    erased,
    changelog,
    barrio,
    master_id,
    connect_id,
    extra_emails,
    doc_type,
    mobil_code,
    zoom_city_code
  )
VALUES (
    @cust_name,
    NULLIF(@cust_lastname, ''),
    @address,
    @phone,
    NULL,
    @email,
    @zip,
    @docid,
    @customer_id,
    @city_id,
    @maincompany_id,
    NOW(),
    0,
    '',
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
  );
SET @baddress_id := LAST_INSERT_ID();
UPDATE `customer`
SET adrdefault_id = @baddress_id,
  adrmain_id = @baddress_id
WHERE id = @customer_id;
INSERT INTO `user` (
    username,
    password,
    salt,
    email,
    name,
    lastname,
    creationdate,
    maincompany_id,
    agency_id,
    agent_id,
    type,
    status_id,
    logged,
    erased,
    phonenumber,
    photo,
    lang,
    welcome,
    session,
    auth_id,
    auth_provider,
    stripe_customer,
    uiconfig,
    subscriber_id,
    pobox_id
  )
VALUES (
    @username,
    @password_hash,
    @password_salt,
    @email,
    @cust_name,
    IFNULL(NULLIF(@cust_lastname, ''), ''),
    NOW(),
    @maincompany_id,
    @agency_id,
    @agent_id,
    'POBOX',
    1,
    0,
    0,
    @phone,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
  );
SET @pobox_user_id := LAST_INSERT_ID();
INSERT INTO users_roles (user_id, role_id)
VALUES (@pobox_user_id, 58),
  (@pobox_user_id, 60),
  (@pobox_user_id, 62);
INSERT INTO `pobox` (
    number,
    creationdate,
    customer_id,
    user_id,
    createby_id,
    maincompany_id,
    agency_id,
    agent_id,
    warehouse_id,
    type_id,
    status_id,
    reempack,
    stripe_customer,
    numberchanged,
    image
  )
VALUES (
    @pobox_number,
    NOW(),
    @customer_id,
    @pobox_user_id,
    @created_by_user_id,
    @maincompany_id,
    @agency_id,
    @agent_id,
    @warehouse_id,
    @pobox_type_id,
    @pb_status_id,
    0,
    NULL,
    0,
    NULL
  );
SET @pobox_id := LAST_INSERT_ID();
UPDATE `user`
SET pobox_id = @pobox_id
WHERE id = @pobox_user_id;
UPDATE `customer`
SET pobox_id = @pobox_id
WHERE id = @customer_id;
UPDATE maincompany
SET countcustomers = countcustomers + 1
WHERE id = @maincompany_id;
COMMIT;