-- Normalizacion de catalogo_items con catlogos de tipo y afectacion IGV

CREATE TABLE IF NOT EXISTS catalogo_tipo_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(20) NOT NULL,
    descripcion VARCHAR(120) NOT NULL,
    comentario VARCHAR(255) NULL,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_catalogo_tipo_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO catalogo_tipo_items (id, codigo, descripcion, comentario, estado, created_at) VALUES
(1, 'PRODUCTO', 'Producto', '1 = productos', 1, NOW()),
(2, 'SERVICIO', 'Servicio', '2 = servicios', 1, NOW());

CREATE TABLE IF NOT EXISTS catalogo_afectaciones_igv (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(4) NOT NULL,
    descripcion VARCHAR(120) NOT NULL,
    tipo_afectacion CHAR(1) NOT NULL,
    codigo_tributo VARCHAR(10) NOT NULL,
    nombre_tributo VARCHAR(20) NOT NULL,
    abreviatura VARCHAR(20) NOT NULL,
    porcentaje DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    afecta_igv TINYINT(1) NOT NULL DEFAULT 0,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_catalogo_afectacion_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO catalogo_afectaciones_igv
    (id, codigo, descripcion, tipo_afectacion, codigo_tributo, nombre_tributo, abreviatura, porcentaje, afecta_igv, estado, created_at)
VALUES
    (1, '10', 'OP. GRAVADAS', 'S', '1000', 'IGV', 'VAT', 18.00, 1, 1, NOW()),
    (2, '20', 'OP. EXONERADAS', 'E', '9997', 'EXO', 'VAT', 0.00, 0, 1, NOW()),
    (3, '30', 'OP. INAFECTAS', 'O', '9998', 'INA', 'FRE', 0.00, 0, 1, NOW());

SET @tipo_col_exists := (
    SELECT COUNT(*)
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'catalogo_items'
      AND column_name = 'tipo_id'
);

SET @sql_add_tipo_id := IF(
    @tipo_col_exists = 0,
    'ALTER TABLE catalogo_items ADD COLUMN tipo_id BIGINT UNSIGNED NULL AFTER empresa_id',
    'SELECT 1'
);
PREPARE stmt_add_tipo_id FROM @sql_add_tipo_id;
EXECUTE stmt_add_tipo_id;
DEALLOCATE PREPARE stmt_add_tipo_id;

SET @afect_col_exists := (
    SELECT COUNT(*)
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'catalogo_items'
      AND column_name = 'afectacion_igv_id'
);

SET @sql_add_afect_id := IF(
    @afect_col_exists = 0,
    'ALTER TABLE catalogo_items ADD COLUMN afectacion_igv_id BIGINT UNSIGNED NULL AFTER unidad_medida_id',
    'SELECT 1'
);
PREPARE stmt_add_afect_id FROM @sql_add_afect_id;
EXECUTE stmt_add_afect_id;
DEALLOCATE PREPARE stmt_add_afect_id;

UPDATE catalogo_items
SET tipo_id = CASE UPPER(tipo)
    WHEN 'SERVICIO' THEN 2
    ELSE 1
END
WHERE tipo_id IS NULL;

UPDATE catalogo_items
SET afectacion_igv_id = CASE
    WHEN afecto_igv = 1 THEN 1
    WHEN afecto_igv = 0 THEN 2
    ELSE 1
END
WHERE afectacion_igv_id IS NULL;

SET @sql_add_tipo_fk := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE catalogo_items ADD CONSTRAINT fk_catalogo_tipo_item FOREIGN KEY (tipo_id) REFERENCES catalogo_tipo_items(id)',
        'SELECT 1'
    )
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'catalogo_items'
      AND constraint_name = 'fk_catalogo_tipo_item'
);
PREPARE stmt_add_tipo_fk FROM @sql_add_tipo_fk;
EXECUTE stmt_add_tipo_fk;
DEALLOCATE PREPARE stmt_add_tipo_fk;

SET @sql_add_afect_fk := (
    SELECT IF(
        COUNT(*) = 0,
        'ALTER TABLE catalogo_items ADD CONSTRAINT fk_catalogo_afectacion_igv FOREIGN KEY (afectacion_igv_id) REFERENCES catalogo_afectaciones_igv(id)',
        'SELECT 1'
    )
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'catalogo_items'
      AND constraint_name = 'fk_catalogo_afectacion_igv'
);
PREPARE stmt_add_afect_fk FROM @sql_add_afect_fk;
EXECUTE stmt_add_afect_fk;
DEALLOCATE PREPARE stmt_add_afect_fk;

SET @sql_tipo_not_null := 'ALTER TABLE catalogo_items MODIFY COLUMN tipo_id BIGINT UNSIGNED NOT NULL COMMENT ''1=Producto, 2=Servicio''';
PREPARE stmt_tipo_not_null FROM @sql_tipo_not_null;
EXECUTE stmt_tipo_not_null;
DEALLOCATE PREPARE stmt_tipo_not_null;

SET @sql_afect_not_null := 'ALTER TABLE catalogo_items MODIFY COLUMN afectacion_igv_id BIGINT UNSIGNED NOT NULL COMMENT ''Referencia a catalogo_afectaciones_igv''';
PREPARE stmt_afect_not_null FROM @sql_afect_not_null;
EXECUTE stmt_afect_not_null;
DEALLOCATE PREPARE stmt_afect_not_null;

SET @sql_drop_tipo_legacy := (
    SELECT IF(
        COUNT(*) > 0,
        'ALTER TABLE catalogo_items DROP COLUMN tipo',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'catalogo_items'
      AND column_name = 'tipo'
);
PREPARE stmt_drop_tipo_legacy FROM @sql_drop_tipo_legacy;
EXECUTE stmt_drop_tipo_legacy;
DEALLOCATE PREPARE stmt_drop_tipo_legacy;

SET @sql_drop_afecto_legacy := (
    SELECT IF(
        COUNT(*) > 0,
        'ALTER TABLE catalogo_items DROP COLUMN afecto_igv',
        'SELECT 1'
    )
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'catalogo_items'
      AND column_name = 'afecto_igv'
);
PREPARE stmt_drop_afecto_legacy FROM @sql_drop_afecto_legacy;
EXECUTE stmt_drop_afecto_legacy;
DEALLOCATE PREPARE stmt_drop_afecto_legacy;