-- Alta de catalogo de productos y servicios para facturacion electronica

CREATE TABLE IF NOT EXISTS catalogo_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empresa_id BIGINT UNSIGNED NOT NULL,
    tipo ENUM('PRODUCTO','SERVICIO') NOT NULL DEFAULT 'PRODUCTO',
    codigo VARCHAR(50) NULL,
    descripcion VARCHAR(255) NOT NULL,
    unidad_medida VARCHAR(10) NOT NULL DEFAULT 'NIU',
    valor_unitario DECIMAL(14,2) NOT NULL DEFAULT 0,
    precio_unitario DECIMAL(14,2) NOT NULL DEFAULT 0,
    afecto_igv TINYINT(1) NOT NULL DEFAULT 1,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    KEY idx_catalogo_empresa (empresa_id),
    KEY idx_catalogo_tipo (tipo),
    KEY idx_catalogo_descripcion (descripcion),
    CONSTRAINT fk_catalogo_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @catalogo_col_exists := (
    SELECT COUNT(*)
    FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name = 'facturacion_comprobante_detalles'
      AND column_name = 'catalogo_item_id'
);

SET @sql_catalogo_col := IF(
    @catalogo_col_exists = 0,
    'ALTER TABLE facturacion_comprobante_detalles ADD COLUMN catalogo_item_id BIGINT UNSIGNED NULL AFTER comprobante_id',
    'SELECT 1'
);
PREPARE stmt_catalogo_col FROM @sql_catalogo_col;
EXECUTE stmt_catalogo_col;
DEALLOCATE PREPARE stmt_catalogo_col;

SET @sql_catalogo_key := IF(
    @catalogo_col_exists = 0,
    'ALTER TABLE facturacion_comprobante_detalles ADD KEY idx_fact_detalle_catalogo (catalogo_item_id)',
    'SELECT 1'
);
PREPARE stmt_catalogo_key FROM @sql_catalogo_key;
EXECUTE stmt_catalogo_key;
DEALLOCATE PREPARE stmt_catalogo_key;

SET @sql_catalogo_fk := IF(
    @catalogo_col_exists = 0,
    'ALTER TABLE facturacion_comprobante_detalles ADD CONSTRAINT fk_fact_detalle_catalogo FOREIGN KEY (catalogo_item_id) REFERENCES catalogo_items(id)',
    'SELECT 1'
);
PREPARE stmt_catalogo_fk FROM @sql_catalogo_fk;
EXECUTE stmt_catalogo_fk;
DEALLOCATE PREPARE stmt_catalogo_fk;