SET @has_col_orden_variante := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'orden_compra_detalles'
      AND COLUMN_NAME = 'variante_id'
);

SET @sql := IF(
    @has_col_orden_variante = 0,
    'ALTER TABLE orden_compra_detalles ADD COLUMN variante_id BIGINT UNSIGNED NULL AFTER producto_id',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_idx_orden_variante := (
    SELECT COUNT(*)
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'orden_compra_detalles'
      AND INDEX_NAME = 'idx_orden_detalle_variante'
);

SET @sql := IF(
    @has_idx_orden_variante = 0,
    'ALTER TABLE orden_compra_detalles ADD INDEX idx_orden_detalle_variante (empresa_id, variante_id)',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_fk_orden_variante := (
    SELECT COUNT(*)
    FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND CONSTRAINT_NAME = 'fk_orden_detalle_variante'
      AND TABLE_NAME = 'orden_compra_detalles'
);

SET @sql := IF(
    @has_fk_orden_variante = 0,
    'ALTER TABLE orden_compra_detalles ADD CONSTRAINT fk_orden_detalle_variante FOREIGN KEY (variante_id) REFERENCES producto_variantes(id)',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_col_recepcion_variante := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'recepcion_compra_detalles'
      AND COLUMN_NAME = 'variante_id'
);

SET @sql := IF(
    @has_col_recepcion_variante = 0,
    'ALTER TABLE recepcion_compra_detalles ADD COLUMN variante_id BIGINT UNSIGNED NULL AFTER producto_id',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_fk_recepcion_variante := (
    SELECT COUNT(*)
    FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND CONSTRAINT_NAME = 'fk_recepcion_detalle_variante'
      AND TABLE_NAME = 'recepcion_compra_detalles'
);

SET @sql := IF(
    @has_fk_recepcion_variante = 0,
    'ALTER TABLE recepcion_compra_detalles ADD CONSTRAINT fk_recepcion_detalle_variante FOREIGN KEY (variante_id) REFERENCES producto_variantes(id)',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_col_compra_variante := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'compra_detalles'
      AND COLUMN_NAME = 'variante_id'
);

SET @sql := IF(
    @has_col_compra_variante = 0,
    'ALTER TABLE compra_detalles ADD COLUMN variante_id BIGINT UNSIGNED NULL AFTER producto_id',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @has_fk_compra_variante := (
    SELECT COUNT(*)
    FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND CONSTRAINT_NAME = 'fk_compra_detalle_variante'
      AND TABLE_NAME = 'compra_detalles'
);

SET @sql := IF(
    @has_fk_compra_variante = 0,
    'ALTER TABLE compra_detalles ADD CONSTRAINT fk_compra_detalle_variante FOREIGN KEY (variante_id) REFERENCES producto_variantes(id)',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
