-- Patch v9: datos de medicamentos en líneas de documentos electrónicos.
-- Este patch corresponde a la base de datos de la API fiscal.
-- Es seguro: solo modifica las tablas si existen en la base seleccionada.

DROP PROCEDURE IF EXISTS add_medicamento_columns_if_table_exists;

DELIMITER $$
CREATE PROCEDURE add_medicamento_columns_if_table_exists(IN p_table_name VARCHAR(64))
BEGIN
    DECLARE v_table_exists INT DEFAULT 0;
    DECLARE v_registro_exists INT DEFAULT 0;
    DECLARE v_forma_exists INT DEFAULT 0;
    DECLARE v_tipo_transaccion_exists INT DEFAULT 0;
    DECLARE v_sql TEXT;

    SELECT COUNT(*) INTO v_table_exists
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table_name;

    IF v_table_exists > 0 THEN
        SELECT COUNT(*) INTO v_tipo_transaccion_exists
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table_name
          AND COLUMN_NAME = 'tipo_transaccion';

        SELECT COUNT(*) INTO v_registro_exists
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table_name
          AND COLUMN_NAME = 'registro_medicamento';

        IF v_registro_exists = 0 THEN
            IF v_tipo_transaccion_exists > 0 THEN
                SET v_sql = CONCAT('ALTER TABLE `', p_table_name, '` ADD COLUMN `registro_medicamento` varchar(100) DEFAULT NULL AFTER `tipo_transaccion`');
            ELSE
                SET v_sql = CONCAT('ALTER TABLE `', p_table_name, '` ADD COLUMN `registro_medicamento` varchar(100) DEFAULT NULL');
            END IF;
            PREPARE stmt FROM v_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;

        SELECT COUNT(*) INTO v_forma_exists
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table_name
          AND COLUMN_NAME = 'forma_farmaceutica';

        IF v_forma_exists = 0 THEN
            SET v_sql = CONCAT('ALTER TABLE `', p_table_name, '` ADD COLUMN `forma_farmaceutica` varchar(3) DEFAULT NULL AFTER `registro_medicamento`');
            PREPARE stmt FROM v_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END IF;
END$$
DELIMITER ;

CALL add_medicamento_columns_if_table_exists('documento_detalles');
CALL add_medicamento_columns_if_table_exists('fe_documento_detalles');

DROP PROCEDURE IF EXISTS add_medicamento_columns_if_table_exists;