-- Tablas maestras reutilizables para guias de remision:
-- direcciones (ubigeo + direccion), transportistas y conductores.
-- Asi los datos se registran una vez y se reutilizan en todas las guias.

CREATE TABLE IF NOT EXISTS facturacion_direcciones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empresa_id BIGINT UNSIGNED NOT NULL,
    tercero_id BIGINT UNSIGNED NULL COMMENT 'Opcional: direccion asociada a un tercero',
    descripcion VARCHAR(255) NOT NULL,
    ubigeo VARCHAR(6) NOT NULL,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    KEY idx_fact_dir_empresa (empresa_id),
    KEY idx_fact_dir_tercero (tercero_id),
    CONSTRAINT fk_fact_dir_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id),
    CONSTRAINT fk_fact_dir_tercero FOREIGN KEY (tercero_id) REFERENCES terceros(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS facturacion_transportistas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empresa_id BIGINT UNSIGNED NOT NULL,
    tipo_documento VARCHAR(2) NOT NULL DEFAULT '6',
    numero_documento VARCHAR(20) NOT NULL,
    razon_social VARCHAR(200) NOT NULL,
    registro_mtc VARCHAR(50) NULL,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_fact_transportista (empresa_id, numero_documento),
    CONSTRAINT fk_fact_transportista_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS facturacion_conductores (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empresa_id BIGINT UNSIGNED NOT NULL,
    tipo_documento VARCHAR(2) NOT NULL DEFAULT '1',
    numero_documento VARCHAR(20) NOT NULL,
    nombres VARCHAR(150) NOT NULL,
    apellidos VARCHAR(150) NOT NULL,
    licencia VARCHAR(30) NOT NULL,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_fact_conductor (empresa_id, numero_documento),
    CONSTRAINT fk_fact_conductor_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Referencias a los maestros desde el traslado de cada guia
SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'facturacion_guia_transporte' AND column_name = 'transportista_id'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE facturacion_guia_transporte ADD COLUMN transportista_id BIGINT UNSIGNED NULL AFTER modalidad_transporte_codigo',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'facturacion_guia_transporte' AND column_name = 'conductor_id'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE facturacion_guia_transporte ADD COLUMN conductor_id BIGINT UNSIGNED NULL AFTER transportista_id',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'facturacion_guia_transporte' AND column_name = 'direccion_partida_id'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE facturacion_guia_transporte ADD COLUMN direccion_partida_id BIGINT UNSIGNED NULL AFTER conductor_id',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = 'facturacion_guia_transporte' AND column_name = 'direccion_llegada_id'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE facturacion_guia_transporte ADD COLUMN direccion_llegada_id BIGINT UNSIGNED NULL AFTER direccion_partida_id',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Rescatar los datos de las guias existentes hacia los maestros
INSERT IGNORE INTO facturacion_transportistas
    (empresa_id, tipo_documento, numero_documento, razon_social, registro_mtc, estado, created_at)
SELECT c.empresa_id, COALESCE(gt.transportista_documento_tipo, '6'), gt.transportista_documento, gt.transportista_nombre, gt.transportista_registro_mtc, 1, NOW()
FROM facturacion_guia_transporte gt
INNER JOIN facturacion_comprobantes c ON c.id = gt.comprobante_id
WHERE gt.transportista_documento IS NOT NULL AND gt.transportista_documento <> ''
  AND gt.deleted_at IS NULL;

INSERT IGNORE INTO facturacion_conductores
    (empresa_id, tipo_documento, numero_documento, nombres, apellidos, licencia, estado, created_at)
SELECT c.empresa_id, COALESCE(gt.conductor_documento_tipo, '1'), gt.conductor_documento, gt.conductor_nombres, gt.conductor_apellidos, gt.conductor_licencia, 1, NOW()
FROM facturacion_guia_transporte gt
INNER JOIN facturacion_comprobantes c ON c.id = gt.comprobante_id
WHERE gt.conductor_documento IS NOT NULL AND gt.conductor_documento <> ''
  AND gt.deleted_at IS NULL;

INSERT INTO facturacion_direcciones
    (empresa_id, tercero_id, descripcion, ubigeo, estado, created_at)
SELECT c.empresa_id, c.tercero_id, gt.partida_direccion, gt.partida_ubigeo, 1, NOW()
FROM facturacion_guia_transporte gt
INNER JOIN facturacion_comprobantes c ON c.id = gt.comprobante_id
WHERE gt.partida_ubigeo IS NOT NULL AND gt.partida_ubigeo <> ''
  AND gt.deleted_at IS NULL
  AND NOT EXISTS (
      SELECT 1 FROM facturacion_direcciones d
      WHERE d.empresa_id = c.empresa_id AND d.ubigeo = gt.partida_ubigeo AND d.descripcion = gt.partida_direccion AND d.deleted_at IS NULL
  );

INSERT INTO facturacion_direcciones
    (empresa_id, tercero_id, descripcion, ubigeo, estado, created_at)
SELECT c.empresa_id, c.tercero_id, gt.llegada_direccion, gt.llegada_ubigeo, 1, NOW()
FROM facturacion_guia_transporte gt
INNER JOIN facturacion_comprobantes c ON c.id = gt.comprobante_id
WHERE gt.llegada_ubigeo IS NOT NULL AND gt.llegada_ubigeo <> ''
  AND gt.deleted_at IS NULL
  AND NOT EXISTS (
      SELECT 1 FROM facturacion_direcciones d
      WHERE d.empresa_id = c.empresa_id AND d.ubigeo = gt.llegada_ubigeo AND d.descripcion = gt.llegada_direccion AND d.deleted_at IS NULL
  );

-- Vincular las guias existentes con sus maestros
UPDATE facturacion_guia_transporte gt
INNER JOIN facturacion_comprobantes c ON c.id = gt.comprobante_id
INNER JOIN facturacion_transportistas t ON t.empresa_id = c.empresa_id AND t.numero_documento = gt.transportista_documento AND t.deleted_at IS NULL
SET gt.transportista_id = t.id
WHERE gt.transportista_id IS NULL AND gt.transportista_documento IS NOT NULL AND gt.transportista_documento <> '';

UPDATE facturacion_guia_transporte gt
INNER JOIN facturacion_conductores cd ON cd.empresa_id = (SELECT empresa_id FROM facturacion_comprobantes WHERE id = gt.comprobante_id) AND cd.numero_documento = gt.conductor_documento AND cd.deleted_at IS NULL
SET gt.conductor_id = cd.id
WHERE gt.conductor_id IS NULL AND gt.conductor_documento IS NOT NULL AND gt.conductor_documento <> '';

UPDATE facturacion_guia_transporte gt
INNER JOIN facturacion_direcciones dp ON dp.empresa_id = (SELECT empresa_id FROM facturacion_comprobantes WHERE id = gt.comprobante_id) AND dp.ubigeo = gt.partida_ubigeo AND dp.descripcion = gt.partida_direccion AND dp.deleted_at IS NULL
SET gt.direccion_partida_id = dp.id
WHERE gt.direccion_partida_id IS NULL AND gt.partida_ubigeo IS NOT NULL AND gt.partida_ubigeo <> '';

UPDATE facturacion_guia_transporte gt
INNER JOIN facturacion_direcciones dl ON dl.empresa_id = (SELECT empresa_id FROM facturacion_comprobantes WHERE id = gt.comprobante_id) AND dl.ubigeo = gt.llegada_ubigeo AND dl.descripcion = gt.llegada_direccion AND dl.deleted_at IS NULL
SET gt.direccion_llegada_id = dl.id
WHERE gt.direccion_llegada_id IS NULL AND gt.llegada_ubigeo IS NOT NULL AND gt.llegada_ubigeo <> '';